The Database Design Resource Center



Analysis Trap 1 - ER modeling with incompleteness

Analysis trap 1

When we work on establishing the business model through Entity Relationship modeling, one of the most common pitfalls we risk walking into is:

Modeling with incompleteness

You may be asked: "We need a system for reporting quarterly balances." I have seen models put up like this: (the example is from a real world situation that eventually went wrong):

Denormalized table

This was the first suggestion, which obviously will not pass the test of even the First Normal form.

They ended up with this:

Badly normalized table

Nice enough.

But, is this based on a complete model of the reality? Where do we get our BALANCE from? It does not come from out of the blue. A full and true picture of the reality would be something like:

Normalized tables

Quarterly balances are computed from of transactions. The entities for BALANCES and PERIODS are denormalizations; derived (in this case; computed) values that has to come from transactions. It MAY be correct to use the former model (in a data warehouse, which is a totally different approach), but it violates 3NF if you expand your view to look at the complete business model.

I call this The Analysis Trap 1. This may happen if one tries to limit the scope of the task at hand, a nd it would lead us into a situation where, whenever a transaction is inserted, updated or deleted, we MUST have a business rule that says "Update BALANCES whenever something happens in TRANSACTIONS." There may even be a worse cause for this analysis trap 1 to happen: Someone might say: "Performance will be lacking if we do not sum up here and there", all with the intention of 'helping' the database engine. Such help is seldom or never needed.

A computer's performance ability has NOTHING to do with the analysis of the business!

(We will return to that in an upcoming Design Phase topic).

Remember, it is the business; how we run it (or would like to run it), that determines what information (entities and attributes) we need, and how the different types of information are dependant on others ( relationships).

As you work your way through the analysis phase, the business model (and implicitly the E-R model) will become more and more accurate. Applying normalization techniques will ensure low (read: no) level of redundancy ( repeating the same information), and as a consequence also deliver a high level of referential integrity (correct relationship values)

Avoid falling into the Analysis Trap 1 by staying in tune with the 5 Normal Forms, and by getting the full picture of the customer's business.

Return to The Analysis Phase


Exclusive interviews with:
Steven Feuerstein, PLSQL expert
Donald Burleson, Top IT consultant


Free eBook

Subscribe to my newsletter and get my ebook on Entity Relationship Modeling Principles as a free gift:


What visitors say...

"I just stumbled accross your site looking for some normalization theory and I have to say it is fantastic.

I have been in the database field for 10+ years and I have never before come across such a useful site. Thank you for taking the time to put this site together."

Mike, USA

Read more Testimonials



Database Normalization eBook:


Database Normalization eBook




Copyright © www.databasedesign-resource.com / Alf A. Pedersen
All rights reserved.
All information contained on this website is for informational purposes only.
Disclaimer: www.databasedesign-resource.com does not warrant any company, product, service or any content contained herein.

Return to top

Copyright acknowledgement note:

The name Oracle is a trademark of Oracle Corporation.
The names MS Access/MS SQL Server are trademarks of Microsoft Corporation.
Any other names used on this website may be trademarks of their respective owners, which I fully respect.