数据库字段动态扩展设计

最近讨论数据库有关产品方案的项目自动扩展问题，即每个方案都有多个项目，而每个方案的项目或多或少，也有不一样的，方案以后也可能随之增加新的项目。因此需要数据库设计一套可扩展的方案。

以商品为例，类似淘宝上的设计，要求如下：

1. 字段自动扩展

2. 属性公用

3. 无限扩展字段

现在，看现实世界的产品：

一个产品怎么在数据库存储呢？如：产品表（产品名称，产品类别，品牌，型号，重量……）

而 产品类别 和 品牌 是冗余的，因此独立出两张表来。

再看看产品，电脑 和手机都有相同或者不同的属性，这只是简单列出，实际有几十或上百个字段，虽然操作方便，但这样设计非常不合理。

网上有几种方法：

1. 动态添加属性字段。

2. 先预留字段，到时再用。

3. 使用 XML 字段保存。

4. JSON 格式保存。

5. 属性字段行存储

还有一种方法，把相同属性的字段存储到同一个表，不同的属性，每个产品一张表，这可能会有非常多不同产品的特有属性表！

产品表：

表01：

表02：

所以现在总结考虑的是第五种方法：属性字段行存储 这样就能把所有产品的特有属性都存储到一张表中了！

现在把所有属性都放到同一个表中，产品和属性分开存储：

产品表：

​

属性表：

结果如图：

看看 属性表 ，产品相同属性的就出现冗余了，得把属性和值分库两张表：

属性表：

属性值表：

结构关系如图：

似乎这还不算最终的结果，看 属性值表，对于不同的产品的颜色，也有一样的，属性值表 存储的又会有冗余了，如栗子中的 “白色”。到这里，其实不需要再分表了，有多少属性就插入到该表中。

若是更详细的，继续分表，把值也固定下来。对于颜色，红橙黄绿蓝靛紫等都详细把值都先定义了，在关系表关联对应的颜色就行。当然，属性值表将非常大，因为它包含了所有产品可能的所有参数。所以一般设计到上一步就行了。

属性值：

属性值关系表：

==============================================================

==============================================================

最终设计结果如下：

下面给个例子，只是列出关键字段（可自增列）。

use master
go
create database Demo
go
use Demo
go

--类别（父类别）
create table t2_Category(
Categories_id int not null,
Parent_id int null,
name varchar(50) not null,
constraint pk_Category primary key(Categories_id),
)
go

insert into t2_Category
select 1,null,'电器电子' union all
select 2,null,'服装服饰' union all
select 3,null,'办公用品' union all
select 4,1,'电脑' union all
select 5,1,'手机' union all
select 6,2,'帽子' union all
select 7,2,'衣服' union all
select 8,4,'台式电脑' union all
select 9,4,'笔记本' union all
select 10,4,'Ipad'
go

--品牌
create table t2_Brand(
Brand_id int not null,
name varchar(50) not null,
constraint pk_Brand primary key(Brand_id),
)
go

insert into t2_Brand
select 1,'苹果' union all
select 2,'华为' union all
select 3,'小米' union all
select 4,'诺基亚' union all
select 5,'路易·威登' union all
select 6,'SSPP' union all
select 7,'香奈儿'
go

--产品
create table t2_Procduct(
Procduct_id int not null,
Categories_id int not null,
Brand_id int not null,
name varchar(50) not null,
constraint pk_Procduct primary key(Procduct_id),
constraint fk_Procduct_Category foreign key(Categories_id) references t2_Category(Categories_id),
constraint fk_Procduct_Brand foreign key(Brand_id) references t2_Brand(Brand_id),
)
go

insert into t2_Procduct
select 1,4,1,'iMac aaa' union all
select 2,4,1,'Macbook Air bbb' union all
select 3,4,4,'Nokia ddd' union all
select 4,5,1,'Iphone 6' union all
select 5,5,1,'Iphone 6sp' union all
select 6,5,3,'小米4' union all
select 7,5,3,'红米9' union all
select 8,6,5,'贝雷帽 1999' union all
select 9,7,5,'上衣 COACH 1941' union all
select 10,7,7,'围脖 1945'
go

--属性
create table t2_Property(
Property_id int not null,
name varchar(50) not null,
constraint pk_Property primary key(Property_id)
)
go

insert into t2_Property
select 1,'颜色' union all
select 2,'屏幕尺寸' union all
select 3,'屏幕分辨率' union all
select 4,'CPU型号' union all
select 5,'硬盘容量' union all
select 6,'内存' union all
select 7,'重量' union all
select 8,'手机类型' union all
select 9,'材料' union all
select 10,'衣长'
go


--属性值
create table t2_PropertyValue(
PropertyValue_id int not null,
Property_id int not null,
Value varchar(50) not null,
constraint pk_PropertyValue primary key(PropertyValue_id),
constraint fk_PropertyValue_Property foreign key(Property_id) references t2_Property(Property_id)
)
go

