Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

Historical Data Threads - Conventional Practice??

Discussion in 'Information Technology' started by Filibuster, Jul 28, 2006.

  1. Filibuster

    Filibuster
    Expand Collapse
    Guest

    What is the commom practice with regard to maintaining historical data (like
    product price) in a database? For example, the Northwind database (Access
    Tutorial) has a table for products (primary key = product ID). If I go into
    products and change the price of a product, that new price is reflected in
    all new orders that get entered using that product, but the new price is also
    updated in any previous orders that were already placed (which is not good).

    I can see a couple ways around this. 1) When a product attribute is
    changed, a new product ID should be created, 2) Instead of using product ID
    as a primary key - in case the product ID is some industry standard part
    number, use a sequential index number as the primary key and maintain a
    separate field for the standard product ID.

    Perhaps there are other ways of dealing with this? Does anyone know of a
    sample database that I can download that would use such a strategy? I'd like
    to not only see the database structure, but understand how a database
    application would deal with the problem in all areas (like reports, queries,
    etc).

    I hope I have explained the problem well enough. I am not sure what this
    type of question really relates to, so I had difficulty searching the
    discussions to see if other have asked the question... Thanks!
     
  2. Loading...

    Similar Threads Forum Date
    Dasam Granth, Historical Books And Rehatname (in Punjabi) Dasam Granth Jun 28, 2016
    Zafarnama, An Historical Perspective - Sukhpreet Singh Udhoke History of Sikhism May 17, 2016
    Events Collection Of Rare Historical Pictures From British India History of Sikhism Mar 10, 2015
    Save the Historical Gurdwaras in Pakistan Sikh Gurdwaras Dec 21, 2012
    Christianity The Search for Historical Paul. Which Letters Did He Really Write? Interfaith Dialogues Jul 7, 2011

  3. Douglas J Steele

    Douglas J Steele
    Expand Collapse
    Guest

    Price should not be a field in the Product table. There should be a separate
    Price table, with a PK of ProductID and EffectiveDate.

    What I typically do is have an ExpiryDate for the price as well, setting it
    to Null when inserting the "latest" row (which may be future dated). When a
    new row is inserted, I set the ExpiryDate of the "current" row equal to the
    EffectiveDate of the new row.

    Then, you can look up the price by comparing it to the date of interest.

    WHERE ProductId = <whatever>
    AND Price.EffectiveDate <= Date()
    AND Nz(Price.ExpiryDate, #12/31/9999#) >= Date()

    In this way, you can find out what the price was on any given date:

    WHERE ProductId = <whatever>
    AND Price.EffectiveDate <= #12/31/2005#
    AND Nz(Price.ExpiryDate, #12/31/9999#) >= #12/31/2005#


    --
    Doug Steele, Microsoft Access MVP
    http://I.Am/DougSteele
    (no e-mails, please!)


    "Filibuster" <Filibuster@discussions.microsoft.com> wrote in message
    news:DCAA58C1-85A2-4E56-B5FA-681252F2D121@microsoft.com...
    > What is the commom practice with regard to maintaining historical data

    (like
    > product price) in a database? For example, the Northwind database (Access
    > Tutorial) has a table for products (primary key = product ID). If I go

    into
    > products and change the price of a product, that new price is reflected in
    > all new orders that get entered using that product, but the new price is

    also
    > updated in any previous orders that were already placed (which is not

    good).
    >
    > I can see a couple ways around this. 1) When a product attribute is
    > changed, a new product ID should be created, 2) Instead of using product

    ID
    > as a primary key - in case the product ID is some industry standard part
    > number, use a sequential index number as the primary key and maintain a
    > separate field for the standard product ID.
    >
    > Perhaps there are other ways of dealing with this? Does anyone know of a
    > sample database that I can download that would use such a strategy? I'd

    like
    > to not only see the database structure, but understand how a database
    > application would deal with the problem in all areas (like reports,

    queries,
    > etc).
    >
    > I hope I have explained the problem well enough. I am not sure what this
    > type of question really relates to, so I had difficulty searching the
    > discussions to see if other have asked the question... Thanks!
     
  4. Joan Wild

    Joan Wild
    Expand Collapse
    Guest

    Filibuster wrote:
    > What is the commom practice with regard to maintaining historical
    > data (like product price) in a database? For example, the Northwind
    > database (Access Tutorial) has a table for products (primary key =
    > product ID). If I go into products and change the price of a
    > product, that new price is reflected in all new orders that get
    > entered using that product, but the new price is also updated in any
    > previous orders that were already placed (which is not good).


    I don't believe that is so. Since the price can change, you'll see that the
    price is stored in the OrderDetails table, thus storing the price at the
    time of the order. The new price in the Products table does not cascade to
    existing records, only new ones.

    If you had some need to maintain a history of product prices, you'd create a
    separate table (related to Products) which stored
    ProductID
    PriceDate (date the price was changed)
    Price


    --
    Joan Wild
    Microsoft Access MVP
     
  5. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Thu, 13 Jul 2006 08:43:02 -0700, Filibuster
    <Filibuster@discussions.microsoft.com> wrote:

    >Perhaps there are other ways of dealing with this?


    There is, actually - you can store the price as of the time of the
    order in the OrderDetails table. It appears to be redundant in that
    you have a price in both the Products table and the OrderDetails
    table, but in fact it's not: the former is the *current* price and the
    latter is the *billed* price, which are different attributes.

    John W. Vinson[MVP]
     
  6. Craig Hornish

    Craig Hornish
    Expand Collapse
    Guest

    "Filibuster" <Filibuster@discussions.microsoft.com> wrote in message
    news:DCAA58C1-85A2-4E56-B5FA-681252F2D121@microsoft.com...
    > What is the commom practice with regard to maintaining historical data
    > (like
    > product price) in a database? For example, the Northwind database (Access
    > Tutorial) has a table for products (primary key = product ID). If I go
    > into
    > products and change the price of a product, that new price is reflected in
    > all new orders that get entered using that product, but the new price is
    > also
    > updated in any previous orders that were already placed (which is not
    > good).


    Sorry, unless you are doing somthing different you are incorrect about
    updating a product price in the product table and having it changed in the
    order details. - The price in the order details is a 'stand alone' field.
    It is the price that was current at the time that it was entered into the
    form, and could have been changed there also. If you use a query that has
    the product table and Order detail table you can see the current price from
    the Product table and the price it was sold for a particular detail line.

    > I can see a couple ways around this. 1) When a product attribute is
    > changed, a new product ID should be created, 2) Instead of using product
    > ID
    > as a primary key - in case the product ID is some industry standard part
    > number, use a sequential index number as the primary key and maintain a
    > separate field for the standard product ID.


    > Perhaps there are other ways of dealing with this? Does anyone know of a
    > sample database that I can download that would use such a strategy? I'd
    > like
    > to not only see the database structure, but understand how a database
    > application would deal with the problem in all areas (like reports,
    > queries,
    > etc).


    I strongly disagree with Douglas about the price not being in the product
    table. It is a desision that you need to make for yourself. If you don't
    need to know when exaclty the price has changed then why bother with another
    level of complexity.

    > I hope I have explained the problem well enough. I am not sure what this
    > type of question really relates to, so I had difficulty searching the
    > discussions to see if other have asked the question... Thanks!


    I think Doglas missed part of your question - regarding the information in
    Order Detail table. And just to be clear I believe (please confirm)
    Doublas's Price Table is linked to the Product Table and the ID for the
    price would NOT be linked to the Order Detail Table. You would put the
    value of the price into the Order Detail table just as it is done now. (One
    reason the Order Detail table has a value (instead of linked to some price
    table) is so that it can be change in a "specific" dollar amount as part of
    the order entry for things other than a standard %Discount as in Northwind.)

    Craig Hornish
     
  7. Linc

    Linc
    Expand Collapse
    Guest

    You will probably also need to store any tax rate(s) that apply as well in
    your OrderDetails table for the same reasons. Canada's GST rate just dropped
    1% July 1st.


    "Filibuster" wrote:

    > What is the commom practice with regard to maintaining historical data (like
    > product price) in a database? For example, the Northwind database (Access
    > Tutorial) has a table for products (primary key = product ID). If I go into
    > products and change the price of a product, that new price is reflected in
    > all new orders that get entered using that product, but the new price is also
    > updated in any previous orders that were already placed (which is not good).
    >
    > I can see a couple ways around this. 1) When a product attribute is
    > changed, a new product ID should be created, 2) Instead of using product ID
    > as a primary key - in case the product ID is some industry standard part
    > number, use a sequential index number as the primary key and maintain a
    > separate field for the standard product ID.
    >
    > Perhaps there are other ways of dealing with this? Does anyone know of a
    > sample database that I can download that would use such a strategy? I'd like
    > to not only see the database structure, but understand how a database
    > application would deal with the problem in all areas (like reports, queries,
    > etc).
    >
    > I hope I have explained the problem well enough. I am not sure what this
    > type of question really relates to, so I had difficulty searching the
    > discussions to see if other have asked the question... Thanks!
     
  8. Filibuster

    Filibuster
    Expand Collapse
    Guest

    Thanks All, for the great replies. Joan and Craig - you are correct - Unit
    Price is adifferent field in Order Details, and Products tables. That is a
    subtlety that I missed on my inspection of Northwind. BTW - I am actually
    creating a project forcasting application - but rather than explain the
    project, I picked Northwind as an example others might be familiar with - I
    should have spent a few more minutes understanding the example!

    In any case, you have all provided me with several good alternatives (and a
    better understanding of Northwind), and I really appreciate your help!
     
  9. Debba

    Debba
    Expand Collapse
    Guest

    I have been looking for a similar answer. my problem is that i do not store
    the price anywhere in my database, i just have on my form a text box thats
    control source is a formula to calculate the cost for example;
    Total cost textboxes formula is = [numberofcars]*43 So i am calculating
    the number of cars checked by $43 per car. The problem is I have historical
    data where the price used to be $30 but now when i look the old records up,
    it is calculated at $43 because the formula is the same in the form. Can
    anyone help!
    thanks

    "Filibuster" wrote:

    >
    > Thanks All, for the great replies. Joan and Craig - you are correct - Unit
    > Price is adifferent field in Order Details, and Products tables. That is a
    > subtlety that I missed on my inspection of Northwind. BTW - I am actually
    > creating a project forcasting application - but rather than explain the
    > project, I picked Northwind as an example others might be familiar with - I
    > should have spent a few more minutes understanding the example!
    >
    > In any case, you have all provided me with several good alternatives (and a
    > better understanding of Northwind), and I really appreciate your help!
     
  10. John Vinson

    John Vinson
    Expand Collapse
    Guest

    On Wed, 26 Jul 2006 19:46:02 -0700, Debba
    <Debba@discussions.microsoft.com> wrote:

    >I have been looking for a similar answer. my problem is that i do not store
    >the price anywhere in my database, i just have on my form a text box thats
    >control source is a formula to calculate the cost for example;
    >Total cost textboxes formula is = [numberofcars]*43 So i am calculating
    >the number of cars checked by $43 per car. The problem is I have historical
    >data where the price used to be $30 but now when i look the old records up,
    >it is calculated at $43 because the formula is the same in the form. Can
    >anyone help!


    NOBODY can help.

    What was the price when the 317th sale occured?
    You don't remember?

    Neither does the database.

    If you don't have the price stored anywhere, then there is no way -
    even in principle - to determine what the price used to be.

    John W. Vinson[MVP]
     
  11. Joan Wild

    Joan Wild
    Expand Collapse
    Guest

    That is why you need to store the price. Unless you have some
    files/information that tells you when the price changed, you're out of luck.

    If you do have the information, you'd need to add a price field to your
    database, and then run update queries to correct the information.


    --
    Joan Wild
    Microsoft Access MVP

    Debba wrote:
    > I have been looking for a similar answer. my problem is that i do
    > not store the price anywhere in my database, i just have on my form a
    > text box thats control source is a formula to calculate the cost for
    > example;
    > Total cost textboxes formula is = [numberofcars]*43 So i am
    > calculating the number of cars checked by $43 per car. The problem
    > is I have historical data where the price used to be $30 but now when
    > i look the old records up, it is calculated at $43 because the
    > formula is the same in the form. Can anyone help!
    > thanks
    >
    > "Filibuster" wrote:
    >
    >>
    >> Thanks All, for the great replies. Joan and Craig - you are correct
    >> - Unit Price is adifferent field in Order Details, and Products
    >> tables. That is a subtlety that I missed on my inspection of
    >> Northwind. BTW - I am actually creating a project forcasting
    >> application - but rather than explain the project, I picked
    >> Northwind as an example others might be familiar with - I should
    >> have spent a few more minutes understanding the example!
    >>
    >> In any case, you have all provided me with several good alternatives
    >> (and a better understanding of Northwind), and I really appreciate
    >> your help!
     

Share This Page