2

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?

  • A subnet has one network, one location, zero or more other subnets, zero or more VLANs, and zero or more VPLSs; is that right? And the same for VLANs and VPLSs? – Jon of All Trades Jul 31 '14 at 23:09
  • 1
    What are the business rules between all these entities? The point of database design, and particularly of Normalization, is to capture these real-world business rules in the structure of the database to the greatest extent possible. If your design is creating many-to-many relationships that don't exist in the real world that indicates that you are declining to Normalize your database design. – Pieter Geerkens Jul 31 '14 at 23:24
1

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.

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.