Is there any reason why not to normalise?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Is there any reason why not to normalise?

  1. #1
    Join Date
    Apr 2003
    Location
    DCU, Ireland
    Posts
    5

    Is there any reason why not to normalise?

    Is there any reason not to normalise?

    Sure its great, but is there an actual concrete reason not to normalise?

    I kinda need this information urgently if anyone knows!
    Last edited by Tobey; 05-13-2003 at 03:47 PM.
    I have no idea what I'm doing...

  2. #2
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Yes...for example when you are developing a data warehouse (and decision support systems).

    Very common is the practice of take a normalized operational system and denormalize it down to sometimes a few big "mother" tables sometimes known as data stores.

    Conceptually, "fact tables" hold the keys and pointers to the data stores.

    Also....sometimes in a normal operational system, you may denormalize (not normalize in the physical design) for performance reasons....always bench mark!

    HTH

  3. #3
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    As far as DSS, OLAP, and other reporting-type databases go, yes - de-normalized is the way to go.

    As far as OLTP goes, Normalize, Normalize, Normalize!!!!

    There are just so many reasons why everything should be fully normalized. The only reason people ever give for de-normalization is 'performance'. Unfortunately, this is a simple crutch for most people who simply don't know how to write good SQL anyway (I'm not insinuating anything here, gopi ). the greater good in an OLTP database is always served with a normalized design - this includes performance.

    However, it is certainly true that some *additional*, de-normalized fields can further enhance performance. Such fields should:

    1) Always be easily distinguished from 'real' fields,
    2) Always be an addition to, and *never* a replacement of the existing, normalized fields
    3) Always be trigger-populated.

    So basically, if you have an Employee_T table and an EmployeeSalaryHistory_T table with a history of employee salaries, it might certainly be beneficial to have an Employee_T._dn_LatestSalary field on hand, where _dn denotes a trigger-populated de-normalized field. Triggers could handle keeping this *additional* field always up-to-date so that data integrity is guaranteed and no developer ever has to worry about maintaining it. As a de-normalized field, it exists to help Queries, period. If its maintenance is left up to developers, then its integrity is certainly not guaranteed and its maintenance would make it less attractive and less useful.

    So yes, *adding* de-normalized fields can help performance, but not fully-normalizing your overall design will only lead to problems down the road.

    - Chris

    (Disclaimers
    - Never say Always
    - 'Fully Normalized' has some exceptions. Such things as phone numbers, names, addresses, City&St vs Zip, etc. are generally not normalized.
    )
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  4. #4
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by chrisrlong
    3) Always be trigger-populated.
    - Chris
    Not always..it gotta downsides of it too..

    Better if we have intermediate application which acts like Trigger, when new entry or an existing entry being updated or deleted.

    Abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by abhaysk
    Not always..it gotta downsides of it too..

    Better if we have intermediate application which acts like Trigger, when new entry or an existing entry being updated or deleted.

    Abhay
    Not sure I follow you.

    As I see it, the key is that these fields need to be automatically populated. If you have a nice, tight database abstraction layer, and you can guarantee that at every single place where a record can be updated, deleted or inserted you take the proper action to keep the field populated, then fine. But you have to remember that you may not always be there to remind everyone. A big failing is often new applications going against the database that don't see these fields in the logical model or don't follow through on them. Or external data-fix scripts that neglect to update summary fields, etc.

    There are many dangers to de-normalized fields, with the biggest problem being data integrity. Therefore, IMHO, the default behaviour should be that the data integrity is always assured, so triggers keep the data correct for every situation. Now, if an external data-fix is necessary to correct a lot of rows and we find the triggers causing performance issues, *then*, with eyes wide open, we can disable them and take steps to make sure the data stays correct. But if the triggers weren't there in the first place, it would have been far too easy for these summary fields to have been forgotten about.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    After an extensive search, I've found you an excellent example of why you shouldn't denormalize.

    Read about it here
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Wow! I say wow to my successes implementing **data warehouses**, massive reporting and DSS and perhaps I need a new (or different) understanding.

    I wish Bill Inman was a member of this group not because I think I'm correct (actually I'm not certain in my response(s)) but just to hear his thoughts on the subject.

    In my free time I will spend more time at:
    http://www.billinmon.com/library/index.html

  8. #8
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Originally posted by gopi
    Wow! I say wow to my successes implementing **data warehouses**, massive reporting and DSS and perhaps I need a new (or different) understanding.

    I wish Bill Inman was a member of this group not because I think I'm correct (actually I'm not certain in my response(s)) but just to hear his thoughts on the subject.

    In my free time I will spend more time at:
    http://www.billinmon.com/library/index.html
    What did I miss?

    Nobody disagreed with you, as a matter of fact, I fully supported your position that Datawarehouses, DSS, etc (any reporting database) should be de-normalized.

    The rest of the entire discussion has focused on OLTP databases, which was probably the focus of the question anyway.

    And not to nit pick too much, but warehouses (as in, star schemas) are actually a rather rigid design system with the fact table being highly normalized and the dimensions being highly-de-normalized. So in that case, neither methodology actually wins by itself.

    And even with DSS, while the design tends more towards de-normalized than normalized, it is certainly not 'fully de-normalized', or you would likely have a single monolithic table, which wouldn't do anybody any good .

    And the name-dropping was cute, although you spelled it wrong. And actually, I follow Richard Kimball. I think his ideas about incremental data-marts are far more attainable than Inmon's all-or-nothing approach, but that's obviously a point of much contention.

    Regardless of all that, however, I stress again that nobody was actually contradicting your take on those designs. I was arguing against your statement about not fully normalizing an OLTP design, with which I disagree whole-heartedly. Even so, however, I wasn't busting on you in particular, because far too many people hold that unfortunate belief.

    - Chris
    Christopher R. Long
    ChrisRLong@HotMail.Com
    But that's just my opinion. I could be wrong

  9. #9
    Join Date
    Dec 2001
    Location
    Keene, NH
    Posts
    510
    Originally posted by chrisrlong
    Regardless of all that, however, I stress again that nobody was actually contradicting your take on those designs. I was arguing against your statement about not fully normalizing an OLTP design, with which I disagree whole-heartedly. Even so, however, I wasn't busting on you in particular, because far too many people hold that unfortunate belief.
    I am one of those people who held that belief.

    After reading yours and slimdaves valuable and accurate postings for over a year now, I knew I was kinda' playin' with fire.

    Humbled,

    Frank

  10. #10
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by chrisrlong
    Not sure I follow you.

    As I see it, the key is that these fields need to be automatically populated. If you have a nice, tight database abstraction layer, and you can guarantee that at every single place where a record can be updated, deleted or inserted you take the proper action to keep the field populated, then fine. But you have to remember that you may not always be there to remind everyone. A big failing is often new applications going against the database that don't see these fields in the logical model or don't follow through on them. Or external data-fix scripts that neglect to update summary fields, etc.

    There are many dangers to de-normalized fields, with the biggest problem being data integrity. Therefore, IMHO, the default behaviour should be that the data integrity is always assured, so triggers keep the data correct for every situation. Now, if an external data-fix is necessary to correct a lot of rows and we find the triggers causing performance issues, *then*, with eyes wide open, we can disable them and take steps to make sure the data stays correct. But if the triggers weren't there in the first place, it would have been far too easy for these summary fields to have been forgotten about.

    - Chris

    Chris :

    I think U got me wrong...i wassnt saying Triggers should not be used....only i meant in some excetion cases it will hinder performance...

    All u have said is correct...but think of situation where in ( OLTP Systems ) you have thoushands rather millions of transactions need to de done simultaneously...then having trigger would be bad idea if other wise it would hang DB..as also for one more thing it cant handle those many transactions at one go...and u may have to queue..which again calls for so many other things....

    Rather had u had a intermediate components which does all you buniness logic handled in it...and only then you allow that transaction to be performed.

    I am looking far from normal systems possibly...but yes in such diverse systems if Business logic being implemented with triggers it will suerly hinder performance...infact i wud say it wud hang DB for toss...

    Well with intermediate application...possible dirtibuted on diff systems to optimize performance Of OS...Wat we could possibly look at is OS initiate a seprate thread for 1 Tran ( Similar to COM compnents where in u jus need to call it and rest is done by it )...and if Business logic is validated...it wud go to DB...

    Handling threads at OS level wud be wise in such cases rather than a seesion ( Transaction ) initiating trigger for bussiness logic.

    And again Question arrises, how do we queue, as there is limitation on number of Tran simultaneously at DB level...we cud as well implement that in the application....


    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width