Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Normalization vs Denormalization Expand / Collapse
Author
Message
Posted Thursday, October 28, 2010 5:16 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:47 AM
Points: 104, Visits: 433
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
Post #1012211
Posted Thursday, October 28, 2010 5:51 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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.
Post #1012229
Posted Thursday, October 28, 2010 7:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:47 AM
Points: 104, Visits: 433
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.

Post #1012282
Posted Thursday, October 28, 2010 7:23 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Saturday, March 15, 2014 4:31 AM
Points: 2,808, Visits: 7,173
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?
Post #1012292
Posted Thursday, October 28, 2010 7:57 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:47 AM
Points: 104, Visits: 433
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.
Post #1012319
Posted Thursday, October 28, 2010 8:51 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 41,531, Visits: 34,449
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 2008, MVP
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

Post #1012374
Posted Thursday, October 28, 2010 9:10 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, April 17, 2014 8:47 AM
Points: 104, Visits: 433
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.

Post #1012401
Posted Thursday, October 28, 2010 9:14 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 7:05 AM
Points: 41,531, Visits: 34,449
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 2008, MVP
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

Post #1012404
Posted Thursday, October 28, 2010 9:18 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:40 PM
Points: 1,477, Visits: 4,281
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.



"Winter Is Coming" - April 6, 2014
Post #1012409
Posted Thursday, October 28, 2010 9:32 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 18, 2014 12:40 PM
Points: 1,477, Visits: 4,281
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.



"Winter Is Coming" - April 6, 2014
Post #1012423
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse