Data Modeling

When Data Modeling goes too far

One thing I have struggled with when I have created Operational Data Stores is the tendency to create generic tables that promote re-use. I find these are usually tables like Address and Person. In an enterprise environment there may be many applications or Systems of Record that store Person or Address information. There is the tendency when we create the objects in an Operational Data Store to store like information in one table.


In most cases having multiple Address or Person tables don’t violate any rules of Normalization. It is just a habit of organization to try to have only one table for a certain entity. We probably don’t have a requirement to report on all addresses together? Maybe for Person, but not for Address.

So the question is, Is this over-modeling?

Early in my modeling career I would have thought the question ridiculous. But now, I’m not so sure. If I had it to do over, I think I would duplicate some fields on different tables and leave the objects separate . Is there a problem created by having clients and Sales Representatives in separate tables with the same columns? Certainly the Data Model has more tables but it many ways it is way easier to understand and query. One of the main reasons I would do this is how generic entities can complicate querying and reporting. Although it can provide a more aesthetically pleasing data model, the opposite could be said of the queries that are required to pull information out of the database. And if the queries and reports are more complicated, the same could be said of the Extract, Transform, and Load processes to load the data.

Going forward, I think I will resist the urge to combine similar entities when there isn’t a business reason to do so.

How about you?

About Terry Bunio

Terry Bunio has worked for Protegra for 14+ years because of the professionalism, people, and culture. Terry started as a software developer and found his technical calling in Data Architecture. Terry has helped to create Enterprise Operational Data Stores and Data Warehouses for the Financial and Insurance industries. Along the way Terry discovered that he enjoys helping to build teams, grow client trust and encourage individual career growth, completing project deliverables, and helping to guide solutions. It seems that some people like to call that Project Management. As a practical Data Modeller and Project Manager, Terry is known to challenge assumptions and strive to strike the balance between the theoretical and real world approaches for both Data Modelling and Agile. Terry considers himself a born again agilist as Agile implemented according to the Lean Principles has made him once again enjoy Software Development and believe in what can be accomplished. Terry is a fan of Agile implemented according to the Lean Principles, the Green Bay Packers, Winnipeg Jets, Operational Data Stores, 4th Normal Form, and asking why


No comments yet.

Leave a Reply

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

You are commenting using your 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: