1

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?

  • The ID from ProjectClient can be used as Foreign Key in table ` ClientContact` to link a project and client both with a contact. – vijayp Mar 18 '15 at 9:06
  • Thanks @vijayp, however that solution would limit the contact to one project where as I would like to be able to relate a contact to multiple projects (the same number of projects as the Client or fewer) – pera Mar 18 '15 at 9:22
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...

ERD

| improve this answer | |
  • Excellent, I was thinking of linking "project contact" directly to the project table but this seems to be a better design! I will implement it later today and let you know how I get on! Many thanks – pera Mar 20 '15 at 10:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.