As I read David C Hay’s awesome book on Data Model Patterns, I start to realize the mistakes I have made creating certain Data Models in the past. In particular, one mistake that I made repeatedly became very apparent.
Data Modeling mistake – Violating 4th Normal Form
Looking back in some of the Data Models I have created, I would say they have been pretty good and for the most part modeled the data in the enterprise pretty well. But in a couple of cases, we did create some components of the models that in hindsight leave me somewhat regretful.
The one situation that caused me to reflect was the solution we used to model relationships between three primary entities and addresses. Each of these relationships were a many-to-many relationship so certainly an intermediate table was required. In addition, we also need to define an attribute for the intended purpose of the address. As this was an attribute of the relationship and not the address itself, this was another requirement for a relationship table. So far, so good.
What we eventually decided on was the following structure as an address could be related to an account OR an instruction OR a client.
- contact_method_id (PK)
- Address_id (FK)
- Account_id(FK) – nullable
- Client_id(FK) – nulllable
- Instruction_id(FK) – nullable
- Address fields (no sense listing them all out)
We thought it would be simpler having one table that contained all the relationships to Address. Basically I think we thought that it was simpler from the Data Modeler point of view and made the diagram simpler. But what we underestimated is that it required the developers and business users to understand a more complicated model and know when the different nullable foreign keys would need to be populated. In a sense, we built additional logic into the data model to reduce the number of tables.
4th Normal Form
What we were doing was violating the 4th Normal Form for the reasons of perceived simplicity. In hindsight, I experienced the problems that violating 4th Normal Form can cause.
4th Normal Form is defined as: “Under fourth normal form, a record type should not contain two or more independent multi-valued facts about an entity.”
All my Data Models now are in 4th Normal Form. They may have more entities, but more entities does not mean a more complex model. My standard now is for my Relational models to start in 4th Normal Form and not just 3rd Normal Form.
