EAV模型 - 如何限制产品属性？

Question

我有一个数据库结构，实现了一个简单的EAV模型（见图片）。我的产品有一个类型，通过连接表限制可用于此产品的属性名称，这很清楚。但是，当我添加prop_values表来存储每个产品的属性值时，问题就来了。它通过prod_sku引用到products，并通过prop_id引用到prop_names。在此情况下，用户可以将任何属性添加到任何产品中，即使不允许该产品类型的属性也可以添加。此外，还可能存在重复，即单个产品有两个或多个相同的属性。有没有办法在数据库级别上限制这些？在@BillKarvin的回答之后，我尝试了下面的CREATE代码，但在创建最后一个表（property_values）时出现“外键约束格式不正确”的错误。我发现了我的错误-忘记向产品表添加关键字。以下是我的代码经过更正后的版本（可运行）。

CREATE TABLE product_types (
  id INT PRIMARY KEY,
  product_type varchar(50) NOT NULL,
  block_css_id varchar(50) NOT NULL,
  block_description varchar(50) NOT NULL
);

CREATE TABLE products (
  sku varchar(50) PRIMARY KEY,
  name varchar(50) NOT NULL,
  price decimal(20,2) unsigned NOT NULL,
  id_product_type INT NOT NULL,
  FOREIGN KEY (id_product_type) REFERENCES product_types (id),
  KEY (sku, id_product_type)
);

CREATE TABLE property_names (
  id INT PRIMARY KEY,
  property_name varchar(50) NOT NULL,
  property_css_id varchar(50) NOT NULL,
  property_input_name varchar(50) NOT NULL
);

CREATE TABLE junction_ptype_propname (
  id_productt_type INT NOT NULL,
  id_property_name INT NOT NULL,
  PRIMARY KEY (id_productt_type, id_property_name),
  FOREIGN KEY (id_productt_type) REFERENCES product_types (id),
  FOREIGN KEY (id_property_name) REFERENCES property_names (id)
);

CREATE TABLE property_values (
  id INT NOT NULL PRIMARY KEY,
  product_sku varchar(50) NOT NULL,
  property_id INT NOT NULL,
  property_value decimal(20,2) NOT NULL DEFAULT 0.00,
  id_prod_type INT NOT NULL,
  UNIQUE KEY (product_sku, property_id),
  FOREIGN KEY (product_sku, id_prod_type) REFERENCES products (sku, id_product_type),
  FOREIGN KEY (property_id, id_prod_type) REFERENCES junction_ptype_propname (id_property_name, id_productt_type)
);

- Denys Galanenko

---

你的数据库设计稍微有些不一致。基本上你有“产品”、“属性”和“类型”，但这并不是我在你的数据库中看到的。你确实有一个名为“products”的表，其中有一个字段叫做“name”，但是你还有一个名为“prop_names”的表来保存属性，其中有一个名为“prop_name”的字段。这很奇怪。我会把后者的表名改为“properties”。我喜欢唯一的字段名称，所以我会使用“product_name”和“property_name”。不要不必要地缩写。你的“prop_values”表可以被称为“products_properties”，因为它结合了这两个表。 - KIKO Software

由连接表设置的关系是一种模式，而产品和其属性值之间的关系是基于此模式构建的实体。也就是说，用于创建该模式的实体与该模式之间没有关系。 - Akina

@KIKOSoftware - 我同意，也许命名可以更好，但这并不改变主题问题。 - Denys Galanenko

@Akina - 是的 - 我意识到没有这样的关系，实际上这就是我的问题 - 如何创建它... - Denys Galanenko

Answer 1

我会这样设计：

你的模型与之有几个重要的不同之处：

• prop_values 在 (prod_sku, prop_id) 上拥有唯一键，因此每个产品 sku 只能有一个给定属性的实例。

• prop_values 有一个 prod_type 列，并且使用两列 (sku, prod_type) 引用了 products。

