3

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.

7
  • 4
    What's wrong with what you're doing? You just add a new record and adjust the end date of the "previous" record. Jan 12, 2016 at 19:53
  • 1
    I agree with Dan; it seems to me you've already solved the problem. This is more or less exactly what I'd do if I needed to retain a value's history.
    – Ixrec
    Jan 12, 2016 at 20:04
  • 3
    You already have a good solution to the problem you have stated. Is your question about the mechanics of inserting a new record and updating the old record? Jan 12, 2016 at 20:06
  • Designwise your solution seems fine. If you need help with writing simple SQL statements, then please take a look at W3Schools' SQL Tutorial. Programmers isn't the right place to learn a language.
    – COME FROM
    Jan 13, 2016 at 10:51
  • 1
    This question has nothing to do with MySQL. Jan 13, 2016 at 13:32

5 Answers 5

2

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:

enter image description here

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

5
  • 1
    Dummy date is actually better than null as a " < End_Date" will always evaluate false requiring the clumsy "( ? < End_Date or End_date is Null )" Aug 8, 2017 at 4:33
  • 1
    @JamesAnderson A dummy date is a date nontheless whereas a non-existant datum is a null datum and should be left null. That's what NULL is made for. Aug 8, 2017 at 16:44
  • Also if we consider a table big enough, separating history data from active ones is a must.
    – Walfrat
    Nov 6, 2017 at 15:16
  • Can you please make it clearer what the "agreed_price" is? Also how does price_history know about which product each of its records are?
    – aderchox
    May 2, 2021 at 22:37
  • 1
    @aderchox Agreed price is a price agreed upon with a client that may differ from the official price, you may use price instead. Regarding your second question, you make a query joining PRICE_HISTORY with PRODUCT thru PRICE table. This is a conceptual diagram, PKs are omitted but infered. This query would link every row in PRICE_HISTORY with their product: select h.price, h.date_changed, r.name as product_name from price p join price_history h on (p.price_id = h.price_id) join product r on (p.product_id = r.product_id) May 4, 2021 at 10:47
0

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.

0

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.

-1

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.

1
  • 2
    ...At least with dates you can use a simple index probe for the price. With something like this, you need a greatest-n-per-group query... and that assumes that the price in effect is always the "most recent". If they were uploaded early you're in trouble. Jan 13, 2016 at 13:57
-1

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.

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.