这一小节我们将分析一些较为常见的业务场景,并给出对于这些场景的表结构设计方法。这些方法可以放入我们自己的数据库设计工具箱,当在面对现实需求时可灵活加以运用。
多值属性
多值属性很常见,如淘宝网中每个用户都可以设置多个送货地址,又如在CRM系统中客户可以有多个电话号,一个号码用于工作时间,另一个用于下班时间等。
以存储客户的联系电话为例。联系电话是客户的属性,所以首先可能想到的一种设计方案如下:
(图1 联系电话作为客户的属性)
图1这一设计满足了当前的需求,但不久我们发现客户的联系电话比我们想象的多,他们还有移动电话,而且有些客户有不止一个办公电话号码,我们需要记录这些电话号码,并标识不同的办公室地点。
对于这种需求,我们可以在Customer表中增加列,但这样做会有两方面的问题:首先,每次增加新列都需要修改数据表结构,需要DBA从后台写脚本完成,且前台显示联系电话的功能模块也需要相应进行修改。其次,每个客户具有的联系电话类型及数量各不相同,大量的联系电话单元格都是空的,浪费了许多存储空间。
我们换一种设计方案。每个客户有多个不同类型的联系电话,可以把联系电话作为弱实体从原先Customer实体中分离出来,如图2所示:
(图2 实体Customer与实体Phones一对多关系)
Customer与Phones之间为“一对多”关系,即一个客户可以有多个不同类型的联系电话。当我们需要给某客户增加联系电话时,我们不再需要修改表结构,只需要在Phones表中增加记录就可以了。这完全可以作为前台的功能让业务操作人员来完成,而且现在的Customer不再会存在大量空单元格了。在关系数据库中增加行比增加列的代价要小很多。
实体Phones的主键是什么?
CustID肯定是主键的一部分。主键包含的其他列根据我们想表达的不同语义,可以有所不同。
语义1:一个客户不能有重复的联系类型。即一个客户的每个PhoneType不能重复,但多个不同的PhoneType可能对应相同的PhoneNum(如:PhoneType为“Office”和“Home”对应同一个号码)。符合该语义的主键为:CustID,PhoneType;
语义2:一个客户不能有重复的联系电话。即一个客户的每个PhoneNum不能重复,但多个不同的PhoneNum可能对应相同的PhoneType(如:PhoneType为“Office”有多个不同号码)。符合该语义的主键为:CustID,PhoneNum;
语义3:一个客户的一个联系类型能有多个不同的联系电话,一个联系电话可能对应不同的联系类型。符合该语义的主键为:CustID,PhoneType,PhoneNum;
举一反三,该多值属性设计方法同样适用于维护客户的多个地址或Email等场景。
历史追溯
说到历史就会涉及时间。例如:当前物价持续上涨,同一产品的售价每个月都有可能调整,若要追溯产品价格变化的情况,仅仅记录该产品当前的一个售价是不足够的。同样对于银行中的利率变化,购入原材料的单价变化等,都需要进行历史追溯。
要跟踪一个实体随时间的变化可以在该实体中增加属性列,指明实体中每个实例的有效日期。图3展示了可追溯产品价格的订单结构(已经过简化)。
(图3 简化的订单表结构)
实体Orders记录订单的公共信息,包括订单号(OrderID),下订单的时间(OrderDate),客户编号(CustomerID)等。其中OrderID提供了到实体OrderItems的联接。实体OrderItems记录客户订购的产品条目,包括所属订单号(OrderID),产品编号(ProductID),订购数量(Quantity)等。其中ProductID能联接到实体Products。实体Products中包含每种产品的描述信息。实体ProductPrices记录了产品的价格,包括产品编号(ProductID)对应到Products实体,产品价格(Price),以及该价格的有效时间段(EffectiveStartDate,EffectiveEndDate)。
对于上述表结构,回溯历史某个订单的信息的步骤如下:
1. 根据订单号(OrderID)在Orders表中找到对应的记录,并记录下OrderDate
2. 在OrderItems表中根据OrderID找到对应的所有订单明细记录。对每一条明细,记录下Quantity和ProductID,之后:
a. 通过ProductID,在Products表中找到对应产品的产品描述(Description)
b. 在ProductPrices表中找到对应ProductID,且EffectiveStartDate <= OrderDate < EffectiveEndDate的记录。该记录中的Price为指定产品在历史下单时的价格。
这样我们就得到了该订单的历史“快照”信息。
需要注意的几点:
1. 如果我们只需要追溯订单中产品的历史价格,可省去上述步骤中的a。
2. 上述订单表结构在每次查看订单时都需要查询ProductPrices表。我们可以通过在OrderItems表中增加ItemPrice列,来避免对ProductPrices表的频繁查询。当创建订单明细记录时,把从ProductPrices中查询到的价格记录到ItemPrice列中,之后每次查看订单时就不需要再查询ProductPrices表了。
3. ProductPrices表的主键为ProductID,EffectiveStartDate。同时该表还隐含着约束:同一种产品的价格有效时间段不能重叠。
4. ProductPrices表结构中EffectiveEndDate列可省去,把该产品的下一个EffectiveStartDate作为上一个有效时间段的自然结束时间点。但这样做会增加查询的复杂度。
在举一个简单的例子,每个客户只有一个地址信息,但希望能跟踪客户地址的变更情况。我们能设计如下(图4)表结构:
(图4 跟踪客户地址的变更)
类似的场景包括:跟踪员工薪资的变化情况,跟踪汇率的变化情况等等。还有一种场景可使用该技术,当我们通过系统前台试图删除某信息时,系统的后台数据库并不真正去做删除操作,而是通过EffectiveEndDate标识记录的无效时间。通过EffectiveStartDate和EffectiveEndDate可回溯任何历史时间点存在的记录“快照”。
树型结构
树型结构最典型的例子是员工组织机构图,如图5所示。
(图5 员工组织结构图)
树型结构中除根节点之外,每一个子节点都有一个父节点。可以把节点建模为一个实体,父子之间的联系建模为“一对多回归关系”。图5中的员工组织结构可建模为图6所示的ER结构。
(图5 员工组织结构ER模型)
实体Employees中的EmpID,FirstName,LastName,HireDate,Salary等属性描述了员工的基本信息,树型层次关系通过ManagerID属性进行描述,该属性存储了该员工的经理ID,即指向其父节点。
在节点实体中存储指向父节点的属性已足够描述树型结构的语义,但为了提高查询的效率,设计中可增加树型结构层次(Lvl)和物化路径(Path)作为辅助信息。图5员工组织结构样例数据如下:
EmpID | FirstName | LastName HireDate … | ManagerID | Lvl | Path |
1 | David | …… | NULL | 0 | .1. |
2 | Eitan | …… | 1 | 1 | .1.2. |
4 | Seraph | …… | 2 | 2 | .1.2.4. |
5 | Jiru | …… | 2 | 2 | .1.2.5. |
10 | Sean | …… | 5 | 3 | .1.2.5.10. |
8 | Lilach | …… | 5 | 3 | .1.2.5.8. |
6 | Steve | …… | 2 | 2 | .1.2.6. |
3 | Ina | …… | 1 | 1 | .1.3. |
7 | Aaron | …… | 3 | 2 | .1.3.7. |
11 | Gabriel | …… | 7 | 3 | .1.3.7.11. |
9 | Rita | …… | 7 | 3 | .1.3.7.9. |
12 | Emilia | …… | 9 | 4 | .1.3.7.9.12. |
13 | Michael | …… | 9 | 4 | .1.3.7.9.13. |
14 | Didi | …… | 9 | 4 | .1.3.7.9.14. |
(表1 员工组织结构数据,其中Lvl列,Path列可选,利用该两列能提升某些查询的性能)
注:如何对树型结构数据表进行查询、遍历在这里不进行展开,可参考《Inside Microsoft SQL Server 2005 T-SQL Querying》一书。本例及以下两小节中的例子,引用自《Inside Microsoft SQL Server 2005 T-SQL Querying》,但同样适用于其他关系数据库。
有向无环图结构
有向无环图(DAG)的典型应用场景是物料清单(BOM)。BOM记录了产品的组装零件或配置方式,下图为某咖啡店的BOM图,描述了配置每种饮料的原料及剂量。
(图6 咖啡店BOM图)
我们如何把这一BOM信息存储到数据库中呢?
BOM场景以有向无环图为模型。有向无环图与树型层次结构的差异之处在于,有向无环图中的一个节点能有多个父节点。故ER模型中,有向无环图需建模成两个实体,一个实体用于描述节点,另一个实体用于描述节点之间的边。咖啡店BOM场景的ER模型如图7所示,实体Parts表示咖啡店的原料及饮品,实体Assemble表示原料配置的方向(即“有向边”),其中还包括边的权值,此例中边的权值为qty,表示配料的剂量,unit为配料的剂量单位(如:g,ml等)。
(图7 咖啡店BOM的ER模型)
把咖啡店BOM的ER图转化为SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
|
create
table
Parts
(
PartID
int
not
null
primary
key
,
PartName
varchar
(25)
not
null
);
create
table
Assemble
(
PartID
int
not
null
references
Parts,
AssemblyID
int
not
null
references
Parts,
Unit
varchar
(3)
not
null
,
Qty
decimal
(8,2)
not
null
,
primary
key
(PartID, AssemblyID),
check
(PartID <> AssemblyID)
);
|
需要注意以下几点:
1. 上述代码在SQL Server 2008下测试通过。对于其他数据库产品,代码细节可能需稍作调整,但主体设计结构不变。
2. Assemble表的主键为:PartID,AssemblyID。
3. Assemble表的PartID列和AssemblyID列外键引用Parts表。
4. Assemble表的check约束保证其中任何记录的PartID与AssemblyID的值不会相同。
无向循环图结构
无向循环图的一个典型例子是城市道路系统。下图展示了美国主要城市之间的道路
(图8 美国道路系统)
图8中每个节点表示一个城市,城市之间的连线代表城市之间的道路,连线上的数值表示距离。道路系统以无向循环图为模型,无向循环图中的节点能与任意数量的其他节点相连,且相连接的节点之间没有父子或先后关系(即“边”没有方向)。对图8中的道路系统进行ER建模得:
(图9 道路系统ER模型)
把图9中的ER模型转化为SQL:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
|
create
table
Cities
(
CityID
char
(3)
not
null
primary
key
,
CityName
varchar
(30)
not
null
,
Region
varchar
(30)
not
null
,
Country
varchar
(30)
not
null
);
create
table
Roads
(
CityID
char
(3)
not
null
references
Cities,
DestID
char
(3)
not
null
references
Cities,
Distance
int
not
null
,
primary
key
(CityID, DestID),
check
(CityID < DestID),
check
(Distance > 0)
);
|
需要注意以下几点:
1. 上述代码在SQL Server 2008下测试通过。对于其他数据库产品,代码细节可能需稍作调整,但主体设计结构不变。
2. 为了更易于理解,图9道路系统ER模型中的关系connect,在转化为SQL表时更名为Roads。Roads表描述了一个无向循环赋权图。表中每一行表示一条边(道路)。Distance属性表示权值(城市间的距离)。
3. Roads表的CityID列和DestID列外键引用CityID表。
4. Roads表的主键为CityID,DestID。
5. Roads表中包含check约束(CityID < DestID),以避免存入两个相同的边(eg:“芝加哥到纽约”和“纽约到芝加哥”)。无向循环图中节点之间是平等的,故该约束很重要,避免冗余数据。
6. 若要扩展到“有向循环图”场景(如:道路系统中的单行道),我们只要去除check约束(CityID < DestID),此时不同方向的数据不再是冗余。
from:http://www.cnblogs.com/DBFocus/archive/2011/09/19/2181781.html