I'm developing a database with a price list table for my workplace. I came with two major options, and would like to have your opinion :)
It's rather simple, containing:
On one hand, it's now working with each price on new row and having an extra field
start_date, and is the way I've found over stack
Id - description - start_date - price 1 - some item - 2018-10-30 - 500 2 - some other item - 2018-10-30 - 260 3 - some third item - 2018-10-30 - 921 4 - some item - 2018-11-15 - 525 5 - some other item - 2018-11-15 - 310 6 - some third item - 2018-11-15 - 1025
But this leads to lots of repeated description, unhandy calculation of dates when printing prices lists.
So I'm thinking on this other approach:
Id - description - 2018-10-30 - 2018-11-15 1 - some item - 500 - 525 2 - some other item - 260 - 310 3 - some third item - 921 - 1025
This way the id and description are unique, and all pricelists are beautifully ordered.
To add a new pricelist (on my case, most of the changes will be updates of this list) just do
ALTER TABLE prices ADD now() INT; followed by the
On Designing pricing table (RDBMS agnostic) seems like everyone is sticking to the first option, but
So, to the questions...
Will I have headaches doing the second?
I'm missing something?
Should I stick to the first option for some reason?