Shopping Cart Database Structure

Question

I study the database structure for shopping carts and notice that when I save order information, the product information is repeated and stored in the table again. I was wondering what could be causing this? Here is a small example of what I mean:

Product table

product_id name desc price 1 product 1 This is product 1 27.00 

Order table

 order_id customer id order_total 1 3 34.99 

Order Details Table

 order_details_id product_id product name price qty 1 1 product 1 27.00 1 

So, as you can see, the product name and price are again stored in the order details table. Why is this? The only reason I can think of is because the details of the product may change after the order has been placed, which can cause confusion. Is it correct?

thanks

Floor

Answer 1

yes that's the only reason

the price of your product often change

so that you can create another table and save the product detail as shown below.

Product Update Table

 id product_id name desc price 1 1 product 1 This is product 1 27.00 

and the order table will be

 order_details_id product_Update_id qty 1 1 1 

Answer 2

This is probably done for performance reasons, instead of making a JOIN, you can only get a direct SELECT to get order details.

Answer 3

You can change your structure according to

 product_id name desc price 1 product 1 This is product 1 27.00 order_id product_id customer id order_total 1 1 3 34.99 order_details_id order_id qty 1 1 1 

there is no need to take productname and its price in orderdetail just take the order identifier and in the order table add one field product_id