对分层数据结构进行建模

Question

20120304-简化问题

假设我们有实体R、D和E，这个关系基数

• R<n： m>D
• D<1:n>E

这个规范的映射是直接的，但我们有另一个要求：

• R<n： m>E

边条件：如果E1与某个D1相关，并且该D1与R1相关，则E1可能只被"分配"给R1。

不幸的是，即使E2与D2相关，也可能与R2无关。

• 我正在寻找一个关系数据库模型
• 一个模型，如果D从Ra分离并重新连接到另一个Rb，则不需要多次更新。在这种情况下，D的所有Es都需要与Ra分离，并连接到Rb上

20120305-变通办法

一位朋友建议创建一个实体DxR，该实体通过元组（D，R）将其D和R链接起来。然后创建一个关系

• DxR<n： m>E

嗯。。。

20120302-原始问题

系统由顶级区域（Z）组成。一个区域可能有几个区域（R）。

所谓的部门（D）可以分配到区域。一个部门可以被分配到多个区域，前提是每个区域属于不同的区域。

最后，员工（E）只属于一个部门。

只有当员工所在的部门属于某个地区时，员工才能被分配到该地区。

重要提示：员工不必属于其部门所属的所有地区。

假设在下面的图形中E1属于D1。E1也应该属于R1，但不属于R2——尽管D1属于R1和R2:

-     Z            Z
-   __|___      ___|___
-   R1   R      R2    R
-    _________/
-     D1         

问：请提出为上述规范建模的关系数据库的表结构

Answer 1

这个问题在某种意义上非常具体，有些人可能会认为它过于本地化。然而，有一个更普遍适用的想法可能在未来对其他人有用，所以这个问题不一定太具体。

这些业务规则中真正有趣的部分是：（我强调了这一点）

一个部门可以被分配到多个地区，仅当每个地区区域属于不同的区域。

这里有一个模式，它以声明的方式捕获几乎所有声明的业务规则，而不必求助于任何触发器。

create table ZONE
( ID int not null
, NAME varchar(50) not null
, constraint PK_ZONE primary key clustered (ID)
)
create table REGION
( ZONE_ID int not null
, REGION_ID int not null
, NAME varchar(50) not null
, constraint PK_REGION primary key clustered (ZONE_ID, REGION_ID)
, conttraint FK_REGION__ZONE foreign key (ZONE_ID) 
    references ZONE (ID)
)
create table DEPARTMENT
( ID int not null
, NAME varchar(50) not null
, constraint PK_DEPARTMENT primary key clustered (ID)
)
create table EMPLOYEE
( ID int not null
, NAME varchar(50) not null
, DEPT_ID int not null
, constraint PK_EMPLOYEE primary key clustered (ID)
, constraint FK_EMPLOYEE__DEPARTMENT foreign key (DEPT_ID) 
    references DEPARTMENT (ID)
)

上面的表格很明显。然而，有一个特殊的怪癖：REGION表有一个复合主键，其中包括FK到ZONE。这对于传播关于部门必须在区域内不同的约束非常有用。

将部门分配给地区需要一个交叉表：

create table DEPT_ASGT -- Department Assignment
( REGION_ID int not null
, DEPT_ID int not null
, ZONE_ID int not null
, constraint PK_DEPT_ASGT (REGION_ID, DEPT_ID)
, constraint FK_DEPT_ASGT__REGION foreign key (ZONE_ID, REGION_ID) 
    references REGION (ZONE_ID, ID)
, constraint FK_DEPT_ASGT__DEPARTMENT foreign key (DEPT_ID) 
    references DEPARTMENT (ID)
, constraint UN_DEPT_ASGT__ZONES unique nonclustered (ZONE_ID, DEPT_ID)
)

这个交集表是非常正常的，因为它有一个外键指向它链接的每个表。这个交集表的特殊之处在于它的唯一约束。这就是为什么一个部门不能在同一区域内的两个不同地区的规则。

最后，我们需要将员工划分为部门和地区。这需要另一个交集表：

create table EMP_ASGT -- Employee Assignment
( REGION_ID int not null
, DEPT_ID int not null
, EMPLOYEE_ID int not null
, constraint PK_EMP_ASGT (REGION_ID, DEPT_ID, EMPLOYEE_ID)
, constraint FK_EMP_ASGT__DEPT_ASGT (REGION_ID, DEPT_ID) 
    references DEPT_ASGT (REGION_ID, DEPT_ID)
, constraint FK_EMP_ASGT__EMPLOYEE (EMPLOYEE_ID) refernces EMPLOYEE (ID)
)

您会注意到，EMPLOYEE表有一个DEPARTMENT的外键，它强制执行每个员工只能属于一个部门的规则。EMP_ASGT表添加了员工参与哪些区域的详细信息。由于员工可能不参与他或她的部门分配到的每个区域，EMP_ASGT表仅将员工连接到他们参与的区域。

这是一个需要触发器或其他程序逻辑的地方。您需要确保EMPLOYEE.department_id与EMP_ASGT中的记录保持一致。您可以尝试通过将EMPLOYEE的PK设置为ID和DEPT_ID的组合来将其推进到声明性引用完整性中，但这将迫使您决定是要违反3NF，还是让您的员工部门更改在程序上一团糟。在一天结束时，一个小的触发器来确保EMP_ASGT不会与EMPLOYEE.DEPT_ID意见相左，麻烦就会小得多。