CODEWITHSUNDEEP
sqlreferential-integrity
user3467440
user3467440

Reputation:

Referential integrity where multiple allocations in each table?

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?

Upvotes: 0

Views: 47

Answers (4)

Venkataraman R
Venkataraman R

Reputation: 13009

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.

Upvotes: 0

IvelinaMarinova
IvelinaMarinova

Reputation: 71

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. enter image description here I hope it works for you.

Upvotes: 0

Doan Cuong
Doan Cuong

Reputation: 2614

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270993

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

Upvotes: 1

Related Questions