Post reply

Normalizing Data

  • May 11, 2005 at 3:27 pm

    #362584

    I've inherited a database with some financial data. Our reports look at the change in inventory from the beginning of the year (BOY) to end of year (EOY). BOY and EOY are separate, because they are actually measured at the end of January and the end of December. I know its strange, but bear with me... The existing design puts this on separate records, but it seems to me that combining EOY and BOY is more efficient (change = EOY-BOY) instead of a CASE statement to flip the signs with a SUM and a GROUP BY.

    My problem is trying to put this into a normalization discussion. The designer says this is just like entering batting averages with one player's average on each record. I say, it is different because we want the change.

    Thanks.

  • May 12, 2005 at 1:50 am

    #558285

    Depends on your physical set up and business logic. Change in inventory over a period is the sum of orders received and/or production less goods delivered. Alternately, if you are in a business with (unknown) stock losses because of pilfering or other problems, you may do a physical stock take. In the latter case, it is necessary to record the physical stock levels.

    If you are doing a calculation, you don't need to store anything since you can always rerun the calculation. However, if the calculation is complex, then you may wish to snapshot the data. If you do, it is best to store the data in a way that may have longer term implications, e.g. what happens if you want to examine changes in January? If you only store the change, then you lose the ability to do this.

    Normalisation is not an exact science because the degree to which you normalise the data depends on what you want to do with it.

  • May 12, 2005 at 9:57 am

    #558464

    Thanks for the suggestions. In particular to Joe for putting into words the concept I was struggling with about row and entity. For our system, it is inventory change that matters so EOY and BOY are different attributes of the same data element.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply