Stack Exchange network consists of 183 Q&A communities including Stack Overflow, the largest, most trusted online community for developers to learn, share their knowledge, and build their careers.
@ErwinBrandstetter I am not asking for a full worked out improvement. It is for a learning project so I'd like to do it myself. Could you just give me some pointers, keywords or references in the right direction? And second question: Is this implementation okay on it's own even without improvements?
@Akina Yeah so that means that I could be having rows which would never be used in a sale, but that’s the same in a update-history-tracking-table. I will implement one of the two solutions (update-tracking table of products OR soft-delete at every update). Still in doubt, thanks!
@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?
@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.
@J.D. Okay, what if there is a product with ID 123 named "Apples" listed for €5. And a user purchases it. Then the admin updates the price of product 123 to €7. The user still needs to see €5 in his purchase history. But if I'd reference by ID instead of copying the data to string, I'd get €7. Or do you suggest to copy only the original price in the orderline, and reference all the other data (name, description,...) by ID?
@J.D. But that also means that for every time a product gets updated (e.g the admin gives it a different name), I need to store the original product (before update) in a different row and give it the label is_deleted == true? Imagine buying a product named "Apples", and the admin renames it to "Oranges". In your purchase history the user still needs to see "Apples". So, tl;dr: Not only deletes, but also updates need to have a soft-delete?
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.
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?
