Relationship and ERD Query

[emncwundy]

I am having trouble getting my head around the appropriate relationship setup between tables in my database.

Tables in question include Clients, Contacts, Communication and Projects.

Clients (parent) have multple Contacts (child)

Communication (phone converstaion, email, or fax) occurs with one or more of the contacts from one client, in relation to one or more projects.

I had intially set up a client layout showing related communication via a portal. I wanted to nominate which contact person the communication is occuring with but couldnt seem to get the related name values to appear in a value list. After doing some research it has occured to me that perhaps I have the relationship set up round the wrong way because one 'communication or conversation' can occur with more than one of the clients contacts at the same time (ie an email).

But I am having trouble working out if there should be an additional LI table in between contacts, communication and projects.

Please see a draft/tester of the ERD. Any comments/suggestions gratefully received.

TesterERD.pdf

[dansmith65]

Communication (phone converstaion, email, or fax) occurs with one or more of the contacts from one client, in relation to one or more projects.

If you really want to restrict a communication record to one client, you need to store the _fkClientID in the Communication (or CommunicationLi) table.

If you want to associate a Communication with more than one Contact, you have two options: another table (like CommunicationsLi), or a multi-key field

To use another table: add it between Communication and Contacts

To use a multi-key field: change the _fkContactID field to text, then store ContactID's in it as a value list (return deliminated ie: "123¶456¶789").