How do I create a table such that historical prices are kept?

[Gavroche00]

I am a total newbie, so I am sure this is more then basic, yet I can't figure it out.

I have a table of truckroutes (truckroute# [primary key], origin, destination, miles, gallons...). Each route has a price. If I include the price of the route in the truckroute table, when i will change the prices, all the historical data will reflect the new price - no good. I need to create a second table, and link it to the truck route table, but I can't figure it out. Also, what would be my primary key in that second table?

Any help is apreciated.

Gavroche

 

[BNPMike]

Truckroute#,Start_date is the key. You may want to add End_date, which is not part of the key. This may make it easier to process.

 

[Gavroche00]

Dear BNPMike,

I am not sure I undesrtand your reply. Are you saying to create a new table with a combined key of truckroute# AND start date? If so, when I update the price for a specific route, I will duplicate the row, then type a new start date and then change the price for the route?

 

[BNPMike]

Move prices from your main table to the new table. Maybe it will be:
(Truckroute#, Startdate,, Enddate, Price)

Set Enddate to null. To get a truckroute and a price you need to join the new table to the old table on truckroute and then pick the record you want using where conditions. To get today's price you can select on Enddate is null. For historic dates you obviuosly need to use both dates.

When you change a price, select the record with null Enddate and set it to the day before the new start date, then add a new record with Startdate=NewPriceDate, and the Enddate null.

 

[Gavroche00]

That is what I thought you meant, thank you for taking teh time to clarify a second time.