Достижение ссылочной целостности в отношениях Сотрудника, Менеджера и Отдела

Question

Я нашел случай в ER, где я не могу понять, как добиться ссылочной целостности. Классические отношения Сотрудник, Менеджер, Департамент могут проиллюстрировать эту проблему.

Со следующими ограничениями:

1. Сотрудник может работать только в одном отделе.
2. Отдел может иметь много сотрудников.
3. Сотрудник может иметь одного менеджера, работающего в том же отделе.
4. Менеджер может иметь много сотрудников, работающих в одном отделе.
5. Сотрудник, у которого нет менеджера, является менеджером.

Эта диаграмма иллюстрирует концепцию.

Перед нормализацией я получаю следующую таблицу.

После нормализации я получаю эти таблицы.

Тем не менее, ничто не мешает мне случайно назначить менеджера, работающего в одном отделе, сотрудником, работающим в другом отделе в EmployeeManager Таблица.

Одним из возможных решений, которое я нашел, было поместить Департамент в EmployeeManager таблица и определить ограничение целостности ссылки, чтобы {Manager, Department} относится {Employee, Department} в EmployeeDepartment Таблица.

Однако для этого не работает {Manager, Department} должен быть ключ кандидата? Есть ли другой дизайн, который может решить эту проблему?

Обновить

Хорошо, чтобы ответить на мой первый вопрос, не так ли {Manager, Department} должен быть ключ кандидата? Оказывается, что {Manager, Department} в EmployeeManager таблица не должна быть ключом-кандидатом или уникальным ключом. Это просто должен быть внешний ключ, ссылающийся на {Employee, Department} в EmployeeDepartment Таблица. Уникальность {Employee, Department} Ключ не очень хорошо определен и может отличаться для разных двигателей. Например, MySQL сообщает, что внешние ключи ссылаются только на уникальные ключи.

Кроме того, MySQL требует, чтобы указанные столбцы были проиндексированы по соображениям производительности. Тем не менее, система не выполняет требование, чтобы указанные столбцы были УНИКАЛЬНЫМИ или были объявлены NOT NULL. Обработка ссылок на внешние ключи на неуникальные ключи или ключи, которые содержат значения NULL, недостаточно четко определена для таких операций, как UPDATE или DELETE CASCADE. Рекомендуется использовать внешние ключи, которые ссылаются только на ключи UNIQUE (включая PRIMARY) и NOT NULL.

В моем случае это будет работать, потому что Сотрудник может работать только в одном Отделе, однако, если ограничение шансов разрешить Сотрудникам работать во многих Отделах, оно не будет работать, потому что {Employee, Department} больше не будет уникальным.

Это должно работать во всех случаях, в том числе, если ограничение шансов разрешить сотрудникам работать во многих департаментах.

Есть ли другой дизайн, который может решить эту проблему? Я тоже думал о замене EmployeeDepartment с ManagerDepartment стол с {Manager} в качестве первичного ключа и возвращаясь к предыдущему EmployeeManager стол с (Employee, Manager) колонны. Теперь, чтобы узнать, в каком отделе работает сотрудник, нужно вступить EmployeeManager с ManagerDepartment Таблица.

Видите ли вы какие-либо плохие практики или аномалии с этим дизайном?

Answer 1

Предполагая, что все эти столбцы объявлены как NOT NULL .,,

Одним из возможных решений, которое я нашел, было поместить Department в таблицу EmployeeManager и определить ограничение ссылочной целостности, чтобы {Manager, Department} ссылался на {Employee, Department} в таблице EmployeeDepartment.

Да, добавить столбец для "отдела" в таблицу "EmployeeManager". Но вам нужны два ограничения внешнего ключа, которые перекрываются. (Но смотри ниже...)

• (руководитель отдела) ссылки EmployeeDepartment (сотрудник отдела)
• (сотрудник, отдел) ссылки EmployeeDepartment (сотрудник, отдел)

Поскольку EmployeeDepartment.Employee является уникальным, пара столбцов EmployeeDepartment.Employee и EmployeeDepartment.Department также является уникальной. Таким образом, вы можете объявить "Сотрудник" в качестве первичного ключа, а также объявить уникальное ограничение для пары столбцов (Сотрудник, Отдел). Если требования изменятся и позволят сотрудникам работать в нескольких отделах, вы можете удалить первичный ключ из одного столбца. Я бы, вероятно, отбросил как первичный ключ, так и ограничения уникальности, и создал бы новое ограничение первичного ключа, включающее оба столбца, но все, что строго необходимо, это снять ограничение первичного ключа.

В таких системах, как ваша, обычно неплохо иметь таблицу менеджеров с очевидными ссылками на внешние ключи. Прямо сейчас, если вы удалите сотрудника Уилла, вы потеряете тот факт, что Стив является менеджером.