Analysis Trap 1 - ER modeling with incompleteness
Analysis trap 1When we work on establishing the business model through Entity Relationship modeling, one of the most common pitfalls we risk walking into is:
This was the first suggestion, which obviously will not pass the test of even the First Normal form.
They ended up with this:
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:
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.
Free eBookSubscribe 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.Read more Testimonials
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."
Theory & Practice
SQL Server DBA
Install SQL Server
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
The name Oracle is a trademark of Oracle Corporation.