Data Modeling, Experience Report

Data Modeling mistake – Violating 4th Normal Form

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.

About Terry Bunio

Terry Bunio is passionate about his work as the Manager of the Project Management Office at the University of Manitoba. Terry oversees the governance on Information Technology projects to make sure the most important projects are being worked on in a consistent and effective way. Terry also provides leadership on the customized Project Methodology that is followed. The Project Methodology is a equal mix of Prince2, Agile, Traditional, and Business Value. Terry strives to bring Brutal Visibility, Eliminating Information islands, Right Sizing Documentation, Promoting Collaboration and Role-Based Non-Consensus, and short Feedback Loops to Minimize Inventory to the Agile Project Management Office. As a fan of pragmatic Agile, Terry always tries to determine if we can deliver value as soon as possible through iterations. As a practical Project Manager, Terry is known to challenge assumptions and strive to strike the balance between the theoretical and real world approaches for both Traditional and Agile approaches. Terry is a fan of AWE (Agile With Estimates), the Green Bay Packers, Winnipeg Jets, and asking why?

Discussion

No comments yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: