How to design a product table for many kinds of product where each product has many parameters

https://stackoverflow.com/questions/695752/how-to-design-a-product-table-for-many-kinds-of-product-where-each-product-has-m

 

You have at least these five options for modeling the type hierarchy you describe:

• Single Table Inheritance: one table for all Product types, with enough columns to store all attributes of all types. This means a lot of columns, most of which are NULL on any given row.

• Class Table Inheritance: one table for Products, storing attributes common to all product types. Then one table per product type, storing attributes specific to that product type.

• Concrete Table Inheritance: no table for common Products attributes. Instead, one table per product type, storing both common product attributes, and product-specific attributes.

• Serialized LOB: One table for Products, storing attributes common to all product types. One extra column stores a BLOB of semi-structured data, in XML, YAML, JSON, or some other format. This BLOB allows you to store the attributes specific to each product type. You can use fancy Design Patterns to describe this, such as Facade and Memento. But regardless you have a blob of attributes that can't be easily queried within SQL; you have to fetch the whole blob back to the application and sort it out there.

• Entity-Attribute-Value: One table for Products, and one table that pivots attributes to rows, instead of columns. EAV is not a valid design with respect to the relational paradigm, but many people use it anyway. This is the "Properties Pattern" mentioned by another answer. See other questions with the eav tag on StackOverflow for some of the pitfalls.

I have written more about this in a presentation, Extensible Data Modeling.

---

Additional thoughts about EAV: Although many people seem to favor EAV, I don't. It seems like the most flexible solution, and therefore the best. However, keep in mind the adage TANSTAAFL. Here are some of the disadvantages of EAV:

• No way to make a column mandatory (equivalent of NOT NULL).
• No way to use SQL data types to validate entries.
• No way to ensure that attribute names are spelled consistently.
• No way to put a foreign key on the values of any given attribute, e.g. for a lookup table.
• Fetching results in a conventional tabular layout is complex and expensive, because to get attributes from multiple rows you need to do JOIN for each attribute.

The degree of flexibility EAV gives you requires sacrifices in other areas, probably making your code as complex (or worse) than it would have been to solve the original problem in a more conventional way.

And in most cases, it's unnecessary to have that degree of flexibility. In the OP's question about product types, it's much simpler to create a table per product type for product-specific attributes, so you have some consistent structure enforced at least for entries of the same product type.

I'd use EAV only if every row must be permitted to potentially have a distinct set of attributes. When you have a finite set of product types, EAV is overkill. Class Table Inheritance would be my first choice.

 

 

关于EAV的补充想法:虽然很多人似乎都喜欢EAV，但我不喜欢。这似乎是最灵活的解决方案，因此也是最好的。但是，请记住TANSTAAFL这句格言。以下是EAV的一些缺点:

无法强制列(相当于NOT NULL)。

无法使用SQL数据类型验证条目。

无法确保属性名称拼写一致。

无法将外键放在任何给定属性的值上，例如对于查找表。

在传统的表格布局中获取结果是复杂而昂贵的，因为要从多行获取属性，需要对每个属性执行JOIN。

EAV为您提供的灵活性要求您在其他方面做出牺牲，这可能会使您的代码比用更传统的方法解决原始问题更复杂(或者更糟)。

在大多数情况下，没有必要有那么大的灵活性。在OP关于产品类型的问题中，为特定于产品的属性创建每个产品类型的表要简单得多，因此至少对相同产品类型的条目要强制执行一些一致的结构。

只有当每一行都必须被允许具有一组不同的属性时，我才会使用EAV。当你有一组有限的产品类型时，EAV是多余的。类表继承将是我的首选。