Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Normalization vs Denormalization


Normalization vs Denormalization

Author
Message
steveb.
steveb.
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2848 Visits: 7195
As mentioned it does not look like you have normalised to begin with,
when desiging a database you should not worry too much about how the application structures the data, and concentrate more on solid database design following tried and tested methods.


Yes there isn't any rule that tells that, but it should be...



there is no rule saying a DW should be denormalised, but it can be. What level you do depends a lot on the data and reporting requirements.
rootfixxxer
rootfixxxer
SSC-Enthusiastic
SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)SSC-Enthusiastic (140 reputation)

Group: General Forum Members
Points: 140 Visits: 527
How do i quote some one?!

@GilaMonster

Should it? Always? In every case? Why?

I have little knowledge about developing dw's, but the little thing that i know for example in a star scheme i have a table facts in the middle, and then the related info in the other tables (dates, clients, suppliers, whatever), and i know there are some approaches that use normalized tables...
But i'm just a noob/beginner in the dw area...


@Eric Russell 13013
Like i wrote in the beginning this was a small application, i didn't know how the application will grow, plus i didn't know any requirement, so the tables were designed to handle the requirements in the beginning... The tables that i put in the example have much more columns,
So you say to put some extra tables to handle this kind of situations?

I have a similar problem with a module of loading notes (items to pick to a truck, doesn't know the correct name).
In the start of this process the users tell that need to load 3 units of the Item A to delivery day A, 4 units Item B to delivery day B, and so on. The next step, one user will create the loading document to send it to the warehouse, this process it's done by selecting the predefined items and submitting the document to the warehouse. When the document arrives at the warehouse the user responsible for the loading can remove some of the items, and loading the rest.
So i need to maintain the original document intact, but the predefined items will be available to add to another loading document. In this case what should i do? Insert the ID from the predefined item and add another table (don't know how to manage this) to control when was dispatched? Insert the info directly (denormalized) because it's to generate reports?

Thanks
And sorry if i can't express myself correctly English it's not my mother language
PaulB-TheOneAndOnly
PaulB-TheOneAndOnly
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3093 Visits: 4639
rootfixxxer (10/28/2010)
How do i quote some one?!

@GilaMonster

Should it? Always? In every case? Why?

I have little knowledge about developing dw's, but the little thing that i know for example in a star scheme i have a table facts in the middle...


This is what we call Dimensional Modeling(*) - the Kimball(istic) approach but to be fair there is life beyond it. You may want to research Inmon, which approach allows for building a DWH in the 3NF.

You know, when you talk DWH you must have some background info in both Kimball and Inmon.

Hope this helps.

(*) Which I particularly like for a DWH environment.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.
TomThomson
TomThomson
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10698 Visits: 11991
GilaMonster (10/28/2010)
Normalise fully. 4th normal form is probably a safe place to stop. Denormalise as necessary and with good reasons. Not bothering to normalise past 1st is not denormalisation, it's lazyness.

It's not just laziness. It's also stupidity and irresponsible negligence.

But I don't like to start at 4NF, instead I usually start out aiming at EKNF, using Bernstein's algorithm to eliminate functional dependencies. That results in a schema that is in EKNF (hence also in 3NF) and has the respresentation property - every functional dependency is represented by the keys. At that point I look to see whether I want any more normalisation - I don't want multivalued dependencies because I don't want to have to code around update and insertion anomalies, but I also don't want to sacrifice the representation property and have to produce code to suppert functional dependencies. In cases where there are no multivalued dependencies I'm already in 4NF so I'm happy; if there are multivalued dependencies that can be eliminated without sacrificing representation, I eliminate them (and if all my multivalued dependencies get eliminated, I'm in 4NF, so happy again). If there are still some multifunctional dependencies I have to decide for each one whether to normalise it out and accept the difficulty of handling some unrepresented functional dependencies, or leave it in and accept the penalty of dealing with it in code - but this is a fairly rare situation, most business problems can be handled by a schema that both is 4NF and has the representation property (it's a pity that they can't all be done that way).
In effect, rather than starting from 4NF and denormalising where neccessary, I start from EKNF and do additional normalisation where it's both useful and sensible. I suspect the results of both approaches will be the the same, but the means of getting there is different.

Tom

Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search