Is this a sane model for a network provisioning system?

Question

I'm sketching out a model for a network setup, where I have the following conditions:

1. There are multiple nodes which contains a number of switches each.
2. Each switch has multiple services, which may or may not have a rate limiting.
3. Each service is identified by a VLAN ID (VID), which is different on each switch per node.
4. VID cannot be re-used for other services across switches, but it is permitted to re-use the VID for the same service on switches on a different node.
5. The rate limiting for each service may be overridden by the profile assigned to the CPE.
6. A CPE can have any number of ports, each with its own service, and possibly rate-limit. VID and default rate-limit is determined by service, as noted above.

Example on the service-switch-VID restrictions: The "Internet" service has VID 1 on switch 1, and VID 11 on switch 2 for node #1, and #2. But VID 21 and 31 for switch 1 and 2 on node #3

Please let me know if you need any additional information. Almost 3am here, so I might have forgotten something.

Update: Fixed the list, and updated the model to reflect the changes I've highlighted in my first comment. Also noticed a missing FK relation, between cpe and cpe_port`, which I've added.

Answer 1

Understanding of the specifications and proposed data model

We have mantained very productive interactions via comments, I have read your question carefully and examinined your diagram closely, so I have prepared the following assertions that I consider relevant in order to describe my current understanding of your scenario and, therefore, share my personal approach to such:

• A Node lodges one-to-many Switches.
• A Switch forwards data to one-to-many Services (employing one particular VLAN in each data forward).
• A Switch provides one-to-many Services to one-to-many CPEs (employing one specific VLAN in each provisioning). Or, put another way, a CPE is provided with one-to-many Services by one-to-many Switches (using one specific VLAN in each provisioning).
• A CPE is classified by one SpeedProfile.
• A CPE is set with one Port in oder to receive one specific Service.
• A CPE is set with one Port in oder to receive data from one specific Switch.
• A Customer (either one Organization or one Person) receives one-to-many Services via one CPE.

From such collection of assertions, I have derived (and uploaded as a .PDF document to Dropbox) the following…

• Network Provisioning Preliminary Data Model.

Note: It is necessary to point out that some of these assertions (or parts of specific ones) have been identified as inaccurate or erroneous, as mentioned below in the section entitled Subsequent conclusions.

As you can see in said IDEF1X data model, I consider that CPEService is the “core” entity, so to speak, since this is where I think that the relationships between CPEs and Services, Switches or VLANs take effect.

It is very important to mention that, if I understand your specifications well, there is a “three way” relationship in which the entities Service, Switch and VLAN are involved. That is why I have modelled (a) the association between Switch and VLAN in a specific many-to-many relationship, (b) the association between Service and VLAN in another particular many-to-many relationship, and (c) another many-to-many relationship called SwitchVLANService, where the “connection” between these three entities actually takes place.

I have used natural PRIMARY KEYS as much as has been possible, in order to capture the meaning of every prime attribute and make it more evident not only in its original entities, but also when it migrates to related ones. If you feel comfortable with my proposed model, it will be more easy for you to determine the stability of each one of such KEYS, and decide if you keep them or replace them with different ones, since you are, of course, involved in the actual context where the database is going to be used.

Subsequent conclusions

By virtue of comparing our models, discussing the important subjects and clarifiying the pending points via comments, we have have been able to confirm most of the things that you have already defined in your model:

• The “three way” relationship between Service, Switch and VLAN that I proposed in my preliminary is totally unnecessary, since what it is actually relevant in your context is stroring only what Service is broadcast by a particular VLAN. In this manner, a Service is related to a Switch by means of its association with a VLAN.
• The Service.RateLimit, the SpeedProfiles defined for a specific VLAN and the CPEPort.RateLimit are all necessary, and you will manually decide which speed is set for a particular set of entities, since you have determined an overriding order for this aspectt.
• CPEPorts are not directly related to Switches, but CPEPorts have a direct relationship with VLANs, since you have to set a specific CPEPort to receive a given Service that is supplied by a particular VLAN. In this way, as you have pointed out via comments, it is necessary to ponder the possiblity of dropping the Service - CPEport relationship that you have presented in your diagram.
• The stability of the used PRIMARY KEYS has been identified.

This Network Provisioning Data model (also uploaded as a .PDF document to Dropbox), which is based on yours and also on the series of deliberations that we have kept over the last weeks, depicts these conclusions.

The Party entity

I have included the Party^[1] entity in the data model since I think that it can be very useful to provide a means to represent both Organizations and Persons as a single entity, while also keeping their particular attributes in two corresponding separate entities. This scenario is an occurrence of what is commonly referred as supertype-subtype cluster (an exclusive one, in this case) in which Party has been defined as the subertype, and Organization and Person as its subtypes. You may find this answer of help as an introductory resource about this kind of structures.

Thus, your following comment provides a good oportunity for describing its usage:

I quite like the Party table, though I don't think MySQL supports a compound FK in a single column? I'll, most likely, use a single table for both types of customers. Might also use two child tables, to contain the different info for each type of customer. With a 1-0 relation to the customer table.

These are very relevant points that you have brought to the discussion since, in accordance to the structure depicted in the new data model, it is not necessary to use a compound FOREIGN KEY in a single column, because Organization.OrganizationId and Person.PersonId are PRIMARY KEYS that, at the same time, serve as FOREIGN KEYS that draw their values from a corresponding row contained in Party.PartyId. This is an example of what Dr. E. F. Codd defined as Primary Keys on a Common Domain^[2].

This way, as you have already skillfully determined, you can use a single table for storing the data pertaining to the role played by a particular Party (Organization or Person) as a Customer, so I have included the latter entity in the model. For instance, suppose that the MaximumOverdue for customers will be arrranged on an individual basis, so this datum should be stored in the Customer table. It is also worth mentioning that the Customer.CustomerId PRIMARY KEY column expresses the role name assingned to Party.PartyId in this specific entity and, as such, CustomerId always takes its values from the last-mentioned column.

Implementation

The follwing are some important aspects that must be taken into account when implementing exclusive supertype-subtypes relationships:

• Each supertype row must always have one corresponding subtype row, that is, there should not be a single supertype row without one subtype counterpart stored in the database. This means that, in this particular scenario, you should make sure that there is not a single Party row without an Organization (or Person) row.
• Every supertype occurrence must only be complemented by the correct subtype instance, i.e., the value contained in the subtype discriminator attribute (which is Party.PartyTypeCode^[3] in this case) has to be correct for the respective subtype row, and there should be no chance for a supertype to be supplemented by one row of the wrong subtype. In other words, you should create a method in order to assure that, when you have a Party that contains a PartyTypeCode value that indicates that such Party is an Organization, it is only complemented by a row contained in the Organization table, and never by a row contained in the Person table.

In order to cover these aspects in your database, I would highly recommend you inserting every supertype-subtype relationship instance inside a transaction.

Notes

_{1. A party is a person or group of persons (or people) that compose a single entity in a legal sense, so this term is useful to represent either an Organization or a Person in this particular business domain.}

_{2. Codd, E. F. (Jan. 1990). Introduction to Version 2 of the Relational Model. In The Relational Model for Database Management: Version 2 (pp. 25-26). Boston, MA, USA: Addison-Wesley}

_{3. Since a party has only two possible subtypes, you can remove the PartyType entity and the subsequent Party.PartyTypeCode column, and then handle the Party subtype discriminator by means of a BIT column, which you could possibly denote as IsOrganization or IsPerson.}