Database – When keeping product price change history, what are the pros&cons of keeping track of price change events only

databasedatabase-designrelational-database

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 price_change_date

or

  • keep effective_price_start_date and 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 null and 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)

Goal

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.

Best Answer

Domain Driven Design with Event Sourcing, would say that you store the events using an immutable entry that goes into an append-only log. You only store the event as it happens, so single event date, (i.e. no end date).

However, you also probably want to know the current price of everything, so CQRS/ES says you have another table somewhere that stores just that; the CQRS/ES design would call that table a cache, which could be thrown away and rebuilt at will. (So, they concentrate on hardening the logs not the cache.)

When storing the current price, you don't need the end date information because you only store the one current price. You could include an as-of date, if you like, but any other computation regarding old prices would probably have to go back to the event store (or store other cached information).

(Btw, the event store could be the same SQL database or something different.)

The advantage of this is that immutable append-only logs are easy in a variety of technologies (SQL, NoSQL), and that the cache that stores the current date only is a very simple table to use.

Related Topic