如何设计数据库架构以支持类别标记？

一尘不染

如何设计数据库架构以支持类别标记？

sql

我正在尝试像“数据库设计标签”之类的东西，除了我的每个标签都分为几类。

例如，假设我有一个有关车辆的数据库。假设我们实际上对车辆不是很了解，因此我们无法指定所有车辆将具有的列。因此，我们将用信息“标记”车辆。

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。我是否可以添加一个约束，使得仅当VehicleTags中没有一行时才可以将复合主键（vid，tid）添加到vehicleTags中，从而对于同一个vid，with中已经没有tid了。相同的CID？

我的猜测不是。我认为解决此问题的方法是将一个cid列添加到vehicleTags，并制作新的复合主键（vid，cid）。它看起来像：

vehicleTags
  int vid
  int cid
  int tid

这样可以防止汽车拥有两个制造商，但现在我重复了cid中tid的信息。

我的架构应该是什么？

Tom在我之前的问题“您如何进行多对多表外部联接”时在数据库模式中注意到了此问题？

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

阅读 129

2021-05-05

共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（以前称为Sesame）。

2021-05-05