Sql-server – Table design for child tables with multiple many-to-many relationships

database-designsql-server-2012

We are working on designing tables for a new Networking application, and have been having some problems with finding the best design for the top level tables.

  1. Network is the top level table.

  2. Under the Network there are: Subnet, VirtualLan (VLan), and Virtual Private Lan Service (VPLS).

  3. A Network can optionally have a Subnet, a VLan, and a VPLS. Subnets, VLans, and VPLSs, if they exist, they must belong to a Network.

  4. Many-to-many relationships can exist for Subnets, VLans, and VPLSs.
  5. Subnets, VLans, and VPLSs all have one or more Location(s) (e.g. – Boston, Washington), and if they have any relationships to each other, the Locations amongst the relationships must all agree.

We've taken a couple of runs at this, and keep running into problems when we get down to many-to-many relationships among the Subnets, VLans, and VPLSs and the locations. we were trying to use an association table that held a foreign key from each of the Subnet, Vlan, VPLS, and Location tables, but we run into problems when we try to create the relationships (like what if there are existing relationships – do we delete those first, and then create new relationships).

Can anyone give me ideas on the best approach at designing these tables?

Best Answer

I'd recommend creating a table which encompasses subnets, VLANs and VPLSs; you can then have a simple self-join table. If these three concepts each have a significant number of unique fields, you can use vertical partitioning and create a sister table for each of the three. Using pseudo-code:

* Networks
NetworkID PK

* Locations
LocationID PK

* SubNetworks  -- This concept encompasses SubNets, VLANs, and VPLSs
SubNetworkID PK,
NetworkID  NOT NULL REFERENCES Networks

* SubNetworkLocations
SubNetworkID NOT NULL REFERENCES SubNetworks,
LocationID   NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkID, LocationID)

* SubNets
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VLANs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* VPLSs
SubNetworkID PK REFERENCES SubNetworks
-- Additional fields specific for this type of sub-network

* SubNetworkRelationships
SubNetworkA NOT NULL REFERENCES SubNetworks,
SubNetworkB NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkA, SubNetworkB),
UNIQUE (SubNetworkB, SubNetworkA)  -- For efficient querying from the other direction

As with any many-to-many table, be sure your sproc for inserting into SubNetworkRelationships checks for the existence of both { A, B } and { B, A } before inserting a new row. You can use check constraints to prevent a sub-network from relating to itself.

In this model, there's no RDBMS-level enforcement of the rule "if two sub-networks are related, they must have all of the same locations." I'm going to go out on a limb and assume that all such tied-together sub-networks must belong to the same Network, too. If you really want this:

* Networks
* Locations

* SubNetworkGroups
SubNetworkGroupID PK,
NetworkID         NOT NULL REFERENCES Networks

* SubNetworkGroupLocations
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
LocationID        NOT NULL REFERENCES Locations,
PRIMARY KEY (SubNetworkGroupID, LocationID)

* SubNetworks
SubNetworkID PK

* SubNetworkGroupMemberships
SubNetworkGroupID NOT NULL REFERENCES SubNetworkGroups,
SubNetworkID      NOT NULL REFERENCES SubNetworks,
PRIMARY KEY (SubNetworkGroupID, SubNetworkID)

* SubNets
* VLANs
* VPLSs

A group of SubNetworks belong to one Network but zero or more Locations. A SubNetwork can belong to multiple groups. You'd still need to enforce that the various groups a sub-network belongs to all have the same locations, so I'm not sure this buys you much.

Related Question