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?
ID
fromProjectClient
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