Sunday, May 12, 2013

Modeling Party-to-Party Relationships

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.
Created with SILVERRUN RDM Relational Data Modeler - Tool for Conceptual, Logical and Physical Data Modeling
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.

Created with SILVERRUN RDM Relational Data Modeler - Tool for Conceptual, Logical and Physical Data Modeling
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.

2 comments:

  1. How does this take into account step-parent relationships? Thanks!

    ReplyDelete
    Replies
    1. Dennis,

      Thank you for your interest in the solution that I suggested with the above normalized model.

      For clarification: This model is not intended to be complete for all potential (or even the most frequent) relationships between Parties, but contains an exemplary representation each of a 1-to-1, a many-to-1 and a many-to-many relationship.

      To answer your question: Provided the understanding that a Person can only have one step-mother and one step-father, a step-parent-relationship can be represented as two (additional) optional many-to-1 relationships "Is_Step-Child_Of_(Step-Mother)" and "Is_Step-Child_Of_(Step-Father)". These two relationships will result in two additional NULL-value-possible foreign keys (FK) "Is_Step-Child_Of_(Step-Mother) Party Id" and "Is_Step-Child_Of_(Step-Father) Party Id" in the entity Person.

      Of course, this solution is not the only possible way of representing a step-parent relationship. E.g. a "business process" in an orphanage may require that a potential step mother and step father are identified as a married or common-law couple. In that case, the relationship may have to be created between an instance of Person (the orphan child) and an instance of an entity Couple (the step-parents) whereas Couple itself would be a relationship-entity.

      Delete