Two relationships between two tables?

kyler · #1 (**permalink**) 02-19-04, 11:24

I am working on an asset inventory database for computers, monitors, printers, etc. PCs and printers each have their own table, but I would like to have a relationship between the PC and printer tables for PCs with a local printer.

This leads me to question if I should have a field in the PC table for the local printer ID, a field in the printer table for attached PC ID, or both. Unfortunately, since not many PCs have a local printer, there would be a lot of nulls in the field in either table. However, it would be nice to easily list local printers (without searching the printer table) on a query of PCs. It would also be nice to easily identify an attached PC (without searching the PC table) on a query of printers.

What are the thoughts on this? Would it be better to create two relationships, or only one? If one, which one? Thanks.

andrewst · #2 (**permalink**) 02-19-04, 12:00

How about adding another table for the printer/pc relationship? This would also allow you to track movement of printers, e.g.:

PHP Code:


			
select * from printer_pc where printer_id = 123;



printer_id  pc_id     from         to

----------  --------  -----------  -----------

123         ABC1      01-Jan-2001  14-Jul-2003

123         DEF4      15-Jul-2003

Internet Services

Web Development

Digital Marketing

Consumer Tech