Handling Product Price Updates Without Affecting Past Orders

[signal ice]

Consider the following database structure:
• A products table that stores product information.
• An orders table where each order can have multiple orderItems (stored in a separate table).
• The orderItems table includes a reference to a product.

Now, if an admin updates a product’s details (e.g., changes the product price), this update will also affect the sale price when viewing past orders.

How would you handle this behavior to ensure the sale price in past orders remains unchanged?

[queen cosmos]

Put the final price on the orderItems table. It can still reference the products table, but once the order is placed, the price is no longer a property of the product, it's a property of the orderItem.

Look at Stripe's API documentation for how they model these kinds of entities.