具有可变属性的多个产品类型的数据库设计

我有一个包含不同产品类型的数据库.每种类型都包含彼此差异很大的字段.第一类产品分为三类.第二类产品分为三类.但第三个和第四个,没有任何分类.

每种产品可以具有任意数量的不同属性.

我使用的数据库模型基本上如下：
(见链接)
http://www.damirsystems.com/dp_images/product_model_01.png

我有一个庞大的数据库,在产品表中包含大约500000个产品.

因此,当我要从数据库中获取具有所有属性的产品,或者按属性搜索产品过滤时,它会严重影响性能.

任何人都可以帮助我在sql中的表结构,或者为这个问题做一些索引或任何可行的解决方案.因为不同的电子商务网站正在使用这种数据库并且可以使用大量不同类型的产品.

解决方法

如果您没有大量类型的产品(最多数十个,但不是数百个),那么您可以使用两种常用方法中的一种来处理此问题.

第一种方法是简单地为产品设置一个表,其中包含每种不同类型产品可能需要的所有属性的列.您可以使用适合每种产品的列,并将其余列留空.假设您销售书籍,音乐和视频：

create table Product (
    id integer primary key,name varchar(255) not null,type char(1) not null check (type in ('B','M','V')),number_of_pages integer,-- book only
    duration_in_seconds integer,-- music and video only
    classification varchar(2) check (classification in ('U','PG','12','15','18')) -- video only
);

这具有简单且不需要连接的优点.但是,它并没有很好地强化数据的完整性(例如,你可能有一本没有多个页面的书),如果你有多种类型的产品,那么这个表将变得非常笨重.

您可以使用表级检查约束来解决完整性问题,这些约束要求每种类型的产品都具有某些列的值,如下所示：

check ((case when type = 'B' then (number_of_pages is not null) else true end)))

(给Joe Celko提示 – 我查找了如何在SQL中进行逻辑蕴涵,并找到了一个例子,他用这种结构来构建一个非常相似的检查约束！)

你甚至可以说：

check ((case when type = 'B' then (number_of_pages is not null) else (number_of_pages is null) end)))

确保没有行在列中的值不适合其类型.

第二种方法是使用多个表：一个基表包含所有产品通用的列,一个辅助表用于每种类型的产品,其中包含特定于该类型产品的列.所以：

create table Product (
    id integer primary key,name varchar(255) not null
);

create table Book (
    id integer primary key references Product,number_of_pages integer not null
);

create table Music (
    id integer primary key references Product,duration_in_seconds integer not null
);

create table Video (
    id integer primary key references Product,duration_in_seconds integer not null,classification varchar(2) not null check (classification in ('U','18'))
);

请注意,辅助表与主表具有相同的主键;它们的主键列也是主表的外键.

这种方法仍然相当简单,并且可以更好地执行完整性.但查询通常涉及连接：

select
  p.id,p.name
from
  Product p
  join Book b on p.id = b.id
where
  b.number_of_pages > 300;

完整性仍然不完美,因为可以在与主表中的错误类型的行对应的辅助表中创建行,或者在与主表中的单个行对应的多个辅助表中创建行.您可以通过进一步细化模型来解决这个问题.如果您将主键设置为包含类型列的复合键,则产品的类型将嵌入其主键中(书籍将具有主键,如(‘B’,1001)).您需要将类型列引入辅助表,以便它们可以具有指向主表的外键,并且您可以在每个辅助表中添加一个检查约束,该约束要求类型正确.喜欢这个：

create table Product (
    type char(1) not null check (type in ('B',id integer not null,primary key (type,id)
);

create table Book (
    type char(1) not null check (type = 'B'),number_of_pages integer not null,id),foreign key (type,id) references Product
);

这也使得在仅给定主键的情况下查询正确的表更容易 – 您可以立即告诉它所引用的产品类型,而无需首先查询主表.

但是,仍然可以在主表中创建行,而在任何辅助表中都没有对应的行.我不知道如何解决这个问题.

列存在重复的潜在问题 – 如上图所示,其中duration列在两个表中重复.您可以通过为共享列引入中间辅助表来解决此问题：

create table Media (
    type char(1) not null check (type in ('M',id) references Product
);

create table Music (
    type char(1) not null check (type = 'M'),id) references Product
);

create table Video (
    type char(1) not null check (type = 'V'),'18')),id) references Product
);

你可能认为不值得付出额外的努力.但是,您可能会考虑做的是混合使用两种方法(单表和辅助表)来处理这种情况,并为某些类似的产品提供共享表：

create table Media (
    type char(1) not null check (type in ('M',classification varchar(2) check (classification in ('U',id) references Product,check ((case when type = 'V' then (classification is not null) else (classification is null) end)))
);

如果在应用程序中将类似的产品集中在一起,那将特别有用.在这个例子中,如果您的店面一起呈现音频和视频,但是与书籍分开,那么这种结构可以支持比为每种媒体分别使用单独的辅助表更有效的检索.

（编辑：PHP编程网 - 黄冈站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!