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

Question

我的数据库结构如下，实现了一个简单的 EAV 模型(见图):

我的产品有一个类型，它通过联结表限制prop_names，可用于该产品。这里一切都很清楚。但:然后我添加了一个 prop_values 表来保存每个产品的属性值。它通过prod_sku 引用products，通过prop_id 引用prop_names。问题来了:可以向任何产品添加任何属性 - 甚至是该产品类型不允许的属性。此外，可能存在重复 - 单个产品的两个或多个相同属性。

有什么方法可以在数据库级别限制它吗？

在@BillKarvin 的回答之后，我尝试了下面的 CREATE 代码，但在创建最后一个表 (property_values) 时失败并出现“外键约束不正确”错误。

我发现了我的错误 - 我忘记将 KEY 添加到 products 表中。以下是我的代码的更正(工作)版本:

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)
);

Answer 1

我会按以下方式设计:

与您的模型有一些重要区别:

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

• prop_values 有一个 prod_type 列，它引用 products，使用两个列 (sku, prod_type).

• 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)
);

这个问题很有趣，因为它是使用第五范式的案例。许多关于数据库设计的文章声称不使用超过第三范式的范式。但您的模型反驳了这一点。