• prop_values 具有到 junction_ptype_propname 的复合外键，而不是到 prop_name 的外键。

现在，在 prop_values 中的 prod_type 可以在每行中只有一个值，并且它必须引用 products 表和 junction_ptype_propname 表中的正确产品类型的两个列。 因此，它被限制为给定产品的有效属性，并且是产品类型的有效属性。 因此，您无法为不适用于该产品类型的属性添加属性到产品中。

以下是 DDL：

create table prod_types (
  id int primary key,
  type_name varchar(30) not null
);

create table products (
  sku varchar(30) primary key,
  name varchar(30) not null,
  type int not null,
  foreign key (type) references prod_types(id),
  key(sku, type)
);

create table prop_names (
  id int primary key,
  prop_name varchar(30) not null
);

create table junction_ptype_propname (
  id_prop_name int not null,
  id_prod_type int not null,
  primary key (id_prop_name, id_prod_type),
  foreign key (id_prod_type) references prod_types(id),
  foreign key (id_prop_name) references prop_names(id)
);

create table prop_values (
  id int primary key,
  prod_sku varchar(30) not null,
  prod_type int not null,
  prop_id int not null,
  prop_value decimal not null,
  unique key (prod_sku, prop_id),
  foreign key (prod_sku, prod_type) references products(sku, type),
  foreign key (prop_id, prod_type) references junction_ptype_propname(id_prop_name, id_prod_type)
);

这个问题很有趣，因为它涉及到使用第五范式。许多关于数据库设计的文章声称超过第三范式的范式不常用。但是你的模型证明了这一点。

- Bill Karwin

---

非常感谢您的回答。我已经尝试实现它几天了（自从我提出初始问题以来，我的数据库已经发生了变化）。但是我失败了。请查看我的CREATE代码-我已将其暂时添加到问题正文中，因为它太长了，无法在评论中发布。它会产生一个错误：“外键约束格式不正确”。 - Denys Galanenko

抱歉 - 我已经找到了我的错误 - 我忘记在“产品”表中创建一个KEY。 - Denys Galanenko

将来，您可能会喜欢我为之做出贡献的外键错误清单：https://dev59.com/PHM_5IYBdhLWcg3wQQtd#4673775 - Bill Karwin

Answer 2

此外，可能存在重复 - 单个产品的两个或多个相同属性。

使用UNIQUE来防止重复。 w3schools.com - UNIQUE

- Quiet Molly

---

这样做是行不通的 - 在 prop_values 表中可能会有许多相同类型的产品，它们当然会具有相同的 prop_id 值，因此 UNIQUE 不适用。应该有一些类似查询的限制 - 具有相同 prod_sku 的项目不能具有相同的 prop_id... - Denys Galanenko

@DenysGalanenko：如果您将唯一性限制在prod_sku和prop_id列上，我认为这可能有效。如果问题解释得更好，我会点赞此问题。 - KIKO Software

@KIKOSoftware - 我感觉我在这里完全误解了什么： UNIQUE 的定义是：“UNIQUE 约束确保列中的所有值都不同。” 那么如果我要存储以下内容，如何将 UNIQUE 应用于 prod_sku 和 prop_id： 1）具有多个属性的产品 - 这需要重复使用 prod_sku 2）具有相同属性的不同产品 - 这需要重复使用 prop_id我需要确保一对 prod_sku 和 prop_id 是唯一的。 - Denys Galanenko

@DenysGalanenko：请点击链接并查看第二个示例。您可以在多个列上放置唯一约束。在您的情况下，应该是：CONSTRAINT unique_property_value UNIQUE (product_sku, property_id)。这意味着product_sku和property_id的组合必须是唯一的。 - KIKO Software

@KIKOSoftware - 非常感谢您，这真的很有效，而且您的描述比W3Schools的示例更好！那么我的主要问题呢 - 关于限制添加错误属性的问题？ - Denys Galanenko