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
device.
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,
Mike
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
