How to create a relationship to another row in the same table

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=gcdmg-mysql-2@m.gmane.org