Дизайн базы данных для рекурсивных отношений

Рассмотрим этот случай, когда я пытаюсь смоделировать базу данных для компании:

Объекты: Employees, Managers, Departments.
Employee работает только в 1 Department, тогда как Department может иметь много Employees, работающих в нем.
A Manager может управлять только 1 Department, и аналогично a Department может иметь только 1 Manager.
A Manager контролирует многие Employees, но Employee контролируется только одним Manager.

Теперь у меня есть два способа моделирования этого:

Первое решение:

Я буду считать, что объект Manager наследуется от объекта Employee, считая, что я сохраню данные, которые уникальны для Менеджеров (например, Бонус и Статус).

Поскольку отношение между Department и Employee равно 1:N, тогда я поставлю Department Id в качестве внешнего ключа в таблице Employee для Works отношение.
Так как отношение между Department и Manager равно 1:1, тогда я поставлю Department Id в качестве внешнего ключа в таблице Manager для Manages отношение.

Проблема: как я могу представить рекурсивное отношение между Manager и Employee?

Второе решение:

Я буду считать, что объект Manager не нужен, поскольку другой Employees может также иметь Bonus и Status. (На самом деле я добавил эти 2 атрибута, чтобы посмотреть, как их моделировать в обоих случаях)

Так как отношение между Department и Employee равно 1:N, тогда я поставлю Department Id в качестве внешнего ключа в таблице Employee для Works отношение.
Так как отношение между Employee и Manager равно 1:N, тогда я поставлю Employee Id в качестве внешнего ключа в таблице Employee для Supervises и назовите его Manager Id.

Проблема: как я могу представить связь между Manager и Department?

Вопросы:

Есть ли очевидные ошибки в обоих конструкциях?
Как решить каждую проблему в обоих случаях?
Есть ли лучшее решение, чем эти два?

Ответ 1

Вероятно, я бы сказал что-то вроде:

Эта модель имеет следующие характеристики:

Менеджер "наследует" сотрудника.
- Чтобы представить сотрудника, вставьте одну строку в EMPLOYEE.
- Чтобы представить менеджера, вставьте одну строку в EMPLOYEE и одну строку в MANAGER.
У отдела может быть несколько сотрудников.
Каждый отдел имеет ровно 1 менеджера, и каждый менеджер управляет 0 или 1 отделами.
Наблюдателем может быть обычный сотрудник или менеджер.
Отделы не обязаны "соответствовать":
- Руководитель может работать в другом отделе от контролируемого сотрудника.
- Менеджер может управлять другим отделом, из которого он работает.
- Если руководитель является менеджером, то отделы, которыми он управляет, отделы (ы), в которых он работает, и отдел его/ее контролируемых сотрудников могут быть разными.

ПРИМЕЧАНИЕ. Если ваша СУБД не поддерживает отложенные ограничения, вы должны сделать DEPARTMENT.MANAGER_ID NULL возможным, чтобы разбить цикл, который в противном случае не позволял бы вам вставлять новые данные.

Если необходимо, чтобы отделы соответствовали друг другу, тогда вы либо применяете технику, специфичную для СУБД (например, триггеры или "специальные" ограничения), либо "распространяете" DEPARTMENT_ID в ПК сотрудников. Это распространение в конечном итоге позволяет выполнить сопоставление:

Так как EMPLOYEE_ID должен быть глобально уникальным, он не может оставаться в составном ключе вместе с DEPARTMENT_ID. Итак, мы делаем его альтернативным ключом и вместо этого используем суррогатный EMPLOYEE_NO в ПК.

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

Если вы не знакомы с символом...

... он обозначает "категорию". В этом контексте вы можете просто интерпретировать его как отношение "1 к 0 или 1" между EMPLOYEE и MANAGER.

Ответ 2

Не вдаваясь в подробности, я заверяю вас, что решение Employee/Manager/Department в долгосрочной перспективе является источником неудовольствия (сначала), а затем реальной PITA (позже) для лиц, ответственных за поддержание базы данных и/или разработки интерфейса. Поэтому я рекомендую вам придерживаться вашего второго предложения.

Что касается отношения между менеджером и отделом, у вас есть в основном два способа представления этого отношения. Оба решения разрешают вам поддерживать рекурсивное отношение "Менеджер управляет сотрудником" в дополнение к соотношению "менеджер управляет отделом", которое вы можете реализовать следующим образом:

1 - первый/простой способ: добавить идентификатор менеджера/сотрудника в таблицу отдела. Это поле, конечно, является внешним ключом таблицы сотрудников

2 - второе/более сложное решение: добавьте таблицу "manager" со следующими полями:

Manager id (PK, surrogate)
Department id (FK)
Employee id (FK)
beginningDate
endingDate

где вы будете хранить историю управления: кто, для какого отдела, с того момента, когда

В этом случае не забудьте добавить некоторую логику (триггер или управление на стороне клиента), чтобы перевести свои бизнес-правила, например, у вас может быть только один менеджер на определенный период и определенный отдел, ни один отдел не может оставаться больше, чем... без менеджера и т.д.

EDIT:

3 - более богатое решение будет обобщением моего второго предложения и позволит вам следить за каждой карьерой в компании. Вы можете сделать это с помощью таблицы "works in", такой как эта (как мы ее называем здесь "позиция", я буду придерживаться той же терминологии здесь:

Position id (PK, surrogate)
Department id (FK)
Employee id (FK)
Position Level (FK)
beginningDate
endingDate

Где "уровень позиции" ведет к другой таблице, содержащей разные позиции, которые могут существовать в отделе, причем один из них, конечно, является "менеджером".

Это предложение ближе к тому, что используется в базе данных и программном обеспечении HR, и вам может не понадобиться такое сложное решение. Но имейте в виду, что расщепление людей в нескольких таблицах ВСЕГДА совершает ошибку.

EDIT: после вашего комментария...

Чтобы все было ясно, я бы посоветовал вам настроить имена полей. Я предлагаю вам иметь следующие поля:

Tbl_Employee.id_EmployeeManager

Tbl_Department.id_DepartmentManager

Выполняя это, мы (или любой разработчик) сразу поймем, что id_EmployeeManager участвует в рекурсивной связи между людьми, а id_DepartmentManager участвует в отношениях между людьми и отделом.

Вернитесь к своим вопросам, и, по мне, вы не должны создавать следующую ссылку:

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_EmployeeManager

Таким образом, вы имеете в виду, что кто-то не может быть менеджером отдела , если он уже управляет сотрудниками. Как насчет отделов с одним сотрудником? Как насчет людей, названных менеджерами недавно созданного отдела, где еще не выделен сотрудник? Это не работает. Правильная ссылка должна быть:

Tbl_Department.id_DepartmentManager -> Tbl_Employee.id_Employee

Разумеется, вы можете добавить некоторые бизнес-правила, в которых, например, говорится, что "сотрудник, управляющий отделом, может быть только менеджером" (id_Employee существует где-то как id_EmployeeManager) или "у сотрудника, управляющего отделом, не может быть менеджера (где id_EmployeeManager для этого employee is null...) Но это только бизнес-правила. Ваша модель данных чиста, чтобы принимать все правила до тех пор, пока соблюдается основное правило, а именно, что отдел управляется сотрудником!

Ответ 3

Мое мнение:

Таблица Лицо, в которое вы добавите информацию как для сотрудников, так и для менеджеров, менеджеры тоже люди.:), и у вас есть поле managerId для ссылки на Id менеджера.

Отдел таблиц с информацией отдела

и, если сотрудник может принадлежать более одному отделу, создайте столбец employee_department, чтобы связать их. Если сотрудник может принадлежать только одному отделу, и вам не нужно больше информации в отношении, добавьте поле departmentID в таблицу Employee.

Ответ 4

Я думаю, что это лучшее решение:

Менеджер - это сотрудник, который управляет отделом. Рекурсивное отношение, которое вы можете получить в следующем потоке:

Сотрудник имеет отдел Отдел имеет сотрудника в качестве менеджера.

Может быть, это удобно для указания таблице employee столбца EmployeeType для определения роли.

Ответ 5

Как насчет того, чтобы придерживаться 2-го проекта и иметь псевдо-отношения?

Я предполагаю, что у вас будет столбец department_id в сущности Employee, чтобы связать отношения между Employee и подразделениями. Если мы можем предположить, что иерархии менеджеров не будет (менеджеров менеджеров), мы можем обеспечить псевдосвязь между двумя таблицами, где department_id для менеджеров (Manager_ID - Null) представляет собой Управление, которым они управляют.

Пока вы четко документируете это, я думаю, что это будет подход, основанный на использовании пространства, поскольку у вас уже будет столбец FK (department_id) в сущности Employee, ссылающийся на объект отдела.