Database Design: Storing price date wise, or date range wise?

Question

I must store prices for various items on multiple dates. The table schema would look like this:

CREATE TABLE date_wise_price (
    item_code varchar,
    date date,
    price numeric(19,4)
)

An alternate table schema for this could be to store prices date range-wise, which results in a smaller number of records stored and loaded in-memory. An example would be for prices of 100 days that are the same.

CREATE TABLE date_wise_price (
    item_code varchar,
    date_range date_range,
    price numeric(19,4)
)

The problem with the second approach has to do with the unique price entry for each date. This table may start bloating, unless we split the previously conflicting date range everytime we enter new data.

I need suggestions on what approach I should take.

• The first approach gives me the unique price entry per date. I can have a unique constraint on that.
• The second approach gives me a smaller number of records to be loaded, thus minimising the stress on memory consumed while processing data for multiple items for let's say a year. We're looking at creating 1 entity object vs 300 entity objects.

Is there a way to club same price on continuous days, into date range, while loading data from first table or not? That will give me the best of both approaches.

NOTE: Actual table to store price will have a greater number of columns.

---

It seems there is some confusions with my question. Let me clarify.

Price of an item defined via item_code can be different on different days. It can be the same too. We can store prices for future dates too. And price updates can happen for various dates out of order. Controlled by revenue manager.

Date            Price
2020-12-20      200
2020-12-21      200
2020-12-22      200
2020-12-23      250
2020-12-24      250
2020-12-25      300

At any point of time, the revenue team can change prices for a single date, or multiple dates.

If I store the price for 365 days using the above model, I'll always have 365 rows stored and loaded if I want to show the price for 365 days. Even if prices are the same for all days.

If I store using date_range model, it'll be 1 single row:

Start Date       End Date        Price
2020-12-20      2020-12-25       200

But when we insert a new record, it'll be like this:

Start Date       End Date        Price
2020-12-20      2020-12-25       200
2020-12-23      2020-12-27       250

So we've duplicate prices for 23, 24, 25 now. In fact, that table will keep on growing, unless I continue splitting previous conflicting date ranges, and eventually delete them, when they become redundant.

In date-wise price, I just updated the price for the given date. It's essentially INSERT vs UPDATE query.

Answer 1

I don't really understand why the second involves fewer rows.

Here's what I would do: Use the first schema but interpret your date as the date from which the price was effective. Then when you want a price for a given date you just pick the earliest one that was >= the date you're interested it. You get a unique constraint (good), minimal storage(good) and a slight awkward query (not great).

The query bit can usually be resolved with a view containing lead/lag window functions in it to provide the end of one period being the day before the start of the next one. You can then use BETWEEN

That might be what you meant in your first option but subsequent paragraphs make that unclear.

Answer 2

I would separate the date range into a separate table and use a foreign key to link each price to it:

• Item(item_code, (...) )
• ItemPrice(item_price_id, item_code, price_date_range_id)
• PriceDateRange(item_date_id, start_date, end_date)

Using this may complicate the SQL a little, but have other benefits:

• You can adjust each price range however you like, and set it to e.g. a single day, several days, or unlimited (by setting end_date = null).
• You can use the same date range for several different items (add several records to ItemPrice with ref. to the same PriceDateRange record, but different Item records and corresponding prices). This might, in turn, allow you to read and import less date-range data into memory (Example: If a large part of your items are on sale for the same period of time, no need to fetch and handle separate time-data repeatedly for each item).

I also have a feeling this kind of separation might provide more flexible historic records, in case you might want to look up statistics about price and changes over time later.

Sidenote: Overlapping date ranges

As for issues with overlapping date-ranges, this may not be a problem, so long as you add appropriate rules to handle them: You might for instance decide that if there are overlapping prices for any item for a given date, then the lowest (or highest?) price should always be selected.

Such a rule would enable you to set a default price for an item and then override that price for a limited period by adding a new overlapping price range (with a reduced price) which is valid only for a shorter period.