SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 Old Forums
 CLOSED - General SQL Server
 To normalize or not
 Forum Locked
 Printer Friendly
Author Previous Topic Topic Next Topic  

AskSQLTeam
Ask SQLTeam Question

USA
0 Posts

Posted - 02/26/2004 :  08:08:33  Show Profile  Visit AskSQLTeam's Homepage
shailesh writes "Hi

To what extent one should normalize a given data model keeping in view performance for OLTP database?

I have observed that most of ERP system databases are far denormalized, they maintain the data integrity procedurally and not through relationship constraints.

I guess the design / normalization of OLTP system is such as to facilitate fast input of data into the database. And for such purpose denormalized version would be just right (as implemented by ERP systems like JDEdwards)

Comments / advices / tips / suggestions.... please

Thanks
Shailesh Valera"

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/26/2004 :  09:48:45  Show Profile
www.dbdebunk.com ... do some reading
Search the internet for "Denormalization for Performance".

Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/26/2004 :  10:05:01  Show Profile
Well...yeah...they do that as in to be "flexible", or to be all things tp all people...

Peoplesoft for example has no RI...and then threse Smarstream...ugh

EXTREMELY PAINFUL

Now, that's not to say denormalization doesn't have it's place....



Brett

8-)
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/26/2004 :  10:12:22  Show Profile
Curious ... what is it's place?

Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/26/2004 :  11:07:21  Show Profile
Well ok....damn hangover....

I denoramlize for OLAP, not OLTP...

Point Page47

I had a bunch of web developers that wanted to dynamic against a 2 dimensional hierachal table (ORGXLevel of service)...

I protested...management sided with the "flash" of RAD...

And when the pages got served slower than a french restaurant, the decided that it might be in ther interest to build an over night batch process to speed up the process....

Now where did I hear that before....

Of course it was all their idea in the end....



Brett

8-)
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 02/26/2004 :  15:38:05  Show Profile
quote:
Originally posted by Page47

Curious ... what is it's place?

Jay White
{0}



Well, for fun I went out and did some searching for denormalization fro perfrmance, like you suggested. And then I read some stuff by Fabian Pascal on the concept.

While certainly interesting, Fabian's arguments really have little application to SQL based programmers and designers. He states this pretty clearly in his articles, mentioning several times that SQL DBMS are not truly relational, and do not seperate the logical table structure from the physical table structure well enough.

I do realize that all of his arguments are not invalid, and that his advice is valuable and knowledgeable, but it is also true that denormalizing the physical table structure of a SQL database does lead to application specific improvements.

Given that I haven't heard of too many file-based applications out there that match the performance and flexibility of a SQL DBMS, I don't see why, in some cases, it isn't permissible to use a SQL DBMS in a somewhat denormalized form to accomplish what you are hoping to do.

Should it be used willy-nilly by people who don't really understand it well? Certainly not. Should you make every effort to increase performance in other ways first? Absolutely. But when it comes down to it, sometimes it's necessary.
Go to Top of Page

Page47
Flowing Fount of Yak Knowledge

USA
2878 Posts

Posted - 02/27/2004 :  08:33:13  Show Profile
Just be sure that when you are capturing performance metrics against a denormalized schema, you take into consideration the added costs of maintaining data integrity. With that in mind, if the denormalized schema still comes out on top ... so be it.

In my experience, most of the time, by the time I add in all the DML to maintain data integrity for a denormalized schema, I find that I was better off normalizing. YMMV.

Jay White
{0}
Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 02/27/2004 :  10:54:36  Show Profile
I hear you. But if absolute data integrity isn't necessary on the denormalized fields, you can manage without all of the extra constraints.

Beyond that, a lot of DW applications have a certain cyclical process ("month end") where the DW is unavailable for a couple of days while all of the ETL is done and then it's brought back up for users again. It's a larged loss of elapsed time, but generally acceptable in the DW arena to maintain decent report performance throughout the rest of the month.
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 02/27/2004 :  11:30:14  Show Profile
quote:
Originally posted by crazyjoe

But if absolute data integrity isn't necessary on the denormalized fields, you can manage without all of the extra constraints.


Huh?

I spent a year cleaning up others mess because the number weren't right...

Some people made a career out of "making up" reports so they produced the right results...they just weren't based on the data in the database (until I santized the mess...and they never fixed the mess generator module)



Brett

8-)
Go to Top of Page

bm1000
Starting Member

37 Posts

Posted - 02/27/2004 :  11:49:19  Show Profile
And now for something completely different

There used to be a saying about storing data in a normalized database:
It makes as much sense as getting up in the morning, assembling your car, and driving to work. Then, at the end of the day, driving home and disassembling your car into its component pieces.

The moral of the story was to store data the way you use it.

