Need some help with database table design with shopping cart

Suppose i have tables

Products  -------product_id , name , price , size

shopping_cart------cart_id,item_id,user_id,quantity

order----order_id , user_id ,totalprice , date

orderHistory---------user_id , item_id,date,order开发者_Python百科_id

I am confused how should i store shopping history because if i store item_id , then there may be the possibility that some product may be deleted , then what should i display in history.

There is possibility that price , size , other dimension of that product changes with time but in history i don't want to change

so how should i design the database

---

For the product deletion issue, try including something like an "Active" (boolean) field in the product table. This way you don't need to physically delete products, just deactivate them. Then you can build your code so that inactive products don't show in the catalogue, but they are still available in your database to show in the order history section.

I'm guessing you're trying to create something like an "OrderLine" table with your OrderHistory table. You should only need to link this to products, and your order header (Order) tables, you don't need to link it to users as the order header table is already linked to a user. If you add some additional fields like "quantity" and "price" to the OrderLine table then you can create a snapshot when the order is placed, and insert the price (at the time it was ordered) and the quantity ordered into you order history table. This way if the product price is changed over time, the information in the OrderLine table remains the same and you still have the original price.

You could still build some entities for save product history (price etc) if you wanted to hold this to show price trends, but in terms of maintaining your actual order information its not necessary.

This approach means your shopping cart table could used as a "work in progress" repository where you are only storing current carts, and once the order is completed the cart is emptied and the data inserted into your order header and order line tables.

This doesn't cover everything, but hopefully gives you some ideas on approaches you could take in regards to your questions.

---

I face the same problem right now, and I am solving it basically by duplicating the relevant data into a secondary table, that the order history models can look at. They will never change, and never be deleted.

This way, if prices change or titles change, you'll have a snapshot in time of the order.

Another way would be to create versioned products, and store the specific version id. When the product changes, the displayed version updates to the newest product ID.

You are duplication history either way.

---

In my opinion you should have a table between order and product in which you can store the information of order and product. you can use order history table for this purpose. Just store the at time of shopping information in that table. It should be a good practice to do in my opinion.

Even if the actual values changes in product table you dont need to change the values in order history table. Just touch that table only at the time when user do some kind of shopping otherwise you dont need to do.

I also suggest you to create a customer table and store customer information in that table and use also customer id in orderhistory table also. It will help you to classify the history even with respect to customer