Normalization issue

[juliejoanne]

The main part of my membership/registration database deals with contacts in three tables:
Contacts: CID(pk); companyID; addressID; firstName; lastName; etc
Company: companyID(pk); companyName
Address: addressID(pk); Address1; Address2; City; State; etc

The db has people(same company "Individual", unique addresses) and companies (same address, unique contacts). I would like to have a company "Individual" that can refer to many people, and has several different addresses. When I use a combo box on the form for data entry it lists the company names, however if I enter 2 people as "individuals" the autonumber kicks in from the company table and repeats the CompanyName so there are dublicate entries in the table. Is there a way to enter a new entry and pull ONE companyID that already exists? I thought the limit to list function would handle, but it does not work. Also, to enter a new company at the form level, would I have to utilize a subform or is there code that can be worked into the form?

Perhaps the table structure is incorrect or the link betweens tables is off. I have struggled to get this inherited mess normalized.

Thanks for your consideration.
JE

 

[lameid]

I am trying to follow what your problem is but I am having trouble because you are talking about People ("Invidual") which appear to be contacts. If you can express your problem in terms of the entities (tables), the solution may become obvious or someone can probably help.

I suspect your problem is that you can have people that can belong to multiple companies and that comapanies can have multiple employees. This is a Many to Many relationship that needs to be broken down by what is called a Bridge Entity Table (Members Below)...

People: PersonID(pk), AddressId (fk), First Name, Middle Name, Last Name, Suffix, Title

Members: MemberID(pk), companyID(fk), PersonID(fk)

*fk is short for Foreign key

In this case, I have gotten rid of your contacts table. You may need to move the AddressID field to the company table if that is appropriate.

Call the tables what your like and skip the extra fields I added to people if you like (sometime you will likely wish you included them if you don't know).

 

[juliejoanne]

I will try and make myself more clear, I apologize for any confusion. Again, if this logic seems off base- please advise.

I would like to set up the tables so that the data works under the following assumptions:
Every contact has a company and an address; the company and address information is stored in different tables-therefore they are mutually exclusive.

-Each person is associated with one company and one address- stored in the contacts table . Different contacts may have the same combination of companyID and addressID (5 different people from the same bank location). However, many contacts may have the same companyID and different corresponding addressIDs (5 people with the company name, "Individual" with different addresses). I was not going the many to many route as it is unlikely that I will have a contact associated with 2 different companies.

I should also say that I have all the pks(CID, companyID and addressID) set up as autonumbers.
Thanks so much.

 

[lameid]

Same problem different tables...

Remove addressID from Contacts.

Create a table

ContactAddresses: CAID (PK); CID(fk); addressID(fk);