Проектирование баз данных SQL Server 2005 года - many-many отношения с иерархией

Попробуйте дать каждой сущности собственную таблицу, например

//Table Structure
location
    locationId
    name

division
    divisionId
    name
    locationId (fk => location)

department
    deparmentId
    name
    divisionId (fk => division)

function
    functionId
    name
    departmentId(fk => department)

jobrole
    jobroleId
    name
    functionId

course
    courseID
    name

jobrole_course_requirement
    jobroleID
    courseID

employee
     employeeID
     name

employee_jobRole
     employeeID
     jobRoleId

emploeyee_course_attendance
     emploeyee_course_attendanceID
     emploeyeeID
     courseID
     dateAttended

И некоторые примеры селектов

// Get course requirements for an employee
select course.name 
  from course, 
       jobrole_course_requirement, 
       employee_jobRole
  where 
       employee_jobRole.employeeID = 123 and
       jobrole_course_requirement.JobRoleId = employee_jobRole.JobRoleId
       course.courseID = jobrole_course_requirement.courseID

Обычно, когда я настраиваю базу данных, я придумываю, какие сущности мне нужны и как они связаны друг с другом (т.е. много-один, один-один, ...). Что вы, похоже, и сделали. Далее я выясняю, что нужно каждой сущности. Например, местоположение может иметь: locationid, адрес, ... Затем, отделы Предполагая, что есть одно местоположение для многих отделов, вы могли бы сделать так, чтобы сущность отдела имела divisionid, locationid, информацию, необходимую каждому отделу. Поэтому, в принципе, если это отношения "один-много", например, одно местоположение для многих подразделений, вы можете просто поместить id местоположения в таблицу подразделений. Однако, если это отношение "многие-ко-многим", то, вероятно, лучше иметь промежуточную таблицу для связи между ними, чтобы не иметь дублирующихся записей с изменением только id.

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

Никакая заслуживающая внимания база данных не будет иметь малейших проблем с обработкой миллиона записей, то есть крошечной базы данных.

Вам нужны таблицы для функций, курсов, местоположений, людей, организации и, возможно, некоторые объединяющиеся таблицы, чтобы учесть многие и многие отношения. Но все это не сложно и даже не выходит за рамки базового дизайна. Я рекомендую вам, прежде чем что-либо делать, получить книгу по выбранной вами базе данных и ознакомиться с основами.

Вам нужно простое звездное отношение. Должность (таблица фактов) имеет только идентификаторы связанных главных таблиц (Отдел, Подразделение и т.д.). Это позволяет использовать любую комбинацию основных таблиц

Основные таблицы могут иметь простую иерархию, встроенную в каждую из них по мере необходимости. И могут соотноситься друг с другом по мере необходимости. Но детали этого не влияют на запросы к Position

Вы можете сделать ID в Position nullable для необязательных отношений

Вы можете добавить столбцы StartDate и EndDate в Position для отслеживания изменений во времени

Простой пример:

Возможно (возможно) вам следует рассмотреть отдел кадров текстильного отдела как отдельный отдел, нежели отдел кадров отдела маркетинга.

Поскольку вы «абекедарианец» :), перед любой попыткой освоить дизайн базы данных нужно прочитать о нормализации и полностью понять все нормальные формы вплоть до 5NF

Если вы хотите чтобы смоделировать это
1. отделы входят в подразделения
2. функции выполняются в отделах
3. сотрудники выполняют функции

и что не все функции выполняются во всех отделах, и не все отделы находятся во всех отделах, то вы должны где-то сохранить этот факт.

При логическом проектировании дайте вашим таблицам описательные имена, чтобы некоторые отделы входили в подразделения

departments_in_divisions
candidate key: department, division

, затем у вас были некоторые функции в некоторых отделах

functions_departments_divisions
candidate key: function, department, division
references: (department, division) in departments_divisions

, затем у сотрудников были некоторые функции из некоторых отделов и отделов

employees_function_department_division
candidate key: employee, function, department, division
references: (function, department, division) in functions_departments_divisions

После (или до этого) у вас есть еще 3 сущности, функции, отделы и подразделения, которые будут перечислять все возможные отделы, подразделения и функции, на которые также будут ссылаться приведенные выше таблицы (это может быть не полностью нормализовано).

Также имена сущностей (таблиц) могут стать для вас более подходящими (только вы можете знать полную семантику модели ваших данных). Особенно, если вы заметили, что вам нужно присвоить им другие атрибуты (поля).

Значениями для отделов, подразделений и функций являются их названия, в приведенном выше анализе еще нет искусственных идентификаторов. Вы можете представить их на следующем шаге, после логического моделирования, после физического моделирования, или вы можете оставить естественные ключи.Если вы используете искусственные ключи, которые могут сократить использование составных ключей до максимального значения 2, но при этом скрывают связи и значение фактов, которые вы храните в своих таблицах. (Пример functionID может быть и Идентификатор имени функции или идентификатор функции, которая выполняется в определенной комбинации подразделения / отдела - непонятно, что это такое, и они не взаимозаменяемы; вроде как разница между экземпляром и классом).