Best Method for Archiving Data

[dragongunner0351]

Hello All,
I have a DB that mimics a CRM to some extent, the area for this question I am most intrested in is in the inventory portion of the DB. Throughout the year pricing changes. For the industry I work in sometimes monthly if not weekly, at times depending on the Asian markets we have seen prices change daily, however that has happened twice in four years.

My question is what is the best way to archive previous pricing. I was thinking of creating a separate tbl to hold just archived pricing, but I have a lot of gray area in regards to how to populate the tbl, when and what method to trigger the population.

I hope I am not to vague here, and any contributions will be greatly appreciated.

Best regards

 

[PaulBricker]

I guess you are not in the oil business. How do you currently handle pricing changes?
If I was doing this, I would have a table for pricing with ProductID, EffectiveDate and Price. When a price changes for a product, you enter the ProductID, new date and new price into the table. Then use a query that takes the latest price for your products from the table based on the EffectiveDate and tie that to your other data using the ProductID. Essentially you never have to archive your pricing.

Paul