Relational Structure

[Guest]

I'm building a database for an organization that has many members companies
and contacts for each company. Some of the individual companies that are
members are part of a parent company, but they all pay for their own
memberships.

As a result, the same contact person can exist for multiple companies. On a
company form I created I have a subform that displays and edits all of the
contacts for that company. On a contact form I created I have a subform that
displays and edits all of the companies for that contact.

I don't want the same company to have duplicate records, nor do I want the
same contact to have duplicate records, but I need to find a way to establish
relationships and data-entry forms that allow me to both add contacts under a
company using the autonumber unique ID assigned for each contact and company
entered both ways. Right now the relationships and forms let me do the
contact under company easily, but I've not figured out how to add multiple
companies to the contact without creating a duplicate company with a new
unique ID#.

I am leaning towards making a sub-subform with search/auto-fill code to do
this, but I'm not sure about the structural integrity that may compromise the
database, or just do it manually with periodic cleanup of duplicates on the
backend. Any suggestions on how to construct these relationships?

 

[Allen Browne]

Download the sample database from:
People in households and companies - Modelling human relationships
at:
http://allenbrowne.com/AppHuman.html

The suggested structure copes with making sales/mailings/contact with people
and companies, connecting people to companies, people to people, companies
to companies, and forming formal and informal groupings that consist of any
combination of persons and companies. You will then need to design the
interface in such a way that you don't get the duplicates that should not be
allowed.

 

[Guest]

Allen,

Thanks for responding. This is good, really sound stuff that I will
definitely incorporate in future databases with similar challenges.

Problem for me is that I'm very deep into this one. I have numerous queries
on queries, forms and subforms with macros, VB and controls galore, reports
and such all tied into my tables and data fields that would require weeks and
weeks of rewrite that I don't have.

Given that my Company and Contacts tables are seperate can you suggest
another way, perhaps using similar kinds of bridge tables like in your
sample? Is my sub-subform search/autofill concept possible without creating
a circular reference?

I may just be talking out my rear here, but while I wish I had posted
earlier, woulda, shoulda, coulda is not an option now. I'm hoping and
praying a total rewrite is not necessary.

 

[Allen Browne]

You have the Company and Contact tables already.
One company has many contacts.

If one contact person only ever has one company, you could just add a
CompanyID field to your Contact table. Your Company form will then have a
subform for the contacts at the form, and you can enter them there. You
could also have a form for the Contact, with a combo for selecting the
company if you wish.

If one person could be a contact for more than one company, you need a 3rd
table, with fields:
CompanyID relates to Company.CompanyID
ContactID relates to Contact.ContactID.
Again, this could be a subform on the Company form, but you cannot add new
contacts here. You could design it so that when the user double-clicks the
ContactID combo in the subform, you open the Contact form to enter the new
person, and then requery to combo so the new person appears there.

HTH