Historical Data Threads - Conventional Practice??

[Filibuster]

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!

 

[Joan Wild]

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

 

[Douglas J Steele]

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#

 

[John Vinson]

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]

 

[Craig Hornish]

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

 

[Linc]

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]

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!

 

[Debba]

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

 

[John Vinson]

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]

 

[Joan Wild]

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

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

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!

 

[Debba]

Ok, I realise that no one can help, but I will add a products table and
include the price but after reading all the threads, I am not understanding
just how to work this with my orders form....when i query a past date, how
does it gather the correct price from the table? how exactly do i set all
this up? complete idiot here sorry!

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

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

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!

 

[Joan Wild]

Yes you add the current price to the products table. In addition you also
add the price to the table where you store the numberofcars.

When you create a new record, it will look up the current price in the
products table and store it in the second table.

If you open Northwind (comes with Access), you'll see an example of how this
is done in the Orders form.


--
Joan Wild
Microsoft Access MVP

Ok, I realise that no one can help, but I will add a products table
and include the price but after reading all the threads, I am not
understanding just how to work this with my orders form....when i
query a past date, how does it gather the correct price from the
table? how exactly do i set all this up? complete idiot here sorry!

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

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

:


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!

 

[Debba]

Thanks alot Joan for your help, will try this out.

Yes you add the current price to the products table. In addition you also
add the price to the table where you store the numberofcars.

When you create a new record, it will look up the current price in the
products table and store it in the second table.

If you open Northwind (comes with Access), you'll see an example of how this
is done in the Orders form.


--
Joan Wild
Microsoft Access MVP

Ok, I realise that no one can help, but I will add a products table
and include the price but after reading all the threads, I am not
understanding just how to work this with my orders form....when i
query a past date, how does it gather the correct price from the
table? how exactly do i set all this up? complete idiot here sorry!

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

:


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!

 

[aaron.kempf]

this is called a 'type 3 slowly changing dimension'

-Aaron

 

[aaron.kempf]

I frequently store historical price for example; and then I have an
audit table where I can keep changes to dimension tables and mimic a
slowly changing dimension-- but it doesn't take as much effort

a lot of times; companies base a new set of prices once a quarter or
something; you might not need to get it sucked down to a particular
date for example.

like you might store; for a quarter-- a particular price

it just depends on what's easiest and best; and how you company does
their internal math

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]