Shopping Cart Database Structure

Question

I have been studying the database structure for shopping carts and notice that when storing order details the product information is repeated and stored again in the table. I was wondering what the reasoning behind this would be? 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 stored again in the order details table. Why is this? The only reason i can think of is because the product details may change after the order has been placed which may cause confusion. Is this correct?

Thanks

Paul

Answer 1

yes this is the only reason

your product price get change frequently

so you can create one more table and store detail of product as shown below

Product update table

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

and order table will be

order_details_id    product_Update_id  qty
       1                1               1