I am in the process of setting up a database that among other things will track projects, clients, contacts. I am however stuck on how to design one piece of relationship. The part of the database that relates to this question have the following layout.
Table: Project
ID INT
ProjectNumber NVARCHAR
...
Table: Client
ID INT
Name NVARCHAR
...
Table: ProjectClient
ID INT
ClientID INT (FK Client ID)
ProjectID INT (FK Job ID)
PrimaryClient BIT (w unique index to prevent duplicates)
Table: ClientContact
ID
FirstName NVARCHAR
LastName NVARCHAR
ClientID INT (FK Client ID)
Every project can have multiple clients (companies or individuals) and those can in turn have multiple contacts. My problem is that currently all contacts are assigned to the project where the client is assigned, I would like to be able to assign specific contacts to each project.
What would be the best way to accomplish that?
Best Answer
You need to add another table:
ProjectContact
that will be an intersection betweenProjectClient
andClientContact
. This will let you say which contacts apply to a particular project.The issue that you're going to have is controlling the integrity of the data. You want to make sure that all project contacts come from clients that are actually on the project. This can be done with declarative referential integrity (DRI) or by application logic. Based on your table design, you seem to be pretty firmly in the camp of "every table gets a surrogate key" which makes using the DRI approach a little harder.
To use DRI for this, you want the to use a compound PK for both
ProjectContract
andClientContact
and in each case it should containClientID
. The same is true for the new intersection table,ProjectContact
. This will ensure thatClientID
will be the same and your data must make sense.Here is an illustration of what I'm suggesting...