腾讯云

开发者社区

文档建议反馈控制台

首页

文章/答案/技术大牛

发布

社区首页 >问答首页 >具有多种产品类别的网站数据库设计

问具有多种产品类别的网站数据库设计
EN

Stack Overflow用户

提问于 2019-02-20 03:19:42

回答 3查看 3.1K关注 0票数 1

我是新加入SQL的。试着尽可能多地学习，所以我把小网店作为我的训练目标。我在数据库结构上苦苦挣扎。我想要实现的是：

有ID和名称的类别(例如电视、洗衣机)
字段(每个类别的字段仅限于该类别(例如电视分辨率、HDR等)、垫圈容量、洗涤周期)
产品(每个产品应该有一般的领域(名称，品牌等)和额外的类别字段，将在每个类别中不同。

因此，系统方面的主要思想是创建一个类别，向其中添加字段，并使用general +类别字段将一些产品插入到该类别中。

我怎样才能做到这一点？我试着把这一切和一对多的关系联系起来，但这似乎不像我所期望的那样有效。

database-design

sql

postgresql

1元体验云点播

覆盖建站/短视频等热门场景，提供媒资管理、短视频 SDK、小程序插件和播放器云+端一体化能力，助您抓住视频风口

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

或者，如果您需要类别特定的动态属性、和产品特定属性，而不考虑类别，甚至两者的组合。

票数 4

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将存储特征，这将取决于分配给产品类别的字段。

票数 0

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