Search code examples
databaserelational-databasee-commerce

How to model a relational table that store order details

I am researching how to create a ecommerce database. After some searches, this is what I found: enter image description here

The problem here is the OrderDetails is reference back to the Products table. If I edited the Product like price, sku etc, it will affected the previously added order.

I can insert the Product data (serialized, or jsonized) in a new column in OrderDetails column when the order 1st created. But the con is it will hard to query, if I want to query specific data from the product data.

My question is how to design the OrderDetails product stick to data when the order is created, so in the future if I edited the product, it will not lost that data.

Solution

  • The problem here is the OrderDetails is reference back to the Products table. If I edited the Product like price, sku etc, it will affected the previously added order.

    Suggest you read the schema before making plainly false statements like that.

    OrderDetails referring to Products is not a "problem", it's what I would expect: a Customer mustn't order a Product that doesn't exist.

    And to answer my q about what's in the etc from your first version: OrderDetail includes price. That's a common structure: OrderDetail's price is taken from Product at the time of the Customer placing an Order. It's the price quoted to the Customer; so we are legally bound to charge the Customer that price. If the price subsequently changes on Product, that doesn't affect this Order.

    Similarly: IDSKU, Size, Colour appear on OrderDetails, so if you "edited the Product", it won't affect "previously added order".

    There's nothing "hard to query" here. If you want Product data (perhaps to place a new Order), query Product. If you want historical actual-Order data, query OrderDetails. If you want to look for discrepancies, OrderDetails INNER JOIN Product ON OrderDetails.ProductID = Product.ProductID. Now you have to be careful to dot-prefix Size, Colour for the table you want. But note that UnitPrice is distinct from Price.

    My question is how to design the OrderDetails product stick to data when the order is created, so in the future if I edited the product, it will not lost that data.

    You should now be able to answer your own question.