1

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?

2

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.

  • 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. This is what I couldn't figure out. How to keep track of the price id when updating the list. Thank you very much! – GasparAlbert Dec 2 '18 at 6:20

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.