jeudi 19 janvier 2017

Changing reference data and maintaining "history"

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.

Aucun commentaire:

Enregistrer un commentaire