1

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.

4
  • 2
    In option 1, would you insert a record for every single day even if the price remains the same? Why? – Rik D Dec 22 '20 at 7:49
  • That's what I'm trying to avoid by having date range. – Rohit Jain Dec 22 '20 at 8:12
  • I see, but why would you even consider it? For me a single date automatically implies that it's a starting date when the price became the new price for the item, replacing the value for any older date. – Rik D Dec 22 '20 at 8:19
  • What if I add price for 3rd to 20th. And then insert price for 1st to 3rd only? It's not always setting of price starting a given date till eternity. There is more control. Your proposed solution works, if I'm setting price in increase sequence of date. – Rohit Jain Dec 22 '20 at 8:20
3

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.

3
  • The second approach involves fewer stored rows because, if the price changes only once a year, you have one row with one price covering the range of 365 days, instead of 365 rows asserting the same price for each day of that range. – Steve Dec 22 '20 at 7:50
  • Right, I wouldn't record each day if there's no price change but use what I suggest here. You just record the date the price changes and work it out from there as needed. Your approach of storing a range is fine but you need to know in advance the end date. My view suggestion can help get around that. – LoztInSpace Dec 22 '20 at 22:26
  • I agree with this solution. To accommodate the slightly awkward query, just put the lead window function in a view and you have the best of both worlds: A nice presentation of the data and great data integrity (no room for overlapping start/end dates). – David Bachmann Jeppesen Dec 27 '20 at 22:20
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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