relationships between two tables and many cards

[isaaccs]

I'm creating a project management database. I'm having trouble defining relationships between two of the tables. The "project" table tracks projects, and the "contact" table tracks contacts & companies. It's easy to set one individual as the primary contact for a project, but my client wants multiple contacts from different companies to be listed on each project card. How do I relate otherwise unrelated contacts by project and have them appear in a portal. Ie, anyone of the contacts who had any hand in the project should be listed on the project entry. I can't get more than one contact to appear. Any help? Much appreciated.

[QuinTech]

Hi, isaaccs, and welcome to FM Forums!

One way to do this would be to insert multiple key values into your primary key field. That is:

I assume you are using ID numbers, rather than something changeable such as company name, as your relationship key. If not, you should start doing so. So say the relationship in question is from Project::ContactID to Contact::ContactID.

If you put multiple values into Project::ContactID, separated by carriage returns, then all Contacts whose ContactIDs are listed in that field will be related to that Project. This is the quick&dirty way to do it, and possibly appropriate for your project.

However, i would like to suggest you look into join tables, which are usually the best way of creating these "many-to-many" relationships. Do a search on FM Forums for join tables and you should come up with a wealth of information. In a nutshell, a join table does nothing but link multiple records with multiple records; records in such a file would look like:

PROJECTID   CONTACTID

---------   ---------

Proj001     Cont001

Proj001     Cont002

Proj002     Cont002

Proj003     Cont003

This shows that Proj001 is associated with Cont001 and Cont002; that Proj002 is associated with only Cont002; that Proj003 is associated with only Cont003, etc.

HTH,

Jerry

[transpower]

Jerry:

I agree with your last paragraph--I think the correct architecture would be to have four tables: projects, companies, contacts, and project-contacts (the join table).