Design for table with multiple links to other table

Posted on

Question :

I am designing a database to keep track of our network equipment. My db has 2 tables:

sites & circuits

(circuit = cable) & (site = property location)

Each circuit runs between two sites, so it should relate to two sites, yes? The circuit table has the following columns: ID_circuit (PK), circuit_number, site1, site2. I thought that I could relate my ID_site field to both site1 and site2, but I cannot. Would I be better off creating a whole other table to hold the locations of both cable ends?

Answer :

Site1 and site2 can both be FKs, linked to the ID_site PK. This works on any rdbms that i’m aware of.

Example Query:

SELECT CT.site1, CT.site2, S1.site_description, S2.site_description
FROM circuit_table AS CT
INNER JOIN site_table as S1
  ON CT.site1 = S1.ID_site
INNER JOIN site_table as S2
  ON CT.site2 = S2.ID_site

Yes, you will be better off creating a whole separate table to hold the locations of both cable ends unless you can enforce constraints during row creation. The ambiguity mentioned by Michael Green in a comment, about how one ensures that AB is recognized as identical to BA, is the critical issue.

If you are in a position to enforce an ordering constraint on the sites during row construction then the issue is less significant, and you can take the shortcut of a single table safely.

If you require the separate Site table, you will probably wish to add a bit field such as IsSource to its Key as the simplest way to ensure that each Circuit can have only 2 ends.

Leave a Reply

Your email address will not be published. Required fields are marked *