When tracking something like pricing history, in my relational database, what is a recommended way to store pricing changes? For example I can:
- keep price change events only (aka a single date indicating new price was entered). One date per one price history record. aka
effective_price_end_date(aka, when entering new price, old record gets and updated "end date", and new record gets current date as "start date" and
null(?) for "end date"). That is two dates for each price history record.
I am thinking that the first approach - keep only the single date record that means "this is the date the price has been entered" is sufficient. And perhaps a bit simpler to maintain.
However I have seen suggestions in other answers that recommend keeping effective duration of the price, i.e. price start date and end date. But in that approach there are complications such as
- what if I want to go back to previous pricing? Do I just create a new record with the same price value but different dates?
- I now have to deal with corner issues like - I've just entered a new price, what do I place for effective end date when it's indeterminate? Do I place
nulland then interpret it as "currently active"?
Are there any specific issues that I will face if I choose to go with the first approach (one date column) rather than the second approach (two date columns per record)
The goal is auditing purposes and customer questions. i.e. HR asks what did we charge for part X in January of 2015? How do we find out?
Or customer says why are you charging me $200 more for this compared to last year when I ordered from you? Sales can go back and say oh yes we have changed the price back in December for this or that reason.
How changes come about -- say HR directs that in January we should charge $100 for part X, but starting on February charge $200.