Results 1 to 4 of 4
  1. #1
    Join Date
    Oct 2003
    Posts
    84

    Unanswered: Best practices: changing values

    What is the best way to design your tables in cases where field values change?

    Example:
    CREATE TABLE Product (ProductID INT, Description VARCHAR(32), Price SMALLMONEY...);

    CREATE TABLE Purchase (PurchaseID INT, ProductID INT, Quantity INT);

    Since price obviously change over time, I was wondering what the is the best table schema to use to reflect these changes, while still remembering previous price values (like for generating reports on previous sales...)

    is it better to include a "Price SMALLMONEY" field in the purchases table (which kind of de-normalizes it) or is it better to have a separate ProductPrice table that keeps track of changing prices like so:

    CREATE TABLE ProductPrice (ProductID INT, Price SMALLMONEY, CreationDate DATETIME...);

    and have the Purchase table reference the ProductPrice table instead of the products table?

    I have used both methods in the past, but I was wanted to get other peoples' take on it.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Because price can change for many reasons, I always keep it in the actual transaction row. For example, you might have different prices for a given product based on quantity purchased (for example buying 100 units gets a price break). There might be reasons for different prices based on the customer (one price for wholesale, one for sub-contractors, another price for retail). These differences could be either discreet or cumulative. In short, the price in the inventory table might only be a starting point, the price in the transaction table is the authoritive price for a transaction.

    -PatP

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    If you want to be able to track historical prices, such as how much a price has changed over time, then you need to add a time dimension to your price table.
    But for a financial application such as this there is no substitute to storing the actual price paid in the transation table.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2004
    Location
    Kansas City, MO
    Posts
    734
    (which kind of de-normalizes it)

    No, it doesn't. It's an attribute of the purchase.

    The purchase table should have the price paid at time of purchase.

    There should be a ProductPrice table that holds the price historically for each price. If you want to avoid duplicating data, you can put the ProductPriceID in the Purchase table so you have the exact price at the time purchase was made.
    MeanOldDBA
    derrickleggett@hotmail.com
    When life gives you a lemon, fire the DBA.

Posting Permissions

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