insert into t2_PropertyValue
select 1,1,'白色' union all
select 2,1,'黑色' union all
select 3,1,'红色' union all
select 4,1,'蓝色' union all
select 5,1,'灰色' union all
select 6,2,'360X540' union all
select 7,2,'540X720' union all
select 8,2,'720X960' union all
select 9,2,'720X1280' union all
select 10,6,'1 GB' union all
select 11,6,'2 GB' union all
select 12,6,'4 GB' union all
select 13,6,'8 GB' union all
select 14,7,'100 g' union all
select 15,7,'200 g' union all
select 16,7,'300 g' union all
select 17,7,'400 g' union all
select 18,9,'棉' union all
select 19,9,'亚麻' union all
select 20,9,'人造纤维'
go

--产品属性值表(id,产品,属性,属性值)
create table t2_ProductPropertyValue(
ProductPropertyValue_id int not null,
Procduct_id int not null,
Property_id int not null,
PropertyValue_id int not null,
constraint pk_ProductPropertyValue primary key(ProductPropertyValue_id),
constraint fk_ProductPropertyValue_Procduct foreign key(Procduct_id) references t2_Procduct(Procduct_id),
constraint fk_ProductPropertyValue_Property foreign key(Property_id) references t2_Property(Property_id),
constraint fk_ProductPropertyValue_PropertyValue foreign key(PropertyValue_id) references t2_PropertyValue(PropertyValue_id)
)
go

insert into t2_ProductPropertyValue
select 1,1,1,1 union all
select 2,3,1,2 union all
select 3,5,1,1 union all
select 4,6,1,4 union all
select 5,7,1,2 union all
select 6,8,1,5 union all
select 7,1,3,6 union all
select 8,1,3,7 union all
select 9,1,3,9 union all
select 10,1,6,11 union all
select 11,1,6,12 union all
select 12,1,7,13 union all
select 13,4,7,14 union all
select 14,5,7,16 union all
select 15,7,7,17 union all
select 16,9,9,18 union all
select 17,9,9,19 union all
select 18,9,9,20 union all
select 19,10,9,19 union all
select 20,10,9,20
go


select * from t2_Category   --类别
select * from t2_Brand      --品牌
select * from t2_Procduct   --产品
select * from t2_Property   --属性
select * from t2_PropertyValue  --属性值
select * from t2_ProductPropertyValue --产品属性值表

关系图：

select t3.name as 类别,t4.name as 品牌,t2.name as 产品,t5.name as 属性,t6.Value as 属性值
from t2_ProductPropertyValue t1 
LEFT JOIN t2_Procduct t2 on t1.Procduct_id=t2.Procduct_id
LEFT JOIN t2_Category t3 on t2.Categories_id=t3.Categories_id
LEFT JOIN t2_Brand t4 on t2.Brand_id=t4.Brand_id
LEFT JOIN t2_Property t5 on t1.Property_id=t5.Property_id
LEFT JOIN t2_PropertyValue t6 on t1.PropertyValue_id=t6.PropertyValue_id
order by t3.name,t4.name,t2.name,t5.name,t6.Value
go

这是当前不同的产品，这里是详细的产品参数。无论怎么搜索产品，都能匹配出来。对于大型网站，所有的类型最好预先定义，让客户选择就行，否则商家随便定义各属性的值的话，记录将非常多（如颜色：赭石色，土黄色，深红色……）。

如果新增一款产品，如 “无人机” ，参数如下：

/*
===== 新增产品：无人机 =====

类别：电器电子（已存在）
品牌：大疆
产品：无人机
属性：颜色、重量、轴数  （"颜色"、"重量" 已存在）
属性值：{颜色:白色; 重量:1KG; 轴数:6; }  （"白色" 已存在）
*/

只需要添加没有的记录就行，当然添加前需要判断是否存在。

--新增
insert into t2_Brand(Brand_id,name) values(8,'大疆')
insert into t2_Procduct(Procduct_id,Categories_id,Brand_id,name) values(11,1,8,'无人机')
insert into t2_Property(Property_id,name) values(11,'轴数')
insert into t2_PropertyValue(PropertyValue_id,Property_id,Value) values(21,7,'1 KG'),(22,11,'6')
insert into t2_ProductPropertyValue(ProductPropertyValue_id,Procduct_id,Property_id,PropertyValue_id)values(21,11,1,1),(22,11,7,21),(23,11,11,22)
go


select t3.name as 类别,t4.name as 品牌,t2.name as 产品,t5.name as 属性,t6.Value as 属性值
from t2_ProductPropertyValue t1 
LEFT JOIN t2_Procduct t2 on t1.Procduct_id=t2.Procduct_id
LEFT JOIN t2_Category t3 on t2.Categories_id=t3.Categories_id
LEFT JOIN t2_Brand t4 on t2.Brand_id=t4.Brand_id
LEFT JOIN t2_Property t5 on t1.Property_id=t5.Property_id
LEFT JOIN t2_PropertyValue t6 on t1.PropertyValue_id=t6.PropertyValue_id
where t2.name = '无人机'
order by t3.name,t4.name,t2.name,t5.name,t6.Value
go

基本设计完成。

更多参考：

​​如何设计动态（不定）字段的产品数据库表？--淘宝多产品属性字段设计方法​​

​​中小型商城系统中的分类/产品属性/扩展属性的数据库设计​​

​​两难的境界：不定字段数目的数据库表设计和数据结构​​

​​关于商品属性设计​​