具有多种产品类别的网站数据库设计
我是新加入SQL的。试着尽可能多地学习，所以我把小网店作为我的训练目标。我在数据库结构上苦苦挣扎。我想要实现的是：
- 有ID和名称的类别(例如电视、洗衣机)
- 字段(每个类别的字段仅限于该类别(例如电视分辨率、HDR等)、垫圈容量、洗涤周期)
- 产品(每个产品应该有一般的领域(名称，品牌等)和额外的类别字段，将在每个类别中不同。
因此，系统方面的主要思想是创建一个类别，向其中添加字段，并使用general +类别字段将一些产品插入到该类别中。
我怎样才能做到这一点？我试着把这一切和一对多的关系联系起来，但这似乎不像我所期望的那样有效。
回答 3
Stack Overflow用户
发布于 2019-02-20 04:00:52
这是一个已知的(反)模式，称为“实体属性值”(如果您想知道更多信息，可以在internet上搜索该名称)。
现在(尤其是Postgres)，我会选择一个JSONB列，该列存储每个产品的类别特定属性，而不是一个额外的fields表。
您甚至可以根据product表中的元信息验证category表中的动态属性。
所以就像这样：
create table category
(
id integer primary key,
name varchar(50) not null,
allowed_attributes jsonb not null
);
create table product
(
id integer primary key,
name varchar(100) not null,
brand varchar(100) not null, -- that should probably be a foreign key
... other common columns ...
);
create table product_category
(
product_id integer not null references product,
category_id integer not null references category,
attributes jsonb not null, -- category specific attributes
primary key (product_id, category_id)
);现在，使用类别表中的“允许属性”列表，我们可以编写一个触发器来验证它们。
首先，我创建了一个小助手函数，它确保来自一个JSON值的所有键都存在于另一个JSON值中：
create function validate_attributes(p_allowed jsonb, p_to_check jsonb)
returns boolean
as
$$
select p_allowed ?& (select array_agg(k) from jsonb_object_keys(p_to_check) as t(k));
$$
language sql;然后，在类别表的触发器中使用此函数：
create function validate_category_trg()
returns trigger
as
$$
declare
l_allowed jsonb;
l_valid boolean;
begin
select allowed_attributes
into l_allowed
from category
where id = new.category_id;
l_valid := validate_attributes(l_allowed, new.attributes);
if l_valid = false then
raise 'some attributes are not allowed for that category';
end if;
return new;
end;
$$
language plpgsql;现在，让我们插入一些示例数据：
insert into category (id, name, allowed_attributes)
values
(1, 'TV Set', '{"display_size": "number", "color": "string"}'::jsonb),
(2, 'Laptop', '{"ram_gb": "number", "display_size": "number"}');
insert into product (id, name)
values
(1, 'Big TV'),
(2, 'Small TV'),
(3, 'High-End Laptop');现在让我们插入类别信息：
insert into product_category (product_id, category_id, attributes)
values
(1, 1, '{"display_size": 60}'), -- Big TV
(2, 1, '{"display_size": 32}'), -- Small TV
(3, 2, '{"ram_gb": 128}'); -- Laptop这是因为所有属性都是在类别中定义的。如果我们试图插入以下内容：
insert into product_category (product_id, category_id, attributes)
values
(3, 2, '{"usb_ports": 5}');然后，触发器将抛出一个异常，防止使用插入行。
这可以扩展到实际使用存储在allowed_attributes中的数据类型信息。
要根据属性查找产品，我们可以使用Postgres提供的JSON函数，例如所有具有display_size的产品：
select p.*
from product p
where exists (select *
from product_category pc
where pc.product_id = p.id
and pc.attributes ? 'display_size');找到包含多个属性的产品同样容易(而且使用“传统”EAV模型要复杂得多)。
下面的查询只查找具有属性display_size 和 ram_gb的产品
select p.*
from product p
where exists (select *
from product_category pc
where pc.product_id = p.id
and pc.attributes ?& '{display_size, ram_gb}');这可以非常有效地进行索引，使搜索速度更快。
我不完全确定您是否想要将属性存储在product_category表中。也许它们应该直接存储在product表中-但这取决于您的需求和您想要如何管理它们。
使用上述方法，您可以拥有一个类别"Computer“来存储诸如CPU数量、RAM和时钟速度等信息。这个类别(及其属性)可以同时使用，例如智能手机和笔记本电脑。
但是，如果要这样做，则需要product_category中的多行来全面描述产品。
最常见的方法可能是直接将属性存储在产品上，并跳过所有的动态JSONB验证。
所以就像这样：
create table category
(
id integer primary key,
name varchar(50) not null
);
create table product
(
id integer primary key,
name varchar(100) not null,
brand varchar(100) not null, -- that should probably be a foreign key
attributes jsonb not null,
... other common columns ...
);
create table product_category
(
product_id integer not null references product,
category_id integer not null references category,
primary key (product_id, category_id)
);或者，如果您需要类别特定的动态属性、和产品特定属性，而不考虑类别，甚至两者的组合。
Stack Overflow用户
发布于 2019-02-20 03:32:57
您可以创建连接表和外键来表示表之间的关系。
类别表
id _名称
字段表
id _名称
类别字段表
id = category_id \ field_id
品牌 id = name
产品表
id \ category_id _ brand_id _
产品特征
id = product_id \ field_id \x=
对于Products表，您可能需要考虑为品牌拥有一个单独的表，并在products表中使用brand_id列而不是名称，以避免重复。
category_fields表将存储类别的id和相关字段的id，表中的每一行代表该类别的不同字段。
然后，表product_features将存储特征，这将取决于分配给产品类别的字段。
Stack Overflow用户
发布于 2019-02-21 06:43:38
使用基于数据的伪码、内联引用(外键)、数据类型和不相关的详细信息省略：
create table Category {
CategoryId,
CategoryName,
key { CategoryId },
key { CategoryName } /* Don't want categories that differ only by surrogate id */
};
/* Allowed fields */
create table CategoryField {
CategoryId,
FieldName,
key { CategoryId, FieldName },
reference CategoryField_Category
{ CategoryId } references Category { CategoryId }
};
create table Product {
ProductId,
ProductName,
ProductBrand,
CategoryId,
key { ProductId }, /* Probably other attributes, keys and references as well */
reference Product_Category
{ CategoryId } references Category { CategoryId }
};
create table ProductFieldValue {
ProductId,
CategoryId, /* Violates BCNF, but is controlled by foreign superkey */
FieldName,
FieldValue,
key { ProductId, FieldName },
reference PFV_Product
{ ProductId, CategoryId } references Product { ProductId, CategoryId },
reference PFV_CategoryField
{ CategoryId, FieldName } references CategoryField { CategoryId, FieldName }
};重叠外键(我更喜欢“引用”一词，特别是因为其中一个实际上是正确的外接超级键)确保每个产品只能根据CategoryField表中的行具有字段值。
在这个模型中有一些冗余--ProductFieldValue违反了Boyce-Codd范式(2NF，但没关系)-so，如果简单的完整性控制的好处超过了这个缺点，您将不得不自己决定。但是请注意，冗余是被控制的；不会有任何不一致。
该模型假设所有字段值都具有相同的数据类型，例如字符串。如果您也想要约束(例如，一些字段只能有数值；有些字段是枚举的等等)，那么事情就会变得更加复杂。
https://stackoverflow.com/questions/54785101
复制相似问题
腾讯云开发者
