Designing pricing table (RDBMS agnostic)

Question

I'm designing a price list table for my database.

It will include Customer, Model, Start_date, End_date, Price, Currency, RRP

When I update a new price list, which is sent every now and then (maybe every 1~3 months), I need to update the prices but I would like to keep the records of what has already been raised.

Currently in system:

Customer  - Model - Start Date - End date  - Price - Currency - RRP
A         - Z     - 2015/10/20 - 2015-12-19- 120   - GBP      - 220
A         - Z     - 2015/12/20 - 2999-12-31- 100   - GBP      - 200

After updating new price:

Customer  - Model - Start Date - End date  - Price - Currency - RRP
A         - Z     - 2015/10/20 - 2015-12-19- 120   - GBP      - 220
A         - Z     - 2015/12/20 - 2016-02-20- 100   - GBP      - 200
A         - Z     - 2016/02/21 - 2999-12-31-  90   - GBP      - 180

What is the best way to update the price?

I tried to google which solved all the problems until now as most of the search results is bringing up SQL price plans :(

I have learned MySQL 9 years ago in college for a few months so I know how to interpret the SQL scripts but I am totally lost when I'm trying to create anything from scratch.

Answer 1

I have two comments about your design:

1. How do you print a whole price list composed of several products? Do you consider all rows with the same customer and same start and end date as being a single price list? Or you just don't have price lists and instead you have separate prices for different products without bundling them into price lists?
2. You should insert NULL into the end date column instead of a dummy date.

I suggest something like this:

You could have a general price list and special price lists for special clients.

Answer 2

Your scheme seems correct.

To clarify the confusion some other commentators have:

List of current prices:-

select * from tbl where End_Date = '2999-12-31'

Prices at year end 2016:-

select * from tbl where Start_Date >= '2016-12-31' and End_Date =< '2016-21-31'

Updates are simple sql. INSERT the new row then UPDATE the End_Date in the old row, as a single transaction.

Answer 3

Instead of using both Start_Date and End_Date, you might consider a single column Effective_From. This way you will achieve the same result, but inserting new price would need just adding new row, without updating any old one.

Selecting current and historical dates would need a subquery then:

select * from tbl where Effective_From = (select max(Effective_From) from tbl where Effective_From <= '2017-11-06')

It is a tradeoff for insert-heavy usage. But I think that with index on non-nullable Effective_From would make selects quite fast.

Answer 4

There is nothing wrong how you do it, however there is alternative way as well, by using versions, the table could look like this

Customer  - Model -Version - Price - Currency - RRP
A         - Z     -1       - 120   - GBP      - 220
A         - Z     -2       - 100   - GBP      - 200
A         - Z     -3       -  90   - GBP      - 180

The version can be used to link your records to other related tables, and it would give you better relationship without processing dates to find price for a given order.

Answer 5

How would you select the most recent record? Like this?

select *
from tbl
where StartDate = (select max(StartDate) from tbl)

That's a little cumbersome and seems inefficient though I haven't tested it.

Consider having a boolean isMostRecent column. This column is 0 for all records except the most recent record. In this way, here's how you'd select the most recent record:

select *
from tbl
where isMostRecent = 1

To affect this, you'll need a two step insert procedure (with a transaction):

update tbl set isMostRecent = 0 where isMostRecent = 1
insert into tbl (..., isMostRecent) values (..., 1)

With this approach, you're storing calculated fields. Here's some discussion on the pros and cons of storing calculated fields. With an index on isMostRecent, this could speed up performance. It depends on how you're using this table.

Edit: This doesn't preclude you from having StartDate and EndDate columns. You'll probably still want a StartDate column at least so you can look back in time.