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

Question

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?

Answer 1

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.

Answer 2

Yes, it allows you to keep history of the prices of products. This allows reporting and metrics (used by marketing) to identify the price at which a product sold well, and sold poorly.

It's similar to having a employee HIRE_DATE and TERM_DATE columns. You don't just want to delete employees, especially for accounting and tax purposes. I was rehired at my job after a 5 year gap. Since we have Hire/Term Date columns, I (and HR) am able to see both spans easily.

With Option A, you won't be able to track the dates of a price for more than 2 revisions (at least not intuitively).

Answer 3

As an alternative to @TommCat approach, you can also make your select use an ORDER BY and a LIMIT instead of having a subquery to find your max. With appropriate indexes (the one from the PK would do, I guess), this might be a bit faster:

SELECT 
    * 
FROM 
    "Prices" p
WHERE
    p."ID" = 17
    AND p."EffDate" <= Now()  /* Today(), depending on DB */
ORDER BY
    "EffDate" DESC
LIMIT
    1 ;