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.

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.

I don't 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. 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 one advantage: 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 thinks 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.

Finally:

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.

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.