Inspired by a question that was raised on a data modeling forum, namely, how relationships between instances of the master entity Party should be modeled, I created a representation as of image 1.
Image 1 - Click on image to enlarge it
To illustrate a solution by example, the model includes a
- 1-to-1 relationship (Is_Married_To)
- many-to-1 relationship (Is_Child_Of_(Mother))
- many-to-many relationship (Is_Employed_At)
as well as a relationship attribute (Employment Start_Date).
This model is normalized, i.e. built in a way that it enforces the maximum of semantic / business rules (particularly maximizes mandatory (NOT NULL) attributes) and referential integrity. Attributes and relationships are assigned to the specialized level, since e.g.
- a Legal Entity is a Party, but does not have a First Name,
- only a Person can be employed at a Legal Entity.
The normalized model serves as a reference for an operational process / application that creates, updates and/or deletes instances of the included entities.
Image 2 shows a “soft” (denormalized) model of the very same scenario.
Image 2 - Click on image to enlarge it
In this model, Persons and Legal Entities are not distinguished on the entity level, and all relationships are submerged under a generic many-to-many representation. As a result, there is nearly no enforcement of semantic rules and referential integrity.
Accordingly, the denormalized model is not suitable for an operational application. Depending on the purpose, it may be useful as a data warehouse model provided that its instances have been captured using an application based on the normalized model and then mapped / ETL'd into a database created from the denormalized model.
Please be invited to weigh in.
