Maintaining hostoric data in DB-how?

[j_cocker]

I am pretty new to all this Access stuff, so please bear with me...

I am trying to get my head around a problem that's been bugging me when designing a database:

- Say you have a DB for maintaining a record of product range, prices, orders, customers, inventory, etc
- The price of each product is either held in the product table or a separate prices table with each product holding a reference to the entry in the table referring to it.
- Now let's say that a customer ordered a product - the database stores the order information, with the order table holding links (pointers) to the productID, CustomerID, etc
- A while later the price of the above product changes, and the DB is updated to reflect this

- Later still the customer asks for a re-print of their invoice (or a summary of past orders inc prices)

Now this is my problem...

How does the database ensures that the new and old orders refer to the correct prices (old and new respectively)?

I am looking for the common practice used to achieve this. Do you add a new productID when the price changes?

 

[DCrake]

This is a typical example where you store in a table a data item that you wold normally consider as being calculated. The issue is that there is a point of sale price and a current price. To maintain the correct price you need to store this against the line in your table. Then refer to this when creating invoices, statements etc. Likewise you may have a customer who currently receives a 10% discount on goods. Six months later his credit increases and you allow him 12%. Therefore at the point of sale you need to record what his discounted rate was at that moment in time. This way your historic costings will be correct.

 

[j_cocker]

Many thanks!

All should now be sorted.

 

[gemma-the-husky]

with regard to something like changing prices, there are two answers.

One - the "normal" way. Have the historic prices in a second table, and look up the effective price by reference to the date.

Two - the "un-normal" (but possibly practical way). Store (say) the last few prices and dates directly in your products/order lines table etc etc.

I think it depends how often your prices change tbh - I tend to use the second way.

 

[j_cocker]

with regard to something like changing prices, there are two answers.

One - the "normal" way. Have the historic prices in a second table, and look up the effective price by reference to the date.

Two - the "un-normal" (but possibly practical way). Store (say) the last few prices and dates directly in your products/order lines table etc etc.

I think it depends how often your prices change tbh - I tend to use the second way.

Prices shouldn't change more than once a year I would have thought. I guess this suggests that I should use probably you first solution?

 

[gemma-the-husky]

no - i tend to use option B - if the prices only change a couple of times a year, i have more than sufficient history in my order lines table.

i find a small function to step through my dates/prices in the current record is easier than manipulating non-equi joins, trying to find min/maxes of dates.

 

[j_cocker]

Right - makes sense.

Thanks again.