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:
Node lodges one-to-many
Switch forwards data to one-to-many
Services (employing one particular
VLAN in each data forward).
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).
CPE is classified by one
CPE is set with one
Port in oder to receive one specific
CPE is set with one
Port in oder to receive data from one specific
Customer (either one
Organization or one
Person) receives one-to-many
Services via one
From such collection of assertions, I have derived (and uploaded as a .PDF document to Dropbox) the following…
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
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
VLAN are involved. That is why I have modelled (a) the association between
VLAN in a specific many-to-many relationship, (b) the association between
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.
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
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
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
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.
I have included the
Party entity in the data model since I think that it can be very useful to provide a means to represent both
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
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
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.
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
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.
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
- 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 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
In order to cover these aspects in your database, I would highly recommend you inserting every supertype-subtype relationship instance inside a transaction.
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