需要一些有关购物车的数据库表设计的帮助
假设我有表，
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_id
我很困惑应该如何存储购物历史记录，因为如果我存储 item_id ，那么可能有可能删除某些产品，那么我应该在历史记录中显示什么。
该产品的价格、尺寸、其他尺寸可能会随着时间而变化，但在历史上我不想改变
，所以我应该如何设计数据库
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_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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
对于产品删除问题，请尝试在产品表中包含类似“Active”（布尔值）字段的内容。这样您就不需要物理删除产品，只需停用它们即可。然后，您可以构建代码，以便非活动产品不会显示在目录中，但它们仍然可以在数据库中显示在订单历史记录部分中。
我猜您正在尝试使用 OrderHistory 表创建类似“OrderLine”表的内容。您只需要将其链接到产品和订单表头 (Order) 表，无需将其链接到用户，因为订单表头表已经链接到用户。如果您向 OrderLine 表添加一些其他字段，例如“数量”和“价格”，那么您可以在下订单时创建快照，并将价格（订购时）和订购数量插入订单历史记录中桌子。这样，如果产品价格随时间发生变化，OrderLine 表中的信息将保持不变，并且您仍然拥有原始价格。
如果您想保存产品历史记录（价格等），您仍然可以构建一些实体来显示价格趋势，但就维护实际订单信息而言，这是没有必要的。
这种方法意味着您的购物车表可以用作“正在进行的工作”存储库，您仅存储当前的购物车，订单完成后，购物车将被清空，并将数据插入到订单标题和订单行表中。
这并没有涵盖所有内容，但希望能为您提供一些关于解决问题的方法的想法。
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.
我现在面临同样的问题，我基本上通过将相关数据复制到订单历史模型可以查看的辅助表中来解决它。它们永远不会改变，也永远不会被删除。
这样，如果价格发生变化或标题发生变化，您将及时获得订单快照。
另一种方法是创建版本化产品，并存储特定的版本 ID。当产品发生变化时，显示的版本会更新为最新的产品 ID。
不管怎样，你都是重复的历史。
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.
在我看来，你应该在订单和产品之间有一个表，可以在其中存储订单和产品的信息。您可以使用订单历史记录表来实现此目的。只需将购物时的信息存储在该表中即可。在我看来，这应该是一个很好的做法。
即使产品表中的实际值发生变化，您也不需要更改订单历史表中的值。仅在用户进行某种购物时才触摸该桌子，否则您不需要这样做。
我还建议您创建一个客户表并将客户信息存储在该表中，并在 orderhistory 表中也使用客户 id。它将帮助您对历史记录进行分类，甚至针对客户
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