1

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?

5
  • 1
  • 1
    Your data sample #2 doesn't show that the price is the same throughout the year, so it's not equivalent to data sample #1.
    – Andriy M
    Dec 9, 2016 at 7:00
  • @AndriyM I am not asking how to do it, I am asking is there a good reason to do it. Regarding your second comment, you are right, I have edited my question.
    – mko
    Dec 9, 2016 at 7:59
  • 1
    That question seemed to me to have some relevant reasoning to take into consideration, that was why I linked it.
    – Andriy M
    Dec 9, 2016 at 10:25
  • Option B is typically faster for querying the price that is valid for a specific date, e.g.: where xx >= rangestart and xxx < rangeend
    – user1822
    Dec 9, 2016 at 15:42

3 Answers 3

2

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.

1

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).

0

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 ;

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.