Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase ««12

Normalization vs Denormalization Expand / Collapse
Posted Thursday, October 28, 2010 10:03 AM


Group: General Forum Members
Last Login: Monday, August 24, 2015 6:12 PM
Points: 2,808, Visits: 7,195
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.
Post #1012459
Posted Thursday, October 28, 2010 10:12 AM


Group: General Forum Members
Last Login: Saturday, July 9, 2016 4:04 PM
Points: 140, Visits: 525
How do i quote some one?!


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?

And sorry if i can't express myself correctly English it's not my mother language
Post #1012468
Posted Friday, October 29, 2010 7:35 AM

Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, January 28, 2014 8:15 AM
Points: 3,065, Visits: 4,639
rootfixxxer (10/28/2010)
How do i quote some one?!


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.
Post #1013048
Posted Friday, October 29, 2010 2:44 PM

SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 2:03 PM
Points: 9,836, Visits: 11,913
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.

Post #1013334
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse