Normalization vs Denormalization

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » Database Design » Relational Theory » Normalization vs Denormalization

14 posts, Page 1 of 2

1 2 »»

Normalization vs Denormalization

Rate Topic

Display Mode

Topic Options

Author

Message

rootfixxxer

Posted Thursday, October 28, 2010 5:16 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:30 AM
Points: 89, Visits: 368

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

steveb.

Posted Thursday, October 28, 2010 5:51 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 2,802, Visits: 7,107

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

rootfixxxer

Posted Thursday, October 28, 2010 7:08 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:30 AM
Points: 89, Visits: 368

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. Wink

Post #1012282

steveb.

Posted Thursday, October 28, 2010 7:23 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 10:07 AM
Points: 2,802, Visits: 7,107

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

rootfixxxer

Posted Thursday, October 28, 2010 7:57 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:30 AM
Points: 89, Visits: 368

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

GilaMonster

Posted Thursday, October 28, 2010 8:51 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

rootfixxxer (10/28/2010)

DataWarehouses is obvious that must be denormalized. Wink

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

rootfixxxer

Posted Thursday, October 28, 2010 9:10 AM

SSC Journeyman

Group: General Forum Members
Last Login: Monday, May 20, 2013 8:30 AM
Points: 89, Visits: 368

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

Not bothering to normalise past 1st is not denormalisation, it's lazyness.

Post #1012401

GilaMonster

Posted Thursday, October 28, 2010 9:14 AM

SSC-Dedicated

Group: General Forum Members
Last Login: Today @ 3:18 PM
Points: 37,744, Visits: 30,025

rootfixxxer (10/28/2010)

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

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

Eric M Russell

Posted Thursday, October 28, 2010 9:18 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 1,164, Visits: 3,340

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.

"Wise people understand the 10,000 things without going to each one.
They know them without having to look at each one,
and they transform all without acting on each one." - The Tao Te Ching: Verse 47

Post #1012409

Eric M Russell

Posted Thursday, October 28, 2010 9:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Yesterday @ 9:21 AM
Points: 1,164, Visits: 3,340

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.

Post #1012423

« Prev Topic | Next Topic »

14 posts, Page 1 of 2

1 2 »»

Permissions