Normalization Tips From The Trenches - Part 2
 |
(Ben Taylor) Having worked with relational databases for 20 years I have seen (or developed :-( ) database schemas that do not perform well when a real world application uses the database. This is the second half of a two part series walking you through real world scenarios where normalization was used inappropriately to meet the application requirements. The first part demonstrated normalization tips for transactional databases. This article in the series considers non-transactional database normalization tips..
In this article I first provide a quick overview of database normalization. Second I show some examples of how user data access methods impact data structure. Finally I review some real world experiences in Reporting Databases.
|
|
| | Related Articles - For Members.
|
|
Key (Please note):
(R) - registration may be required for access at the target site
($) - target site may require paid membership for access to this or other content
Reader Comments: Post Your Comments/Feedback
RE: Normalization Tips From The Trenches - Part 2
(posted: 4/26/2005 7:41:06 AM)
"Disk usage was actually reduced due to de-normalization. Because of the multiple sub-types there were a lot of reference attributes that were empty...." {From the descriptions it seems quite possible that an original design with normalization issues was simply traded for another (with different ones). HTH SM}
RE: Normalization Tips From The Trenches - Part 2
(posted: 4/26/2005 9:48:55 AM)
It is very difficult in such a small article to accurately convey the changes Confidentiality agreements restrict me from sharing more. I can tell you I reduced 18 tables into 1 de-normalized table with all actual permutations. A cartesian of all possible permutations was huge. But a table of all existing permutations resulted in a 30/1 reduction to a totally denormalized method resulting in about a 50% reduction in disk usage.
Ben
RE: Normalization Tips From The Trenches - Part 2
(posted: 4/26/2005 10:42:13 AM)
"It is very difficult..." "...modify schema to accommodate common access patterns" {OK. It appears that design semantics are altered, i.e.(the latter "principle" represents biasing a previous organization of a DB to app requirements). That may leave one with more of an app "data store" than a general purpose DB (may be desirable from a cost performance perspective). If so, characterizing design changes as "denormalizing" "over normalized" DBs may risk being misleading / confusing. HTH SM}
RE: Normalization Tips From The Trenches - Part 2
(posted: 5/6/2005 2:08:07 PM)
This article represents the work of a lot of people much more skilled than myself who pioneered data warehousing. In this niche market the term De-normalizing as a method of optimizing Static data is most common. There was a lot of negative feedback early on for even suggested the process. Today these concepts are baseline. I have no problem justifying the practice. I saved $15Million in hardware by de-normalizing. It cost 1/20th to change and write maintenance for the app. Do the Math. Ben
|
