sql - 如何设计数据库模式以支持类别标记？

Question

我正在尝试类似 Database Design for Tagging ，除了我的每个标签都按类别分组。

例如，假设我有一个关于车辆的数据库。假设我们实际上对车辆知之甚少，因此我们无法指定所有车辆将具有的列。因此，我们将用信息“标记”车辆。

1. manufacture: Mercedes
   model: SLK32 AMG
   convertible: hardtop

2. manufacture: Ford
   model: GT90
   production phase: prototype

3. manufacture: Mazda
   model: MX-5
   convertible: softtop

现在，您可以看到所有汽车都标有制造商和型号，但其他类别并不完全匹配。请注意，一辆汽车只能有每个类别中的一个。 IE。一辆汽车只能有一个制造商。

我想设计一个数据库来支持搜索所有梅赛德斯，或者能够列出所有制造商。

我目前的设计是这样的:

vehicles
  int vid
  String vin

vehicleTags
  int vid
  int tid

tags
  int tid
  String tag
  int cid

categories
  int cid
  String category

我有所有正确的主键和外键，但我无法处理每辆车只能有一个制造商的情况。或者我可以吗？

我可以在 VehicleTags 中向复合主键添加外键约束吗？ IE。我可以添加一个约束，使得复合主键 (vid, tid) 只能添加到 VehicleTags 中，前提是 VehicleTags 中还没有一行，这样对于同一个 vid，with 中还没有 tid同一个cid？

我的猜测是否定的。我认为解决这个问题的方法是在 VehicleTags 中添加一个 cid 列，并制作新的复合主键(vid，cid)。它看起来像:

vehicleTags
  int vid
  int cid
  int tid

这将防止汽车有两个制造商，但现在我已经复制了 tid 在 cid 中的信息。

我的架构应该是什么？

Tom 在我之前的问题 How do you do many to many table outer joins? 中注意到了我的数据库模式中的这个问题。

编辑
我知道在示例中，制造实际上应该是车辆表中的一列，但假设您不能这样做。这个例子只是一个例子。

Answer 1

这是 Entity-Attribute-Value 的另一个变体设计。

更容易识别的 EAV 表如下所示:

CREATE TABLE vehicleEAV (
  vid        INTEGER,
  attr_name  VARCHAR(20),
  attr_value VARCHAR(100),
  PRIMARY KEY (vid, attr_name),
  FOREIGN KEY (vid) REFERENCES vehicles (vid)
);

有人逼 attr_name引用预定义属性名称的查找表，以限制困惑。

您所做的只是将 EAV 表分布在三个表上，但没有改进元数据的顺序:

CREATE TABLE vehicleTag (
  vid         INTEGER,
  cid         INTEGER,
  tid         INTEGER,
  PRIMARY KEY (vid, cid),
  FOREIGN KEY (vid) REFERENCES vehicles(vid),
  FOREIGN KEY (cid) REFERENCES categories(cid),
  FOREIGN KEY (tid) REFERENCES tags(tid)
);

CREATE TABLE categories (
  cid        INTEGER PRIMARY KEY,
  category   VARCHAR(20) -- "attr_name"
);

CREATE TABLE tags (
  tid        INTEGER PRIMARY KEY,
  tag        VARCHAR(100) -- "attr_value"
);

如果您打算使用 EAV 设计，您只需要 vehicleTags和 categories表。

CREATE TABLE vehicleTag (
  vid         INTEGER,
  cid         INTEGER,     -- reference to "attr_name" lookup table
  tag         VARCHAR(100, -- "attr_value"
  PRIMARY KEY (vid, cid),
  FOREIGN KEY (vid) REFERENCES vehicles(vid),
  FOREIGN KEY (cid) REFERENCES categories(cid)
);

但请记住，您将数据与元数据混合在一起。您无法将某些约束应用于您的数据模型。

您如何使其中一个类别成为强制性的(常规列使用 NOT NULL 约束)？

如何使用 SQL 数据类型来验证某些标记值？你不能，因为你对每个标签值都使用了一个长字符串。对于您将来需要的每个标签，该字符串是否足够长？你不能说。

如何将某些标签限制为一组允许的值(常规表使用查找表的外键)？这是您的“软顶”与“软顶”示例。但是您不能对 tag 进行限制。列，因为该约束将应用于其他类别的所有其他标签值。您也可以有效地将发动机尺寸和油漆颜色限制为“软顶”。

SQL 数据库不适用于此模型。做对是极其困难的，并且查询它变得非常复杂。如果您继续使用 SQL，您最好采用传统方式对表进行建模，每个属性使用一列。如果您需要“子类型”，则为每个子类型定义一个从属表( Class-Table Inheritance )，否则使用 Single-Table Inheritance .如果每个实体的属性有无限变化，请使用 Serialized LOB .

另一种专为此类流动的非关系数据模型设计的技术是语义数据库，将数据存储在 RDF 中。并用 SPARQL 查询.一种免费解决方案是 RDF4J (原芝麻)。