Many to One Relationship
I have encountered a situation where I require a one to many relationship and have had some success but I'm not sure that my solution is the correct way to proceed.
I have 2-tables: Company is the parent and Contact is the child (one to many relation). I want to create a contact browse in list view that shows all the contacts and the companies they work for. Note that 1-company may have many contacts and 1-contact only has 1-company.
My solution so far was to create a new table called CustomerContact. I joined the Customer table via _pkCustID to _fkCustID in the CustomerContact table. I then created a TO for Contact table and joined the original Contact table via _pkContactID to _fkContactID in the TO Contact Table.
In my browse I can see many occurances of company ID's and single occurances of contact ID's - so it appears to work. Now I'm wondering how to display the actual names of companies and contacts in place of the ID's. Also, is may approach correct for this adhoc many to one relationship?
If you have this relationship:
Contacts::_fkCompanyID = Companies::_pkComapnyID
Then your layout does not require this extra table.
You can make a layout based on Contacts to list your individual contacts, but include fields, such as the company name from the Companies table as well.