1

I have been working on a project, when I hit a design dilema for what I believe is quite common situation. Please let me know how do you handle it. Let's assume that we are building a webapplication for a shop. There is a catalogs table, with a number of rows, e.g.

ID | name | price

  1. | Toy car | 10$
  2. | Teddy Bear | 2$

And orders table with payment means. How should I store data about order details, meaning product bought? The main concern is how to handle changes in catalogs table like price modification.

Solutions I came up with are:

  1. Have a table order_details with structure similar to catalog and many-to-one relation with orders table. When a user buys sth, necessary data is copied to order_details table from catalogs table. This requires big storage as we copy all(or at least some) of the columns, and in case of column modification in catalogs table, there is a need to propagate the changes to order_detals.

  2. Another idea is to utilize mechanism of slowly changing dimensions (from data warehousing). Catalogs table requires additional columns like:

ID | name | price | version

  1. | Toy car | 10$ | 1

  2. | Teddy Bear | 2$ | 1

  3. | Teddy Bear | 4$ | 2

And order_details table with many-to-one relation to orders table and many-to-one relation with catalogs table. In this case there is the same number of rows in order_details table, but there is smaller number of columns. The con of this approach is a need to manage the versions of products.

Dariusz Dudziński
  • 197
  • 9
  • 1
    duplicates: http://softwareengineering.stackexchange.com/questions/258234/best-approach-for-an-online-store-which-changes-its-product-s-price-over-time; http://stackoverflow.com/questions/3469803/how-to-handle-price-fluctuations-in-an-invoice-application; http://stackoverflow.com/questions/11818965/how-to-properly-relate-items-to-pricing-data-taking-price-change-history-into-a – jaco0646 Jan 19 '17 at 16:57
  • you are right. I was looking for sth like it, just when it comes to design questions sometimes it is hard ask the "right question" – Dariusz Dudziński Jan 23 '17 at 09:43

2 Answers2

2

An answer to a similar question is here which discusses Version Normal Form.

You need to normalize the price (and any other data you want to track) to a separate table. Your FKs will continue to work normally. All you have to do is get the price that was in effect when the order was made. This is a bit tricky but not difficult.

Community
  • 1
  • 1
TommCatt
  • 5,498
  • 1
  • 13
  • 20
1

In general your db design depends on your shop application logic. But I can guess that this approach will help you:

Create a table catalog_prices (priceId, productId, price, date) and in catalogs table change price column to priceId. So, every product will have one current price and table catalog_prices will have product price history. So, query 

SELECT c.ID, c.name, p.price FROM catalogs AS c 
INNER JOIN catalog_prices AS p ON c.priceId = p.priceId

will return all products with prices and query 

SELECT c.ID, c.name, p.price, p.date FROM catalogs AS c 
INNER JOIN catalog_prices AS p ON c.ID = p.productId

will return all price history.

Next in order_details table add productPriceId column. So query 

SELECT d.ID, d.orderId, c.name, p.price FROM order_details AS d 
INNER JOIN catalog_prices AS p ON p.priceId = d.productPriceId
INNER JOIN catalogs AS c ON p.productId = c.ID

will return order details with prices.

This solution will add additional join for showing catalog products and ordered products but allows reduce data duplication – prices will be stored in one place. And you will be able to see product price history.

Vasyl Zvarydchuk
  • 3,789
  • 26
  • 37