List:General Discussion« Previous MessageNext Message »
From:Michael Pawlowsky Date:October 3 2009 12:14pm
Subject:How to create a relationship to another row in the same table
View as plain text  
I'm having a hard time trying to find the best way to create a  
relationship between two rows in the same table.

I have a database that describes network equipment.

There is a table called devices that contains information about the  

There is another table called ports that describe different types of  
ports (electric, ethernet, serial) and so on. It has a foreign key to  
the device.

What I need now is a way to connect two ports together. So for  
instance, if it was an ethernet port the connection would be from  
perhaps a server to a switch.

I could have a table (connections) with 2 rows in it for each entry  
(from server port to switch port and another one from the switch port  
back to server port) but this seems redundant.
If I create a cross reference table with a primary key on (port_id_a,  
port_id_b) it still allows me to recreate the same connection but in  
the reverse order. This should not be allowed since we can only  
connect ports 1 to 1.

I'vr thought about keeping the connected port relationship in the  
ports table and updating two rows every time a change is made. But  
once again, I'm thinking this should be down using only 1 row.

Any of any ideas on an elegant database structure to do this?

Thanks in advance for your input,

How to create a relationship to another row in the same tableMichael Pawlowsky3 Oct