如何设计数据库模式以支持使用类别进行标记?

这是另一种变体 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 (原名芝麻街)。

我需要解决这个确切的问题(相同的通用领域和所有汽车零部件)。我发现这个问题的最佳解决方案是使用Lucene/Xapian/Ferret/Sphinx或您喜欢的任何全文索引器。比SQL提供的性能要好得多。

你描述的不是标签,标签只是值,它们没有关联的键。 标签通常以字符串列的形式实现,该值是一系列分隔的值。

例如#1,标签字段将包含以下值:

“制造商_梅赛德斯,车型_SLK32 AMG,敞篷车_硬顶”

然后,用户通常能够通过一个或多个标签的存在来轻松过滤条目。从数据库的角度来看,它本质上是无模式的数据。标签也有缺点,但它们也避免了使用EAV模型带来的极端复杂性。如果你真的需要一个EAV模型,那么考虑一个包含JSON数据的属性字段也可能是值得的。查询起来更痛苦,但仍然没有跨多个表查询EAV那么可怕。

我认为你的解决方案是简单地在你的车辆表中添加一个制造商列。这是一个你知道所有车辆都会有的属性(即汽车不会自发出现),通过将其作为车辆表中的一列,你可以解决每辆车只有一个制造商的问题。这种方法适用于所有车辆共享的任何属性。然后,您可以为其他不通用的属性实现标记系统。

因此,从你的例子来看,车辆表类似于:

vehicle
  vid
  vin
  make
  model

一种方法是稍微重新思考你的模式,将标签键从值中规范化:

vehicles
  int vid
  string vin

tags
  int tid
  int cid
  string key

categories
  int cid
  string category

vehicleTags
  int vid
  int tid
  string value

现在,您只需要对 vehicleTags(vid, tid) .

或者,除了简单的外键之外,还有其他方法可以创建约束:根据您的数据库,您可以编写自定义约束或插入/更新触发器来强制车辆标签的唯一性吗?

我需要解决这个确切的问题(相同的通用领域和所有汽车零部件)。我发现这个问题的最佳解决方案是使用Lucene/Xapian/Ferret/Sphinx或您喜欢的任何全文索引器。比SQL提供的性能要好得多。

如今,我几乎从未构建过一个不涉及全文索引器的数据库支持的web应用程序。这个问题和搜索的一般问题经常出现,无法从工具箱中省略索引器。