引用另一个表中的多个主键

I am developing a new web application and for that I also need to define its database structure.

我正在开发一个新的Web应用程序,为此我还需要定义它的数据库结构。

Problem is I have a table called Department, containing:

问题是我有一个名为Department的表,包含:

id: int
name: varchar

And another table called Employee

另一个名为Employee的表

id: int
name: varchar
dept: Department.id(s) (Foreign Key)

Issue is an employee could belong to multiple departments and I am unable to figure out how to store this information in the table, for e.g.:

问题是员工可能属于多个部门而我无法弄清楚如何在表格中存储此信息,例如:

If client 001 belongs to Department 004,005 and 006 than how do I store these three department keys in one column i.e. Employee.dept?

如果客户001属于Department 004,005和006而不是如何将这三个部门密钥存储在一列,即Employee.dept?

I figured out that one possible way could to store them as delimited strings like “004,005,006”, But for this I would have to convert them back and forth to string and int and than search the string for a particular occurrence.

我发现有一种可能的方法可以将它们存储为分隔字符串,如“004,005,006”,但为此,我必须将它们来回转换为字符串和int,然后搜索字符串以查找特定事件。

Can anyone kindly help me suggesting an ‘efficient’ and correct solution for this problem?

谁能帮助我建议一个'有效'和正确的解决方案来解决这个问题?

1 个解决方案

#1

Don't store those ID as comma separated value. It's a very very bad design. Properly normalize the table.

不要将这些ID存储为逗号分隔值。这是一个非常糟糕的设计。正确地将表格标准化。

This is a Many-to-Many relationship. So you should have three tables on your schema,

这是一个多对多的关系。所以你的架构上应该有三个表,

Department

ID (PK)
Name

Employee

ID (PK)
Name

Employee_Department

DepartmentID (FK)
EmployeeID (FK)

so when translated to real DDL,

所以当翻译成真正的DDL时,

CREATE TABLE Department
(
    ID INT,
    NAME VARCHAR(50),
    CONSTRAINT dept_pk PRIMARY KEY (ID),
    CONSTRAINT dept_uq UNIQUE(Name)
)

CREATE TABLE Employee
(
    ID INT,
    NAME VARCHAR(50),
    CONSTRAINT Emp_pk PRIMARY KEY (ID),
    CONSTRAINT Emp_uq UNIQUE(Name)
)

CREATE TABLE Employee_Department
(
    DepartmentID INT,
    EmployeeID INT,
    CONSTRAINT empdep_pk PRIMARY KEY (DepartmentID, EmployeeID),
    CONSTRAINT empdep_FK1 FOREIGN KEY (DepartmentID)
        REFERENCES Department(ID),
    CONSTRAINT empdep_FK2 FOREIGN KEY (EmployeeID)
        REFERENCES Employee(ID)
)

#1