英文:
SQL Table Design for Products with Variations
问题
我正在尝试构建一个库存管理应用程序，允许用户创建产品，上传图片，并在销售渠道之间跟踪订单，如Etsy和Shopify。我目前正在设计用于保存产品的数据库，但在如何处理产品变种方面遇到了困难。产品可以具有一些不同的变种类型（如颜色，尺寸等），在这些类型内部可以有多个变种（如红色尺寸1，红色尺寸2，蓝色尺寸1等）。如果产品具有变种，价格和数量应与变种一起存储。
起初，我只有一个表，但这将非常低效，因为它会重复所有非变种详细信息，如描述和图片。然后，我切换到了2个表，一个用于保存产品，另一个用于保存变种，如下所示。
产品
- ID（主键）
- 名称
- 描述
- 类别
- 等等...
变种
- 变种ID（主键）
- 产品ID（外键 - 产品（ID））
- 变种类型
- 变种名称
- 数量
- 价格
我在处理某些具有变种的产品和其他没有变种的产品时遇到了困难。没有变种的产品将不具有与其关联的数量或价格。
我也不确定如何使用上述的2个表设计来存储不具有变种的产品的订单，因为我需要在订单表中使用变种ID。
订单
- 订单ID（主键）
- 变种ID（外键 - 变种（变种ID））
- 数量
- 订单价值
对于如何最好处理产品变种有任何建议吗？
谢谢
英文:
I'm trying to build a stock management app which allows a user to create products, upload images and track Orders between sales channels such as Etsy and Shopify. I'm currently designing the database to hold the products but struggling on how to handle product variations. Products can have a few different variation types (such as colour, size etc) and within the types can have multiple variation (such as Red Size 1, Red Size 2, blue Size 1 etc) The price and quantity should be stored with the variants if a prodict has them.
I initially had 1 table however this would be very inefficient as it would be duplicating all the non variant details such as the description and images. I then switched to 2 tables, one to hold the product and the other to hold the variations, as per below.
Products
- ID (PK)
- Name
- Description
- Category
- etc...
Variations
- VariantID (PK)
- ProductID (FK - Products (ID))
- Variant Type
- Variant Name
- Quantity
- Price
I'm struggling with how to handle how some products with have variants but others wont. The products that dont have variants wouldn't have a quantity or price associated with them.
I also not sure how I would store orders for products that dont have a variant using the 2 table design above as I would need the variantID in the order table.
Orders
- OrderID (PK)
- VariantID (FK - Variants(VariantID))
- Quantity
- OrderValue
Any suggestions on how best to handle product variants?
Thanks
答案1
得分: 0
最好采用单表设计。如果产品存在不同版本，它们是不同的，因此应该具有不同的ID、不同的价格等。想象一下，你想要将它们存储起来...肯定会将它们按颜色分开存储，而不是混在一起。此外，客户选择特定的颜色，不会接受其他颜色，只因为你发送了它。这些都表明这些产品并不相同，应该被视为各自独立的物品，只是共享部分数据。因此，请尽量不要定义两个表之间的关系。
另外：将价格移动到单独的表中，因为价格随着时间的推移可能会发生变化，肯定希望保留历史记录。
英文:
The one-table-design would be best. If a product exists in different versions, they are different and therefore should have different IDs , different prices etc. Imagine you want to store them ... for sure you store them separated by color instead of mixing them. Also the customer selects one specific color and will not accept another one, just because you sent it. These are signs that the products are not the same and should be seen as individual items which just share some part of data. Therefore try not define a relation with two tables.
Additional: move the price to a separate table because prices can change over time and for sure you want to have a hsitory.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论