Database design - Projects with multiple Clients but only specific client contacts

Question

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?

Answer 1

You need to add another table: ProjectContact that will be an intersection between ProjectClient and ClientContact. 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 and ClientContact and in each case it should contain ClientID. The same is true for the new intersection table, ProjectContact. This will ensure that ClientID will be the same and your data must make sense.

Here is an illustration of what I'm suggesting...