如何在通用方案上对数据变量方差进行建模？ SQL

Question

我最近在思考一些事情，我想知道什么是正确的方法来做类似以下场景的事情（我确信对于数据库人员来说做类似的事情是很常见的事情）。

假设您有一个产品表，如下所示（MySQL）：

CREATE TABLE `products` (
  `id` int(11) NOT NULL auto_increment,
  `product_name` varchar(255) default NULL,
  `product_description` text,
  KEY `id` (`id`),
  KEY `product_name` (`product_name`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8;

这里没有什么异常。 现在假设在不同的表中有一个类别层次结构，并且有一个单独的表将多对多关系与产品表绑定在一起 - 这样每个产品都属于某种类别（我将省略这些，因为这不是这里的问题）。

现在是有趣的部分 - 如果每个类别都要求产品项附加一组变量，会发生什么情况。 例如，计算机显示器类别中的产品必须具有 LCD/CRT 枚举字段、屏幕尺寸枚举等 - 以及其他一些类别，例如冰淇淋具有其他一些变量，如风味 varchar、货架存储时间 int 等。

这里的问题在于，所有产品都有一组公共变量（id、名称、描述等），但还有一些附加变量在不同类别之间不一致 - 但所有产品都应该共享公共变量集，因为最后它们都属于产品组，因此可以查询例如 SELECT * FROM products ORDER BY company_id （简单的例子，也许不具有代表性，但你明白了）。

现在，我看到了几个潜在的解决方案：
- 为每个产品类别生成单独的表，并使用适当的附加变量将产品存储在那里 - 愚蠢且不适合查询

- 产品表与公共变量保持相同，并为每个类别创建一个单独的表，其中包含通过 JOIN 绑定两个表的附加变量 - 标准化，但查询性能和清晰度问题 - 如何从类别中过滤产品（第一个表 - 产品）和额外变量的附加过滤器（例如 17" LCD 显示器） - 这将需要 SQL JOIN 技巧

- 产品表保持不变，并添加另一个变量类型文本，其中包含例如包含其他变量的 JSON 数据 - 紧凑而整洁，但无法使用 SQL 过滤变量

我知道我在这里遗漏了一些非常明显和简单的东西 - 我对标准化技术有点生疏:)

---

edit: I've been searching around stackoverflow before asking this question without success. However, after I've posted the question I have clicked on one of my tags 'normalization' and found several similar questions which resulted in to look up 'generalization specialization relational design'. Point of the story is that this must be the first occurrence in my internet life that tags are actually useful in search. However, I would still like to hear from you guys and your opinions.

---

edit2: The problem with approach no.2 is that I expect somewhere around ~1000 specializations. There is a hierarchy (1-4 level deep) of categories and end nodes add specialized variables - they accumulate in the order of ~1000, so it would be a bit unpractical to add specialized tables to join with.

---

edit3: Due to the vast number of attribute volatility in my case "entity attribute value" that was suggested looks like the way to go. Here comes query nightmares! Thanks guys.

Answer 1

您期望有多少种产品类型？ 它们各自有自己的应用逻辑吗？

您可以创建一个称为“实体属性值”模型的通用模型，但是当您尝试处理产品的特定属性时，它有很多陷阱。 简单的搜索查询有时会变成真正的噩梦。 基本思想是您有一个表来保存产品 ID、属性名称（或属性表中的 ID）和值。 您还可以添加表格来保存每种产品类型的模板。 因此，一组表格会告诉您对于任何给定产品，它可以具有哪些属性（可能以及有效值范围），另一组表格会告诉您对于任何单个产品，其值是什么。

不过，我强烈警告不要使用此模型，因为在您必须实际实现它之前，它似乎是一个非常巧妙的想法。

如果您的产品类型数量相当有限，我会采用您的第二种解决方案 - 一个具有基本属性的主产品表，然后是每种特定类型产品的附加表。

Answer 2

我一直在 Oracle 中这样做。

我有以下表格：

t_class (id RAW(16), parent RAW(16)) -- holds class hierachy.
t_property (class RAW(16), property VARCHAR) -- holds class members.
t_declaration (id RAW(16), class RAW(16)) -- hold GUIDs and types of all class instances
t_instance (id RAW(16), class RAW(16), property VARCHAR2(100), textvalue VARCHAR2(200), intvalue INT, doublevalue DOUBLE, datevalue DATE) -- holds 'common' properties

t_class1 (id RAW(16), amount DOUBLE, source RAW(16), destination RAW(16)) -- holds 'fast' properties for class1.
t_class2 (id RAW(16), comment VARCHAR2(200)) -- holds 'fast' properties for class2
--- etc.

RAW(16) 是 Oracle 保存 GUID 的位置

如果您想选择对象的所有属性，您可以发出：

SELECT  i.*
FROM    (
        SELECT  id 
        FROM    t_class
        START WITH
                id = (SELECT class FROM t_declaration WHERE id = :object_id)
        CONNECT BY
                parent = PRIOR id
        ) c
JOIN    property p
ON      p.class = c.id
LEFT JOIN
        t_instance i
ON      i.id = :object_id
        AND i.class = p.class
        AND i.property = p.property

t_property 保存您通常不搜索的内容（例如文本描述等）。

快速属性实际上是数据库中的普通表，以提高查询效率。 它们仅保存某个类或其后代的实例的值。 这是为了避免额外的连接。

您不必使用快速表并将所有数据限制在这四个表中。

对于您的任务，它将如下所示（为了简洁起见，我将使用方括号中的字符串而不是 GUID）：

t_class

id             parent

[ClassItem]    [ClassUnknown]
[ClassMonitor] [ClassItem]
[ClassLCD]     [ClassMonitor]

t_property

class          property

[ClassItem]    price
[ClassItem]    vendor
[ClassItem]    model
[ClassMonitor] size
[ClassLCD]     matrixType

t_declaration

id             class
[1]            [ClassLCD] -- Iiyama ProLite E1700

t_instance  -- I'll put all values into one column, disregarding type (INT, VARCHAR etc)

id             class           property         value

[1]            [ClassItem]     price            $300
[1]            [ClassItem]     vendor           Iiyama
[1]            [ClassItem]     model            ProLite E1700s
[1]            [ClassMonitor]  size             17
[1]            [ClassLCD]      matrixType       TFT

如果您需要一些复杂的查询来搜索，例如 size AND matrixType，您可以从 property 和 instance 中删除它们并创建另一个表：

t_lcd (id RAW(16), size INT, matrixType VARCHAR2(200))

id             size            matrixType

[1]            17              TFT

并使用它来连接其他属性而不是 t_declaration 在上面的查询中。

但即使没有快速表，这个模型也是可行的。

Answer 3

这种模式有一个名字。 这就是所谓的“泛化专业化”。

如果您搜索“泛化专业化建模”，您将获得一些有关如何执行此操作的文章。 其中一些文章倾向于关系建模和 SQL，而另一些则倾向于对象建模。