Referential integrity where multiple allocations in each table?

Question

Ok so, I have a Projects Table and an Employees Table. First I keep getting very confused how I should set up the PK and FK in general.

I have Customers table and I want it so 1 customer can have multiple Projects at a time. So the solution is to put CustomerId into the Projects table and make it a foreign key right?

So the problem is, I want multiple employees to be a part of projects at any given time, and obviously each Project will need multiple employees. How do I set up Referential Integrity between them?

Many-to-many relations are usually implemented with the help of a separate link table. — PM 77-1, Jun 30 '14 at 02:48

score 1 · Answer 1 · answered Jun 30 '14 at 02:48

1

You need an additional table, ProjectsEmployees. It will have a foreign key relationship to both Projects and to Employees.

answered Jun 30 '14 at 02:48

Gordon Linoff

1,242,037
58
646
786

score 1 · Answer 2 · answered Jun 30 '14 at 02:51

You can make a buffer table which contain Foreign Key to both Employee and Project table to make a many-to-many relationship. Example:

Table: ProjectAssign
PK: ProjectAssignId
FK: EmployeeId
FK: ProjectId
Unique Constraint: EmployeeId & ProjectId

This way you can have many-to-many relationship between employee and project. Unique constraint to make sure you don't have redundant data.

Note

You can also apply composite key to EmployeeId and ProjectId, but I think it's not a good idea

score 0 · Answer 3 · answered Jan 14 '20 at 08:57

So you have 1 Project that can relate to multiple employees and multiple customers at once. So the Project table is your main table. You can add the ID of the project table as FK in both Employee and Customer tables. Also, the combination of the ID column can be used as a PI in order to have evenly row distribution among the AMPs. Those tables are dimensional tables and I guess it won't be millions of rows large so you don't need another relational table to store the keys. The Project table will join with both Employee and Customer tables through "one to many" relation. The Employee and Customer tables will join with the Project table through "many to one" relation.

There is some pic of what I mean. I hope it works for you.

score 0 · Answer 4 · answered Jan 14 '20 at 09:09

These kinds of many-many relationships are handled through bridge table.

You can have a bridge table, which maintains, for each customer, there will be multiple projects, and each project can have multiple employees contributing to it.

CustomerProjectEmployee

+-------------+------------+-------------+
| CustomerID  | ProjectID  | EmployeeID  |
+-------------+------------+-------------+
|           1 |          1 |           1 |
|           1 |          2 |           1 |
|           1 |          3 |           1 |
|           1 |          1 |           2 |
|           1 |          2 |           2 |
|           1 |          3 |           2 |
+-------------+------------+-------------+

Here, for projects(1,2,3) of customerID(1), Employees(1,2) are contributing.

Referential integrity where multiple allocations in each table?

4 Answers4