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
3rd to 20th. And then insert price for
1st to 3rdonly? 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.