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?