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
- | Toy car | 10$
- | 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:
-
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.
-
Another idea is to utilize mechanism of slowly changing dimensions (from data warehousing). Catalogs table requires additional columns like:
ID | name | price | version
-
| Toy car | 10$ | 1
-
| Teddy Bear | 2$ | 1
-
| 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