Database design, one2many, bidirectional dependency between tables

Question

I'm working on lending system. I have 2 tables. Loan and Tool. One loan can have more tools on it.

public class Loan
{
    [Key]
    public int LoanId { get; set; }

    [Required]
    public DateTime WhenBorrowed { get; set; }

    public DateTime? WhenReturned { get; set; }

    // foreign key to Tool
    public virtual ICollection<Tool> Tools { get; set; }
}

public class Tool
{
    // Primary key
    public int ToolId { get; set; }

    [Required]
    public string Name { get; set; }

    // foreign key to Loan
    public int? LoanId { get; set; }
    public virtual Loan Loan { get; set; }
}

I want to ask questions like:

1. Tools availability - borrowed/not borrowed.

I solved this by using LoanId in Tool table, checking if it's null or not.

2. History of transactions

   And here I have a problem. That icollections seems to contain only foreign keys to Tool table. So when I set LoanId back to null(to indicate that the tool was returned and can be borrowed again) I lose the history. How should I solve this. Should I define another foreign key also in Loan table? Like this:

   // foreign key to Tool
   public IList<int> ToolId { get; set; }
   public virtual Tool Tool { get; set; }
   

It seems like circular dependencies in foreign keys to me. What is the right way to achieve needed functionality?

Thanks for any suggestions.

Answer 1

Loan model:

public class Loan
{
    [Key]
    public int LoanId { get; set; }

    [Required]
    public DateTime WhenBorrowed { get; set; }

    public DateTime? WhenReturned { get; set; }

    // foreign key to Tool
    public virtual ICollection<LoanHistory> History { get; set; }
}

LoanHistory model:

public class LoanHistory
{
    // foreign key to Tool
    public int ToolId { get; set; }
    public virtual Tool { get; set; }

    // foreign key to Loan
    public int LoanId { get; set; }
    public virtual Loan Loan { get; set; }
}

Tool model:

public class Tool
{
    // Primary key
    public int ToolId { get; set; }

    [Required]
    public string Name { get; set; }

    public bool IsAvailable { get; set; }
}

This way you keep the loan history.

To check the tools simply as that:

var AvailableTools = db.Tools
    .Where(p => p.IsAvailable == true)
    .ToList();

Also is worth mentioning that you need to update the tools IsAvailable property when making a new loan or returning one.