0

When a user makes a order I create a Order-object with the following data:

+-----------+
|   Order   |
+-----------+
| ID        |
| user      |
| vendor    |
| timestamp |
| ...       |
+-----------+

For each product the user has ordered a OrderLine is linked to the Order.

+---------------------------+
|         OrderLine         |
+---------------------------+
| ID                        |
| order_id (FK)             |
| product_id (FK)           |
| quantity                  |
| price_on_time_of_purchase |
+---------------------------+

This way I can keep a history for each user of what products he has ordered, and display that history in his purchase-overview.

But in the catalog, products can be updated or totally removed from the database. So if a user would purchase a product with ID 123 and that product gets deleted later, the information (name, description,...) of product 123 cannot be displayed in the users' overview anymore.

A solution would be to only "soft delete" products, so the information of all products would remain accessible at all times.

But, would it also be reasonable to not store the ID's of products in OrderLine but rather the product's name and description as a string?

So instead of doing

+-----------+----------+------------+----------+---------------------------+
| OrderLine |          |            |          |                           |
+-----------+----------+------------+----------+---------------------------+
| ID        | order_id | product_id | quantity | price_on_time_of_purchase |
| 1         | 5        | 3          | 5        | 3.25                      |
+-----------+----------+------------+----------+---------------------------+

I would do

+-----------+----------+-----------------------------+----------+---------------------------+
| OrderLine |          |                             |          |                           |
+-----------+----------+-----------------------------+----------+---------------------------+
| ID        | order_id | product                     | quantity | price_on_time_of_purchase |
| 1         | 5        | My Product with description | 5        | 3.25                      |
+-----------+----------+-----------------------------+----------+---------------------------+

This way I can keep showing the user an accurate history of his purchases at all time, without keeping a history of all the products.

So... Is storing product-information (name, description,...) as a string in OrderLines acceptable. Or should I stick to storing ID's and soft-deleting?

8
  • A solution would be to only "soft delete" products This is not a solution. The product properties may be altered, which will alter the data in all existing orders lines.
    – Akina
    Commented Nov 17, 2022 at 5:12
  • @Akina I’ll make the properties of Product non-updatable. Only the price will be updatable. But the actual price is stored in the OrderLine each time.
    – O'Niel
    Commented Nov 17, 2022 at 5:17
  • I’ll make the properties of Product non-updatable. Something will definitely make you change that.. it is more safe to take this possibility into account before it occures.
    – Akina
    Commented Nov 17, 2022 at 5:19
  • @Akina So you'd highly advise to make a separate table to track the updated attributes of each product? Seems reasonable. But isn't the easier solution to soft-delete and create a new row each time a product is updated?
    – O'Niel
    Commented Nov 17, 2022 at 5:36
  • The solution with soft-delete may produce the rows which are used in none purchase if there was not a sale between two row editions. This is not a problem, of course.. From the other side for soft-delete solution you must add actuality column and refer to it in any query while creating an order.. I don't know does this solution is more easy.
    – Akina
    Commented Nov 17, 2022 at 5:40

3 Answers 3

1

Storing extra information here is denormalization. You risk data inconsistencies once you does this: You need to update those name, descriptions in this table when they're updated in another table.

imo in your use case, soft-deleting is a better idea

9
  • How can there be data inconsistency when the only use-case of an OrderLine is to store the information of the product relevant on the given timestamp? Isn't 'inconsistency' not what we are trying to achieve in this specific scenario?
    – O'Niel
    Commented Nov 17, 2022 at 4:34
  • Because I do NOT need to update the product-name when updated. I need to show the user the exact product-name and -description which it was on the time he ordered the product.
    – O'Niel
    Commented Nov 17, 2022 at 4:41
  • @O'Niel What if there was an error in the ProductName, such as a typo?...by storing it in multiple tables, you need to remember to update multiple tables to fix it, which is more error prone and less efficient. You're better off storing the ProductId when they need to be deleted instead, and soft-deleting the Products like this answer suggests
    – J.D.
    Commented Nov 17, 2022 at 4:58
  • 1
    @J.D. Alright, I think I get it... I will store price and quantity in OrderLine to have the actual price on time of purchase. And in OrderLine I will reference the product by ID. The attributes of Product (name, description,...) will be non-updatable.
    – O'Niel
    Commented Nov 17, 2022 at 5:10
  • 1
    Storing extra information here is denormalization - it is not (by default) denormalization if it is stored for a specific reason - the same way sales price and other attributes necessary for legal reasons are stored. Given the nature of the problem it's maybe not the best solution, but it is not denormalization the same way bank statements and recording sales price are not denormalization.
    – user212533
    Commented Nov 17, 2022 at 15:06
0

But in the catalog, products can be updated or totally removed from the database.

If so then the product in the products table is a pattern and not an entity. I.e. you must not refer to it but copy its data into the order line. The pattern instance (in products) and the entity instance (in order_lines) are not related.

But this de-normalizes the data.

As a solution you may create additional table products_in_orders, copy current product into it (if it is not present yet in current state - test all columns values, or test product_id and product_state_timestamp), and refer product_id to this table instead of products table.

0

Strings and JSON are ok for storing information that will not need to be parsed and used in a WHERE or ORDER BY clause.

Likewise, numbers and dates should not be stored in VARCHARs.

INDEXes are the key to performance; they can only work on values they can get to.

For "small" databases this advise does not matter; for large datasets, it can matter a lot.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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