本文介绍了如何为多种产品设计产品表,其中每种产品都有很多参数的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我在桌子设计方面没有太多经验.我的目标是创建一个或多个满足以下要求的产品表:

I do not have much experience in table design. My goal is to create one or more product tables that meet the requirements below:

  • 支持多种产品(电视、手机、PC 等).每种产品都有一组不同的参数,例如:

  • Support many kinds of products (TV, Phone, PC, ...). Each kind of product has a different set of parameters, like:

  • 手机将具有颜色、尺寸、重量、操作系统...

  • Phone will have Color, Size, Weight, OS...

PC 将有 CPU、HDD、RAM...

PC will have CPU, HDD, RAM...

参数集必须是动态的.您可以添加或编辑您喜欢的任何参数.

The set of parameters must be dynamic. You can add or edit any parameter you like.

如果没有针对每种产品的单独表格,我如何满足这些要求?

How can I meet these requirements without a separate table for each kind of product?

推荐答案

您至少有以下五个选项可以为您描述的类型层次结构建模:

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

  • 单表继承:所有产品类型的一个表,有足够的列存储所有类型的所有属性.这意味着很多列,其中大多数列在任何给定行上都是 NULL.

  • 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.

序列化 LOB:产品的一张表,存储所有产品类型通用的属性.一个额外的列以 XML、YAML、JSON 或某种其他格式存储半结构化数据的 BLOB.此 BLOB 允许您存储特定于每种产品类型的属性.您可以使用花哨的设计模式来描述这一点,例如 Facade 和 Memento.但是不管你有一堆不能在 SQL 中轻松查询的属性;您必须将整个 blob 提取回应用程序并在那里进行分类.

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:一张产品表,以及一个将属性转为行而不是列的表.EAV 就关系范式而言不是有效的设计,但许多人仍然使用它.这是另一个答案提到的属性模式".请参阅 StackOverflow 上带有 eav 标签的其他问题,了解一些陷阱.

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.

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

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:

  • 无法使列成为必需的(相当于 NOT NULL).
  • 无法使用 SQL 数据类型来验证条目.
  • 无法确保属性名称拼写一致.
  • 无法在任何给定属性的值上放置外键,例如用于查找表.
  • 在传统的表格布局中获取结果既复杂又昂贵,因为要从多行获取属性,您需要为每个属性执行 JOIN.

EAV 为您提供的灵活性程度需要在其他方面做出牺牲,这可能会使您的代码变得比以更传统的方式解决原始问题更复杂(或更糟).

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.

而且在大多数情况下,没有必要具有这种程度的灵活性.在 OP 关于产品类型的问题中,为特定于产品的属性创建每个产品类型的表格要简单得多,因此您至少对相同产品类型的条目强制执行了一些一致的结构.

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.

仅当必须允许每一行潜在地具有一组不同的属性时,我才会使用 EAV.当您拥有一组有限的产品类型时,EAV 是多余的.类表继承将是我的首选.

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.

2019 年更新:我看到人们使用 JSON 作为许多自定义属性"问题的解决方案的次数越多,我就越不喜欢该解决方案.它使查询过于复杂,即使使用特殊的 JSON 函数 支持他们.与存储在普通的行和列中相比,存储 JSON 文档需要更多的存储空间.

Update 2019: The more I see people using JSON as a solution for the "many custom attributes" problem, the less I like that solution. It makes queries too complex, even when using special JSON functions to support them. It takes a lot more storage space to store JSON documents, versus storing in normal rows and columns.

基本上,这些解决方案在关系数据库中都不是简单或有效的.拥有可变属性"的整个想法从根本上与关系理论不一致.

Basically, none of these solutions are easy or efficient in a relational database. The whole idea of having "variable attributes" is fundamentally at odds with relational theory.

归根结底,您必须根据对您的应用程序的不利影响来选择一种解决方案.因此,在选择数据库设计之前,您需要知道将如何查询数据.无法选择最佳"解决方案,因为任何解决方案都可能最适合给定的应用程序.

What it comes down to is that you have to choose one of the solutions based on which is the least bad for your app. Therefore you need to know how you're going to query the data before you choose a database design. There's no way to choose one solution that is "best" because any of the solutions might be best for a given application.

这篇关于如何为多种产品设计产品表,其中每种产品都有很多参数的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

08-03 22:27
查看更多