CODEWITHSUNDEEP
.netsqldatabase
user971741
user971741

Reputation:

Referencing multiple primary key(s) in another table

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

Problem is I have a table called Department, containing:

id: int
name: varchar

And another table called 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?

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.

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

Upvotes: 3

Views: 1323

Answers (1)

John Woo
John Woo

Reputation: 263743

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

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,

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)
)

Upvotes: 7

Related Questions