Results 1 to 5 of 5

Thread: When to denormalize?

  1. #1

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    When to denormalize?

    I have my databased normalized like crazy and I've been thinking about histories. I already denormalize prices when a transaction occurs (store the price itself, not a reference to the price). Prices will change over time and I don't want to see sales from last year with this year's prices.

    It also occurs to me that maybe I should be denomalizing other fields like the product names in case that get removed or changed in the future too. This morning I thought about just completely denormalizing everything in a table that will be basically a history - nothing in the table will change in the future anyway. If I need to get data I can work with strings etc without to much problem (just sacrifice speed).

    What do you think?

  2. #2
    Addicted Member jg.sa's Avatar
    Join Date
    Nov 2017
    Location
    South Australia ( SA )
    Posts
    199

    Re: When to denormalize?

    G'Day pmeloy

    Are you wanting to do analysis of your system in the future, if you think there is even a chance you will need to do this.

    I wouldn't denormalize your current data because you will always need the functionality that norm... allows for.

    Just a few new 'history' tables that are denor...

  3. #3
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,120

    Re: When to denormalize?

    Quote Originally Posted by pmeloy View Post
    What do you think?
    This is not denormalization. This is what getting business requirements wrong looks like :-))

    I highly doubt the original business requirements would allow changing product name or client name in the nomenclature tables would affect past documents so that invoices from last year cannot be printed carbon copy. That would be insane :-))

    Denormalization is when you add columns for performance reasons (not for correctness reasons) either skipping a join (not very effective honestly) or skipping an aggregate (this is most effective).

    For instance keeping SUM of remaining payments on outstanding documents issued to a client in clients table would be considered denormalization which is very performance beneficial for reporting purposes. Will slow down document creation but will speed up credit limit check and/or other reports.

    cheers,
    </wqw>

  4. #4

    Thread Starter
    Addicted Member
    Join Date
    Dec 2012
    Posts
    136

    Re: When to denormalize?

    Yes current data would all be kept normalized of course (too hard and slow to keep it denormalized) but I'm thinking that each month or each year I should transfer old transactions into non referential history tables. I can still do quite a bit with strings instead of IDs but do I need to have a lot of detail when looking at March 10th sales from last year? Right now a transaction has one table for a transaction header and another table with a single row for each item in that transaction. When looking at something last year I'm thinking just one table with totals and no details about specific items. Or maybe still two tables but everything using strings and values.

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,532

    Re: When to denormalize?

    Quote Originally Posted by pmeloy View Post
    Yes current data would all be kept normalized of course (too hard and slow to keep it denormalized) but I'm thinking that each month or each year I should transfer old transactions into non referential history tables. I can still do quite a bit with strings instead of IDs but do I need to have a lot of detail when looking at March 10th sales from last year? Right now a transaction has one table for a transaction header and another table with a single row for each item in that transaction. When looking at something last year I'm thinking just one table with totals and no details about specific items. Or maybe still two tables but everything using strings and values.
    Sounds like a data warehouse to me. Denormalized data with pre-built/calculated values for reporting.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

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