Этот вопрос в каком-то смысле очень специфичен, и некоторые люди могут возразить, что он слишком локализован. Однако есть еще одна общеприменимая идея, которая может быть полезна другим людям в будущем, так что не обязательно верно, что вопрос слишком специфичен.
По-настоящему интересная часть этих бизнес-правил заключается в следующем: (выделено мной)
Один отдел может быть назначен более чем одному региону, только если каждый регион относится к разным зонам.
Вот схема, которая декларативно охватывает почти все заявленные бизнес-правила, не прибегая к каким-либо триггерам.
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. Вы можете попытаться втиснуть это в декларативную ссылочную целостность, сделав PK EMPLOYEE составным из ID и DEPT_ID, но это заставит вас решить, хотите ли вы нарушить 3NF или сделать изменения вашего отдела сотрудников процедурно уродливым беспорядком. В конце концов, небольшой триггер, гарантирующий, что EMP_ASGT не расходится с EMPLOYEE.DEPT_ID, доставит гораздо меньше проблем.
person
Joel Brown
schedule
04.03.2012