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

Question

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.

Answer 1

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.

Answer 2

Definitively the second approach is easier to work with:

• with only one date, i.e. the start date of a new price, you don't know if a given price record is the one to use for a calculation on a given date:
  • you always have to read several records, accessed in a sorted manner to find out which price is to be used
  • even for the current price you can't be sure, as there could be already some records for planned price changes in the future
• with start and end date, you work with some redundancy. But it's much easier to make relational queries that use the right price.

When using the second approach, reverting to a previous price has first to be analyzed from a business perspective:

• is it about the old price but from a new date onwards ? In this case you just add a new price record as usual
• is it an error, and the new record should be cancelled ? In this case it's more complex:
• you have to find the previous price record and update it back by reseting the end date. Finding the previous record can either be used with a sequential access to decreasingly ordered price records, or just using the known end-date (as we know the start date of the price record to erase).
• but you also have to find any stored calculation that used the wrong price and update it
• of course, for the sakeof consistency the previous steps have to be performed within a single transaction.

For the indetermianted end-date date there are two schools:

• the purist would leave the field null to show that this information is not known.
• the pragmatist would put '31.12.9999' as end-date. This is not so great, but it allows for more consistency (end date always present) and flexibility (use of either start date or end date for sorting) in the queries. A major ERP vendor uses this approach routinely, which makes me say that it's a proven technique, even if not so beautiful as the null.

Answer 3

I agree with @Christophe in that with option 1 (a single date per row) finding the price for any given date (current or otherwise) would involve reading several records in order (the database engine will do that, not your code). The query would be like this:

select
    price
from
    price_history
where
    product_id = myProductID
    date <= myDate
order by date desc
limit 1;

This option has one advantage though: you don't have to worry about gaps and overlaps (see below). But the big disadvantage comes when you want to change time periods because you will have to play with several records and proccess them sequentially in order to move forward times.

So the second option is better, because the query would be as simple as:

select
    price
from
    price_history
where
    product_id = myProductID and
    from_date <= myDate and
    ( thru_date >= MyDate or thru_date is null);

Obviously I think the actual price should have null in the THRU_DATE column beause whe simply don't have a thru date yet.

There are some things to take into consideration however:

• The PK of the PRICE_HISTORY table must be (PRODUCT_ID,FROM_DATE), or there should be a unique index on (PRODUCT_ID,FROM_DATE) if you prefer that the PK be a surrogate.
• Mind the gaps: there's no way to, using database restrictions, to avoid gaps, i.e., periods of time with no price. So that will have to be taken care of with a trigger or a stored procedure.
• Mind the overlaps: same as above, a trigger or SP must be provided to avoid date overlapping for the same product, because that would cause an error where a query for price yields more than one row.
• Also only one row per product can have null in THRU_DATE. Trigger or SP again.

Bottom-line:

Some people prefer option one (1 date per row) because that way they don't have to mind the gaps and overlaps, but the second option is more human-readable, IMO.

In any case add a price audit table to reflect who changed what row an when. If you want to register not only old_price and new_price but also changes in from_date and thru_date, then the PK on the PRICE_HISTORY table should be a surrogate. And dont't confuse PRICE_HISTORY with PRICE_AUDIT. One table has the price of the products over time, the second holds changes made to records on the former.

See these other answer related to price changes over time, including AUDIT_TABLE and ER diagrams.

Answer 4

I'll go with option 2 like most people. One positive consequence of the FromDate and ToDate is you can elegantly implement temporary discounts without risking gaps:

Table Product_Price
Product_ID  Price  FromDate  ToDate
----------  -----  --------  --------
1           300    1/1/1900  Null
1           250    1/1/2016  8/1/2016

Then you have to do your select by getting the lowest price valid on the date of interest

Unless you have a very weird circumstance where you want a temporary higher price - in which case you need to do multiple inserts.

A permanent price change is implemented using an update of the old price with an end date and an insert of the new price.

Answer 5

In my opinion you should instead create another table as Item_Pricing and this should help you finalize the pricing history. The table Item_Pricing history should be like this Item_Pricing(id[PK], itm_id[FK], price, price_still_in_use, date_price_created,...). The idea is that the colum price_still_in_use should be considered as a flag that can have value 1(yes) or 0(no). Then in your table transaction this is the price that should be considered. Then each transaction or sale should relate to the pricing that still in use as followed :

Transactions (id[PK],...,item_pricing_id[FK] // this will point to Item_Pricing(item_pricing_id), quantity, date)

So if the item price changes in your application the price of the item should no longer be available if no longer used. A trigger might also help you for best results so that the same item can't have more than one price that has the value yes for the column(attribute) price_still_in_use