Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 Old Forums
 CLOSED - General SQL Server
 To normalize or not

Author  Topic 

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-02-26 : 08:08:33
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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-26 : 09:48:45
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 - 2004-02-26 : 10:05:01
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
Master Smack Fu Yak Hacker

2878 Posts

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

Jay White
{0}
Go to Top of Page

X002548
Not Just a Number

15586 Posts

Posted - 2004-02-26 : 11:07:21
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

628 Posts

Posted - 2004-02-26 : 15:38:05
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
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2004-02-27 : 08:33:13
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

628 Posts

Posted - 2004-02-27 : 10:54:36
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 - 2004-02-27 : 11:30:14
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 - 2004-02-27 : 11:49:19
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

628 Posts

Posted - 2004-02-27 : 14:59:06
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 - 2005-05-17 : 13:31:34
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 - 2005-05-17 : 13:53:50
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

132 Posts

Posted - 2005-05-17 : 15:21:56
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

4184 Posts

Posted - 2005-05-17 : 15:54:18
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)

7020 Posts

Posted - 2005-05-17 : 17:21:37

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

1591 Posts

Posted - 2005-05-17 : 17:33:19
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

583 Posts

Posted - 2005-05-18 : 01:46:01
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
   

- Advertisement -