DB design for product pricing history for multiple suppliers

0
votes

I am currently trying to design DB(mysql) structure for my project which is an online shop for wholesale company - I already created everything when it comes to products, it's multiple variants etc but I have problem with following which is price and historic data for multiple suppliers:

Please find below main assumptions for the project:

  • We are going to have several suppliers for products
  • Thanks to the above each product will have few different prices
  • We want to be able to have historic price data for each product with each supplier

Variant 1

At first I thought about adding 2 tables to my DB:

suppliers table: supplier_id, name

prices table: id, product_id, price_supplier1, price_supplier2, price_supplier3, timestamp

However in such example whenever we want to add another supplier we need to add row to the database (I am not a db expert but I guess that's not the best approach)

Variant 2

Another idea was just to have price table with following:

suppliers table: supplier_id, name

prices table: id, product_id, supplier_id, timestamp

However in this case if we have 5 suppliers we get 5 records created for 1 products every single day so let's imagine that we have only 1000 products and want to keep historic data for last 6 months - such table would grow very rapidly

So to summarize - which approach is better or maybe there is a different one that I could implement? Thanks a lot for any suggestions.

1
mysqldatabasedatabase-design
Variant 2 is better. Keep the rows short, add appropriate indexes. Might want to consider keeping all of the history in one table, and also store the latest price (current price) in a smaller table (if there's a need to frequently reference the current price.) From the information given, the price history table will be less than a million rows (1000 products * 5 price per day * 183 days = 915,000 rows) – spencer7593
Thanks for your answer: I just realized that since not every product will have same set of suppliers - another table will be handy: product+supplier which will join product_id with supplier_id thanks to that our prices table should be shorter as we will store only price changes: price table: id, product+supplier_id, timestamp – neocrk
As other answers suggested, there is zero question about this. Option 2 every single time. Only question is how often you want to refer to old prices, so the suggestion to use two tables - SupplierPrice and PriceHistory are probably a good idea. Otherwise you would probably add an IsCurrentPrice flag to make for quick lookups. Forget about how big table will be. You have no choice in the long run. – TomC

1 Answers

0
votes

You should go with variant 2. It's best practice to avoid frequent table restructure, which you would have to do in variant 1 any time you add or remove a supplier (although MySQL is fairly fast at this in recent versions). Using a single column to identify the distinct supplier values is better. It also promotes query reuse when you don't have to worry about column values changing or being dropped altogether. Also, space shouldn't really be a concern. To give you an idea, if your prices table had 1,000,000 rows (6 months), it would be about 40-50M in size (assuming only a primary key index). MySQL also offers compression and partitioning to reduce storage as well, if that's really a concern.