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


Normalization vs Denormalization


Normalization vs Denormalization

Author
Message
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: 531
Hello

I have some doubts when should i use the normal form or when i shouldn't.

I'm creating a very large application (kind of ERP) that has several modules, and sometimes in some places i see that if i put the data in denormalized form it's better to handle, but i don't know if i will get problems in the future if i take this decision.

So if someone knows some guidelines, best practices when to choose the denormalized form over the normalized , please write them here.

Thanks
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
for transactional relational databases especailly a large ERP chose normalised, it would be bad practice not too, it may seem 'easier' not too but it will lead to problems in the future.

for data warehouses then you can use de-normalised.
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: 531
Thanks for the reply.

But even if i don't use it right now, i d like to know when and where to do it.

DataWarehouses is obvious that must be denormalized. ;-)
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
Normilastion is part of relational database modelling and if you are creating a large scale system should be used everywhere.

Without knowing anything about the system and data it will be impossible for anyone to tell you anything more specific.

To be honest if you are designing a large ERP for a client and you are struggling on the first step then maybe you should look at getting some help.

Or is this just a homework type question?
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: 531
Hehehe

This was a small application that is increasing in the time, by adding other functions/modules/options.
Now i have to redesign one of the modules, and i was looking to the tables that are related with it and i was thinking that i maybe could replace some of the tables...

For example i have one section in the application where the users (workers) need to register hours for every working day, every user belong to a team, but the users can change of team, so the history doesn't be right if i use a normalized table.


Small example:

Team
----------
ID | Name
----------
1 - TeamA
2 - TeamB

Users
------------------
ID | Name | Team
------------------
1 - User1 - 1

WorkDay
-----------------------------------------
Date | User | TimeWasted | Project
-----------------------------------------
27.10.2010 | 1 | 10 | ProjectA

Later the user change to TeamB, now the table looks like this

Users
------------------
ID | Name | Team
------------------
1 - User1 - 2


If i go and try to check where the TeamB worked i'll get the wrong info because i have one row that tells me that it worked in ProjectA.




BTW - The application was built only by me.
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47247 Visits: 44377
rootfixxxer (10/28/2010)
DataWarehouses is obvious that must be denormalized. ;-)


Really?

They often are, but there's no such rule.

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.


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


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: 531
Yes there isn't any rule that tells that, but it should be... :-)



Not bothering to normalise past 1st is not denormalisation, it's lazyness.
:-D
GilaMonster
GilaMonster
SSC-Forever
SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)SSC-Forever (47K reputation)

Group: General Forum Members
Points: 47247 Visits: 44377
rootfixxxer (10/28/2010)
Yes there isn't any rule that tells that, but it should be... :-)


Should it? Always? In every case?
Why?


Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass


Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
In an transactional database, I always default to at least 3rd normal form. If you want to create some denormalized summary or staging tables on the side for the purpose of speeding up reports or fetching historical totals into an application screen, then that's OK.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
rootfixxxer (10/28/2010)

For example i have one section in the application where the users (workers) need to register hours for every working day, every user belong to a team, but the users can change of team, so the history doesn't be right if i use a normalized table.
...
If i go and try to check where the TeamB worked i'll get the wrong info because i have one row that tells me that it worked in ProjectA.
...

Actually the problem is that your existing design is not really normalized entirely. You are coding team as an "attribute" of the user in the same way as their name or date of hire. However, team membership is an "association", even if they can only be a member of one team at one time. Therfore, you need a User_Team table which relates a user with a team and also Start_Date and End_Date columns indicating when that membership was in effect. The same goes for things like User_Address, User_Phone (which would also include a column indicating "home" or "cell"), and User_Salary, because those things are also either associations or contextual over time.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
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