0

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.

dev-jim
  • 1,736
  • 2
  • 28
  • 52
  • Ask 1 (specific researched non-duplicate) question. – philipxy Sep 28 '20 at 19:58
  • Where did you get your schema, and why do you think this is "Normal", and what do you think is hidden in your `etc`? Read up on typical product identifiers: usually there's a 'SKU' (Stock-Keeping Unit), and usually it has some coding structure style-colour-size for garments. Usually there's a 'Product' table defining SKUs, with a `Foreign Key` reference from `order-item-details` to `Product`, such that you can't delete products or prices if there have been orders for them. – AntC Sep 28 '20 at 22:03
  • @AntC, I saw it in some tutorials. Please refer to my updated question. – dev-jim Sep 29 '20 at 05:05
  • @philipxy, please refer to my updated question. – dev-jim Sep 29 '20 at 05:06
  • 3 question marks & an I'd like to know how. See my 1st comment. Follow a published academic textbook on information modelling, the relational model & DB design & querying. (Manuals for languages & tools to record & use designs are not such textbooks.) (Nor are wiki articles or web posts.) Ask 1 specific researched non-duplicate question where stuck. Basic questions are SO faqs. PS Please don't insert EDITs/UPDATEs, just make your post the best presentation as of editing time. Adding to something unclear doesn't make it clear. – philipxy Sep 29 '20 at 05:34
  • This is improved. But: You aren't clearly stating your problem or question. So how can you google how to do what you want to do? You are just saying a bunch of things about your design & calling them problems without saying why exactly. "stick to" is not clear. Use enough words, sentences & references to parts of examples to clearly & fully say what you mean. How to model data about past purchases (or events) (separately from current data) is a faq. You aren't following a design method, or then you would see that you want data about the past and data about the present & put it all in your DB. – philipxy Sep 29 '20 at 23:18
  • [How to Store Historical Data](https://stackoverflow.com/q/3874199/3404097) PS Please before considering posting read your textbook and/or manual & google any error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags; read many answers. If you post a question, use one phrasing as title. Reflect your research. See [ask] & the voting arrow mouseover texts. We cannot reason, communicate or search unless we make the effort to (re-re-re-)write clearly. – philipxy Sep 29 '20 at 23:21
  • ­­ Please [use text, not images/links, for text--including tables & ERDs](https://meta.stackoverflow.com/q/285551/3404097). Paraphrase or quote from other text. Give just what you need & relate it to your problem. Use images only for what cannot be expressed as text or to augment text. Include a legend/key & explanation with an image. – philipxy Sep 29 '20 at 23:24

1 Answers1

0

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.

AntC
  • 1,993
  • 9
  • 15