0

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?

3

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
4
  • How would I then query this to get information on both of the sites? If I use a LEFT JOIN, I only get circuit info, if I RIGHT JOIN, I only get site info, if I INNER JOIN, I don't get anything.
    – Lily Mara
    Jul 12 '14 at 0:35
  • 1
    edited answer with example query
    – ryand
    Jul 12 '14 at 4:10
  • 1
    If you think of the two sites differently e.g. one is a "source" and the other is a "destination" make sure you name the foreign key columns appropriately and put the right id_site in the right column. If you think of them symetrically be aware of this. The combination (A,B) will be the same in your mind as (B,A) but will be a different value as far as the table is concerned. You need to look out for this when writing data and queries. Jul 12 '14 at 6:08
  • I'm using MS Access (I know), so the query I ended up using is SELECT CT.cableName, S1.siteName, S2.siteName FROM (cables AS CT INNER JOIN sites AS S1 ON CT.site1 = S1.idSite) INNER JOIN sites AS S2 ON CT.site2 = S2.idSite;
    – Lily Mara
    Jul 12 '14 at 13:41
0

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.

3
  • The problem with making an IsSource column is that each site could have multiple circuits going to it. I can enforce constraints, and Rydel's query works fine.
    – Lily Mara
    Jul 12 '14 at 13:41
  • @NateMara: If you are in control of the creation process then I agree - that solution is simpler. Just be aware of the issues and ambiguities involved. Jul 12 '14 at 13:54
  • This is all in an Access DB (I know it's bad; I'm just a lowly intern, have to listen to boss), so everyone using the DB will have to use my forms to fill data, and I can ensure that they only use valid info.
    – Lily Mara
    Jul 12 '14 at 14:04

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.