| 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.... pleaseThanksShailesh Valera" |
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-02-26 : 09:48:45
|
| www.dbdebunk.com ... do some readingSearch the internet for "Denormalization for Performance".Jay White{0} |
 |
|
|
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...ughEXTREMELY PAINFULNow, that's not to say denormalization doesn't have it's place....Brett8-) |
|
|
|
Page47
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-02-26 : 10:12:22
|
| Curious ... what is it's place?Jay White{0} |
|
|
|
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 Page47I 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....Brett8-) |
|
|
|
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. |
|
|
|
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} |
|
|
|
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. |
|
|
|
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)Brett8-) |
|
|
|
bm1000
Starting Member
37 Posts |
Posted - 2004-02-27 : 11:49:19
|
| And now for something completely differentThere 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. |
|
|
|
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)Brett8-)
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. |
|
|
|
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  |
|
|
|
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...Brett8-) |
|
|
|
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)Brett8-)
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? |
|
|
|
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.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
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 |
|
|
|
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 :-)DavidMA front-end is something that tries to violate a back-end. |
|
|
|
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  |
|
|
|
|
To normalize or not