I am trying to create a database for projects. For each project, there can be multiple estimates, multiple invoices, and multiple related companies and contacts.
Right now I am working out the companies / contacts part. Currently, there are tables for Jobs, Contacts, and Companies. Companies is related to contacts as a one to many relationship. Jobs need to be related to both contacts and companies as many to many relationships. I have made a table to reconcile the many to many relationship and make it one to many. This table called Link has a type field so that the role of the contact can be defined (client, assistant, etc.), and it has foreign keys for the company ID and contact ID. So then there are 4 total tables (Jobs, Companies, Contacts, Link). Table names are in parenthesis below:
(Jobs) <---->> (Link)<<---->(Companies)<----(Contacts)
(Jobs) PK_JobID <---->>FK_JobID (Link)
(Companies) PK_CompanyID <---->>FK_CompanyID (Link)
(Companies) PK_CompanyID <---->>FK_CompanyID (Contacts)
On the Jobs layout, I made a portal to the Link table. In this portal I can select the company and a related contact from pop-up menus.
However, the issue is that I am not able to display the related contact data, such as their mobile phone number or personal email address. It currently defaults to one of the contacts mobile phone numbers and does not update as I select different contacts. I think this is because I am not sure how to link the contact ID between Contacts and Link. I've tried creating occurrences to fix this problem, but haven't been able to get the right result.
The other problem is that there can be several contacts from a company on the same job and they can be different types and I need to display unique data from each of them.
Also, in some cases, we do not have a specific contact, but only the company name so this is why I am not connecting the link table to the contact table and then pulling in the company data from there.