产品和变体 - 设计数据库的最佳方式 [英] Products and variants - best way to design database
问题描述
说明
商店可以有产品（鞋，T恤等）。每个产品可以具有许多变体，其中每个产品可以具有不同的价格和库存，例如。 T恤有不同的颜色和尺寸
- 颜色：蓝色，尺码：L，
- 颜色：蓝色，尺寸：XL，价格：$ 10，有库存：10
- 颜色：白色，尺码：
我的问题解决方案如下图所示：
http://dl.dropbox.com/u/43925242/erd_product_variant.png p>
但此解决方案有一些缺点
- 很难控制同一产品的每个变体
- 通过变体功能搜索产品可能需要一些时间，
- 任何其他我不知道...？
您可以建议如何简化这个问题的数据库设计？
===== UPDATE ======
而NoSQL解决方案又如何呢？任何提案？
基本问题是如何存储父类的异构子类型？有很多选择 - 你选择的是一个混合，这不一定是一件坏事。
我看到的这个主题的最好的描述是在Craig Larman的书应用UML和模式 - 虽然他从面向对象写而不是数据库点视图。
首先：你设置variant的方式可能不是你想要的 - 它表明price和stock一起移动，而它们是非常独立的数据位。我会考虑把它们放在自己的表中 - variant_price和variant_stock。
其次，您选择表示功能的选项通常称为实体属性值或EAV。它的主要优点是允许你在设计时不知道它的模式存储数据，但它使任何一种布尔查询都是一个巨大的痛苦 - 想象一下在XL大小的所有红色T恤衫。
在关系世界中有三种选择（这是基于Larman书）：
-
亚型。所以，你创建一个variant_tshirt表与
大小，颜色等，和一个variant_trouser大小，颜色，内部
腿等。这保持表好和自我描述，但使$ -
单个表包含所有可能的列：在这种情况下，您有一个
单个表，所有子类型的所有可能字段。这样，
你的SQL保持更简单 - 但是表变得很麻烦，
你依赖于你的客户端应用程序知道裤子有一个
里面leg属性，和t衬衫不。 -
常用属性表
其子类型在其自己的表中存储其唯一值。在
这个模型，假设你只有裤子和t =衬衫，你有
avariant表的大小和颜色，和一个裤子表与
在腿。 / p>
每个选项都有优点和缺点 - 尤其是在您不知道提前确定哪些子类型需要，第一个选项是最简单的在数据库端，但是对客户端代码创建了一点麻烦。
在SQL之外，您可以使用XML - 使用XPath，您可以轻松执行布尔查询或NoSQL - 但NoSQL不会是我最喜欢的，概念上基于键值关系，这使得布尔查询相当困难。
Description
Shop can have products (Shoes, T-Shirts, etc). Each product can have many variants where each can have different price and stock, e.g. T-Shirt has different colors and sizes
- Color: Blue, Size: L, Price: $10, In stock: 5
- Color: Blue, Size: XL, Price: $10, In stock: 10
- Color: White, Size: L, Price: $10, In stock: 6
My solution for this problem is depicted in the picture
http://dl.dropbox.com/u/43925242/erd_product_variant.png
But this solution has some disadvantages
- it's hard to control each variant of same product should have same number of variant_features,
- searching products through variants features can take some time,
- any other about i have no idea...?
What can you suggest to simplify design of database for this problem?
===== UPDATE ======
And what about NoSQL solution? Any proposals?
The basic question is "how do I store heterogeneous subtypes of a parent class?". There are a number of options - the one you've chosen is a bit of a hybrid, which isn't necessarily a bad thing.
The best description I've seen of this topic is in Craig Larman's book "Applying UML and patterns" - though he writes from an object oriented, rather than database point of view.
First things first: the way you've set up "variants" may not be what you want - it suggests that "price" and "stock" move together, whereas they are very separate bits of data. I'd consider factoring them out into their own tables - "variant_price" and "variant_stock".
Secondly, the option you've chosen to represent features is commonly known as "Entity Attribute Value" or EAV. It's got the major benefit of allowing you to store data without knowing its schema at design time, but it makes any kind of boolean queries into a huge pain - imagine looking for all red tshirts in size XL.
There are 3 alternatives in the relational world (this is based on the Larman book):
subtype per variant. So, you create a "variant_tshirt" table with size, colour etc, and a "variant_trouser" with size, colour, inside leg etc. This keeps the tables nice and self-describing, but makes your SQL into a huge mess - it has to change for every subtype.
single table with all possible columns: in this case, you have a single table with all the possible fields for all subtypes. This way, your SQL stays far simpler - but the table becomes a huge mess, and you depend on your client application to "know" that trousers have an inside leg attribute, and t-shirts don't.
table for common attributes with subtypes storing their unique values in their own tables. In this model, assuming you've only got trousers and t=shirts, you have a "variants" table with size and colour, and a "trousers" table with inside leg.
Each option has benefits and drawbacks - especially in a situation where you don't know in advance which subtypes you're going to need, the first option is the simplest on the database end, but creates a bit of a mess for the client code.
Outside SQL, you can go for XML - using XPath, you can easily execute boolean queries, or NoSQL - but NoSQL would not be my favourite here, most of them are conceptually based on key-value relationships, which make the boolean queries rather hard.
这篇关于产品和变体 - 设计数据库的最佳方式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
