Database Design – Table with Multiple Links to Another Table

database-designrelational-theoryschema

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?

Best 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

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Here is some vanilla SQL:

CREATE TABLE RoomTypes
(
 RoomType VARCHAR(12) NOT NULL,
 UNIQUE (RoomType)
);

CREATE TABLE Zones
(
 Zone VARCHAR(10) NOT NULL,
 UNIQUE (Zone)
);

CREATE TABLE Rooms
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 UNIQUE (RoomType, Zone, Name), 
 FOREIGN KEY (RoomType) REFERENCES RoomTypes (RoomType),
 FOREIGN KEY (Zone) REFERENCES Zones (Zone)
);

CREATE TABLE NumberedRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Numbered'),
 UNIQUE (Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

CREATE TABLE AncillaryRooms 
(
 RoomType VARCHAR(12) NOT NULL,
 Zone VARCHAR(10) NOT NULL, 
 Name VARCHAR(30) NOT NULL, 
 CHECK (RoomType = 'Ancillary'),
 UNIQUE (Zone, Name), 
 UNIQUE (RoomType, Zone, Name),
 FOREIGN KEY (RoomType, Zone, Name) 
    REFERENCES Rooms (RoomType, Zone, Name)
);

The CHECK constraints will not be tested by mySQL e.g. do the tests yourself using triggers. Consider adding other tests e.g. that attribute Name in table NumberedRooms represents an integer.

The idea that every row in the supertype table Rooms will have exactly one row in the union of AncillaryRooms and NumberedRooms. This is merely implied e.g. have 'helper' procs to add rows to both super- and subtype tables as a single operation and use triggers to ensure it is done.

Note NumberedRooms has a simple key Name alone, whereas AncillaryRooms has a compound key on (Zone, Name). All three (non-lookup) tables have a key on (RoomType, Zone, Name) throughout, allowing further subtype tables to referencing them and maintain integrity by further testing for valid RoomType values.

Database Design – Deciding Between Complete Disjoint or Incomplete Overlapping in Supertype/Subtype

Physical implementation of subtyping in a database is a complex issue. Unless you have a situation where it offers compelling advantages (see below for one or two examples) it adds complexity into implementation while providing relatively little value.

Having done this with really complex subtyping (applicaitons and sentences on a court case management system, disparate combined-risk commercial insurance contract structures) I guess I have some observations on this. Some significant corner cases are:

If the total number of database fields across the subtypes is relatively low (say: less than 100) or there is significant commonality between subtypes then splitting the subtypes out into separate physical tables is probably of little value. It will add significant overhead to reporting queries and searches. In most cases it's best to have a single table and manage your subtyping within the application. (Probably the closest to your problem)
If your subtyping is very disjoint, and different subtypes have type-dependent data structures hanging off them (i.e. child tables or more complex structures), then subtype tables make sense. In this case, each subtype probably has relatively little commonality within the application (i.e. there is probably a whole subsystem within the application dedicated to that subtype). Most reporting and querying will probably occur within a given sub-type, with cross-type queries mainly being restricted to a handful of common fields. (Court case management system)
If you have a large number of subtypes with disparate attributes and/or a requirement to make this configurable then a generic structure and supplementary metadata may be more appropriate. See this SO posting for a rundown on some possible approaches. (Insurance policy administration system)
If you have a very large number of fields with little commonality across your sub-types and little requirement to query across sub-type tables (i.e. nothing much in the way of multi-way outer joins against your sub-type tables) then sub-type tables may help to manage the column sprawl. (Pathologically complex version of your problem)
Some O/R mappers may only support a particular approach to managing sub-classes.

In most cases physical sub-type tables in a DB schema are a bit of a solution in search of a problem, as they potentially have undesirable side-effects.

In your case, I assume you have a relatively modest number of sub-types and a manageable number of attributes. Your diagram and question don't indicate any intention to hang child tables off the records. I would suggest that you consider going with the first option suggested above and maintaining one table and manage the sub-typing within your application.

Best Answer

Related Solutions

Mysql – Database Design: What is the best structure for storing site offices and locations

Database Design – Deciding Between Complete Disjoint or Incomplete Overlapping in Supertype/Subtype

Related Question