Multiple Relationships between the same two tables?

[CarlJohnson]

I am making an inventory database, and I would like to keep a running history of who has had which equipment checked out to them. I already have a one-to-many relationship between the equipment and employee tables. Is is possible to create a many-to-many relationship between the same two tables using a junction table? Would there be any side effects to doing something like that?

 

[Kryst51]

That is exactly what you need to do with a many to many relationship.... Be sure to get rid of the current one-to-one and make it purely many-to-many via the junction table.

 

[CarlJohnson]

Kryst51,
Thanks, but I meant can I have these relationships at the same time? I would use the one-to-many relationship to keep track of what is currently checked out, and then use the many-to-many only for a history.

 

[Kryst51]

No, that is unecessary.... It's duplication and does not follow normalization rules.... Why? If there is only one, match between the two, then there will only be one record in the junction for it, so it will act the same as a one-to-one anyway....

 

[CarlJohnson]

Perhaps I'm looking at the problem the wrong way. So I'll just type out my scenario:

Employee 1 currently has Laptop D checked out to them. However, they also have had Laptop A, B, and C in the past. Employee 2 is now using Laptop A, etc.

I want to be able to know which laptops are being checked out to which employee currently, but I also want to be able to look up an employee's history, so I can know if Employee 1 has a history of breaking Laptops.

I started by making my database able to track and interact with the current laptop(s) only (the one-to-many). Now I want to make a table that gets a new entry whenever a laptop is removed (which would be a many-to-many, since the old laptops will be passed to a new employee).

 

[Kryst51]

Add a checkout date and a checkin date onto the junction table. That way for instance:

JunctiontblID1, EmployeeID1, LaptopID1, Checkout-Date (say 05/10/10), Checkin-Date (say 06/10/10)

JunctiontblID2, EmployeeID1, LaptopID2, Checkout-Date (Say 06/11/10), Checkin-Date (Say 07/01/10)

etc.... then you can query this table for current (which would not have a checkin date) or for history (All records with the employee ID) and you could also query all the times a certain laptop has been checked out, etc.

 

[CarlJohnson]

Thank You!

That makes so much sense now.

 

[Kryst51]

You are very welcome. I am glad I could help.