Избегайте дубликатов при разработке отношений «один ко многим»

Вам нужно что-то вроде этого;

CREATE TABLE department(
    dept_id      int    NOT NULL,
    dept_name    char(10)    NULL,
    CONSTRAINT PK1 PRIMARY KEY NONCLUSTERED (dept_id)
)
go


CREATE TABLE department_employee(
    id         int    NOT NULL,
    dept_id    int    NOT NULL,
    emp_id     int    NOT NULL,
    CONSTRAINT PK3 PRIMARY KEY NONCLUSTERED (id)
)
go


CREATE TABLE employee(
    emp_id      int    NOT NULL,
    emp_name    char(10)    NULL,
    CONSTRAINT PK2 PRIMARY KEY NONCLUSTERED (emp_id)
)
go


ALTER TABLE department_employee ADD CONSTRAINT Refdepartment1 
    FOREIGN KEY (dept_id)
    REFERENCES department(dept_id)
go

ALTER TABLE department_employee ADD CONSTRAINT Refemployee2 
    FOREIGN KEY (emp_id)
    REFERENCES employee(emp_id)
go

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

DepartmentMember => (DepartmentId, EmployeeId, MembershipRole)

Почему ты не хочешь этого?

Отдел => (ID, ID сотрудника, местоположение, активный) Сотрудник => (ID сотрудника, имя, должность)

Я думаю, что это был бы гораздо лучший способ организовать ваши столы. Это предполагает, что актив является свойством отдела, в противном случае переместите его в таблицу сотрудников.

Предположим, что сотрудник может работать только в одном отделе. ЕСЛИ нет, то да, вам нужна третья таблица, чтобы избежать дублирования

Работник

ID, Name, EmployeeType, DepartmentID
(pk on ID, EmployeeType)

отделение

ID, Name, Active

Должность/название очень сильно зависит от контекста отдела. Можно быть региональным менеджером в одном отделе и дополнительно занимать должность консультанта в другом отделе.

Тогда отдел и Сотрудник относятся ко многим. Сотрудник на позицию также является многими ко многим. Если вам нужна гибкость, например добавление нового заголовка для отдела, необходимы соединительные таблицы. Вы не можете этого избежать.

Вы можете обратиться к следующей структуре таблицы для справки:

Employee 
-----------------------
EmployeeID (PK)
EmployeeName
Active

Department 
-------------------------
DepartmentID (PK)
DepartmenName
Location

Position 
----------------------------
PositionID (PK)
PositionDescription (eg.Clerk, Accountant etc)

EmployeePosition 
----------------------------
EmployeeID  (FK to Employee.EmployeeID )
DepartmentID (FK to Department.DepartmentID)
PositionID (FK to Position.PositionID )

Если Должность/Название закреплено за Сотрудником, а не за Отделом, т.е. Сотрудник, который является клерком и может занимать эту должность в одном или нескольких отделах, как мы можем это сделать?

Вы имеете в виду, что в крайнем случае многие сотрудники могут иметь свои специальные звания? и они принадлежат многим отделам? Если да, предположим, что сотрудник с идентификатором 123 имеет специальное звание «Особенный» и относится к отделу информационных технологий, бухгалтерского учета и продаж. Сначала вы создаете этот титул (например, «Особенный») в таблице Position и получаете Position.PositionID.

Затем вы вставляете 3 записи для Employee.EmployeeID 123 в таблицу EmployeePosition, используя этот Position.PositionID и идентификатор отдела ИТ, учетную запись, отделы продаж.