In my opinion, normalization is a process that you go through when developing a logical data model. Once the logical data model is stable, you then develop the physical database design. The physical database design is based on the logical data model, the processes that you are going to automate, and the service level objective that the client has agreed to. If you can achieve the performance requirements of the service level objective without denormalizing, terrific. But if you are having problems meeting your SLO, denormalization may well be the answer.



Go to Top of Page

MuadDBA
Aged Yak Warrior

USA
628 Posts

Posted - 02/27/2004 :  14:59:06  Show Profile
quote:
Originally posted by X002548

quote:
Originally posted by crazyjoe

But if absolute data integrity isn't necessary on the denormalized fields, you can manage without all of the extra constraints.


Huh?

I spent a year cleaning up others mess because the number weren't right...

Some people made a career out of "making up" reports so they produced the right results...they just weren't based on the data in the database (until I santized the mess...and they never fixed the mess generator module)



Brett

8-)



I guess what I am saying is that if the denormalized data is just descriptive information, nice to have on reports but not business critical, then denormalizing it and carrying some small risk of it being out of sync is acceptible as long as it is periodically audited.

Medical diagnosis and procedure codes are a good example. The code is the important part...if someone wants to put the description on the report line for each claim, it's not as critical as long as the CODE is correct.
Go to Top of Page

Argyle
Yak Posting Veteran

53 Posts

Posted - 05/17/2005 :  13:31:34  Show Profile
Sorry for bouncing the topic but it ended up on "quote of the week" at www.dbdebunk.com
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 05/17/2005 :  13:53:50  Show Profile
Hey...look at that...he finally posted the author...but still not the lenk to the post...



Brett

8-)
Go to Top of Page

ruan4u
Posting Yak Master

USA
132 Posts

Posted - 05/17/2005 :  15:21:56  Show Profile
quote:
Originally posted by crazyjoe

quote:
Originally posted by X002548

quote:
Originally posted by crazyjoe

But if absolute data integrity isn't necessary on the denormalized fields, you can manage without all of the extra constraints.


Huh?

I spent a year cleaning up others mess because the number weren't right...

Some people made a career out of "making up" reports so they produced the right results...they just weren't based on the data in the database (until I santized the mess...and they never fixed the mess generator module)



Brett

8-)



I guess what I am saying is that if the denormalized data is just descriptive information, nice to have on reports but not business critical, then denormalizing it and carrying some small risk of it being out of sync is acceptible as long as it is periodically audited.

Medical diagnosis and procedure codes are a good example. The code is the important part...if someone wants to put the description on the report line for each claim, it's not as critical as long as the CODE is correct.


Now you mean,you had procedurecode as a table and claim as a table with link to the code and the code.Now reports require procedurecode description.Do you keep it as a part of claim/use the link to get it?
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 05/17/2005 :  15:54:18  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message
I feel a warm, fuzzy feeling inside after reading this big line of crap. I'll have job security for many more years, going behind people fixing all the screwups because they were more concerned about the here and now, and totally missed the boat on the big picture.

Ahhhhhh, the life of a DBA!!!!

How many of you guys are DBAs?

Normalization is building a car with standardized parts, instead of just "piecing it together" from whatever is available. You then don't have to spend years searching for a compatible part when something breaks instead of tearing the whole car apart and starting from scratch.



MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

USA
7020 Posts

Posted - 05/17/2005 :  17:21:37  Show Profile

Usually the people talking about de-normalization like this don't really understand normalization, so it is just BS for "I don't really know how to normalize a database, so I just threw some crap together, and said I did it for performance reasons"

What I think is funny is that the no-nothings are the ones building the applications, and then the senior people are the ones brought in to fix the mess. Kind of like having the construction laborers build a skyscraper, and then bringing in the Architects and Engineers to figure out how to keep it from falling down.








CODO ERGO SUM
Go to Top of Page

byrmol
Shed Building SQL Farmer

Australia
1591 Posts

Posted - 05/17/2005 :  17:33:19  Show Profile
quote:

Kind of like having the construction laborers build a skyscraper, and then bringing in the Architects and Engineers to figure out how to keep it from falling down.



That is a fantastic analogy Michael.

More lobster please :-)

DavidM

A front-end is something that tries to violate a back-end.
Go to Top of Page

AndyB13
Aged Yak Warrior

United Kingdom
583 Posts

Posted - 05/18/2005 :  01:46:01  Show Profile
quote:
Originally posted by Michael Valentine Jones


Kind of like having the construction laborers build a skyscraper, and then bringing in the Architects and Engineers to figure out how to keep it from falling down.




Or putting the LUNATICS in charge of the ASYLUM!!

Beauty is in the eyes of the beerholder
Go to Top of Page
  Previous Topic Topic Next Topic  
 Forum Locked
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000