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
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 table||Michael Pawlowsky||3 Oct|