Jump to content
Sign in to follow this  
emncwundy

Relationship and ERD Query

Recommended Posts

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

Share this post


Link to post
Share on other sites

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").

  • Like 1

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×

Important Information

By using this site, you agree to our Terms of Use.