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: Id, description and price
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 UPDATE's
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?
Best Answer
It looks like you have a one to many relationship between products and prices. The standard approach would be making two tables. One table can be products with the id description and anything else product related, and the other would be prices with a foreign key to products, start date and price. Yhis solves duplication and its really not hard to get the current price of anything, and easily allows for showing the price history of a product.
Another option that is a bit cumbersome is to have a table like option one, and a price history table. when you need to add a new date you insert the existing date/price into the history table and update the original row. This makes getting the active price simple, but it doesn't work well with setting future prices, and relies on triggers or procedures to ensure data integrity.
Your second option is a bad choice because every time you add a new price you have to update your code to use the new column or do some calculations on your columns to determine the correct one. Also, by adding more columns you will get to a point where your table is slow to query fairly quickly, most databases are optimized around rows expanding, lots of columns can really slow down a query.