Which would be some benefits of storing date ranges instead of single dates

database-designdatetime

I need to store large number of dates, along with with a price that corresponds to each date. I am considering two options: One is (a) to store a row for each date and price pair; the other is (b) to store a row with date range and price. For example:

Option a. A table that stores individual dates

CREATE TABLE Foo 
(    
    ID    INT,
    Date  SMALLDATETIME,
    Price DECIMAL
);

Option b. A table that stores date ranges

CREATE TABLE Bar
(
    ID         INT,
    RangeStart SMALLDATETIME,
    RangeEnd   SMALLDATETIME,
    Price      DECIMAL
);

Is option (b) really a good idea? The main reason for this is to decrease number of rows INSERTed. In this case, if a price is the same throught a year I would have, e.g.:

ID     RangeStart    RangeEnd     Price
--     ----------    ----------   -----
 1     2017-01-01    2017-12-31     100

instead of 

ID      RangeStart    Price 
---     ----------    -----
  1     2017-01-01      100 
  2     2017-01-02       100 
...........................
365     2017-12-31       100

Is there really a need for this, since there would be an additional overhead of converting date ranges to dates when used at an application program business layer?

Best Answer

I use only one date, the date the price took effect. That price remains in effect until the price changes. So if a price changes rarely, there will be few records; if the price changes often, there will be many records.

create table Prices(
  ID      int  not null,    -- identifies the item that is being priced
  EffDate date not null,    -- when this price took/takes place
  Price   decimal not null, -- the price
  ...,                      -- other item data
  constraint PK_Prices primary key( ID, EffDate )
);

Prices:
ID  EffDate     Price
... ...         ...
17  2016-01-01     90
17  2017-01-01    100
... ...         ...

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= Today() );

The query will return the current price, 90, every day it is executed this year. After the New Year, it will return the new current price, 100.

The query above asks the question, "What is the current price of item 17?" To ask the question, "What was/will be the price of item 17 on a particular date?" just replace "Today()" with the date.

For example, to see what the date will be on Jan 1, 2017:

select  *
from    Prices p
where   p.ID = 17
    and p.EffDate =(
        select  Max( EffDate )
        from    Prices
        where   ID = p.ID
            and EffDate <= "01/01/2017" );

Of course, using a variable instead of hard-coding the date would give you a single query which will provide the price at any time in the past, the present or any time in the future.

Related Question