如何在MySQL中存储复杂的产品/订单数据？ [英] How to store complex product/order data in MySQL?

问题描述

我正在为我的网上商店订购系统。我有两个表格：

1. 产品存储有关产品的信息


2. ;




现在，我想要在数据库中存储复杂的客户订单。我需要一些东西，让我知道每个产品的每个尺寸（S，M或L）在订单中有多少。





棘手的部分是我想要能够添加/编辑/删除产品（当然不影响过去的订单），所以方法应该是灵活的





我应该怎么办？ / p>





1. 每个订单都有一个单独的表，产品为行？


2. 所有订单都有一个表格，其中产品为列？


3. 其他选项？



感谢！

解决方案

至少需要：

 产品（每个产品一行）
 ProductID 
大小
 
订单（每个订单一行） 
 OrderID 
 
 OrderDetails（每个订单每个产品一行）
 ProductID 
 OrderID 
 Size 
  

请注意，每个'size'是它自己的ProductID。





因此，如果订单＃1有三个产品，那么您可能需要另一个ID对同一基本产品，而订单＃2有四个，则 OrderDetails 将有七行：

  OrderID ProductID数量
 1 234 2 
 1 345 9 
 1 456 30 
 2 432 1 
 2 234 65 
 2 654 8 
 2 987 4 
  




I'm working on an order system for my online shop. I have 2 tables:

1. products, storing info about products
2. orders, storing general id's & infos of customer orders.

Now I want to have a way to store complex customer orders in the database. I need something that will let me know how much of each size (S, M or L) of each product is in an order.

The tricky part is that I want to be able to add/edit/delete products (of course without affecting orders from the past), so the method should be flexible

How should I go about this?

1. a separate table for every order, with products as rows?
2. one table for all orders, with products as columns?
3. some other option?

Thanks!

解决方案

At the very least you need:

Products (one row per product)
    ProductID
    Size

Orders (one row per order)
    OrderID

OrderDetails (one row per product per order)
    ProductID
    OrderID
    Size

Note that each 'size' is its own ProductID. You'll probably want to have yet another ID that groups products that are the same 'base' product, but in different sizes.

So if Order #1 has three products, and Order #2 has four, then OrderDetails will have seven rows:

OrderID ProductID Quantity
1       234       2
1       345       9
1       456       30
2       432       1
2       234       65
2       654       8
2       987       4

这篇关于如何在MySQL中存储复杂的产品/订单数据？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！