Table Normalization

[LAM]

In this case should I have two separate company tables?

I need to associate some companies with other companies can I do this in the same table?
Here are some scenarios
Company A is our client, but is affiliated with Company B
Company C has a Sister Company that may or may not be our client
Company D sold all or part of their company to Company E
Company F bought all or part of Company G

Typically only one company in the equation will be our client.

How do I set up a table that can reference other companies in the table?
Is it better to have two separate tables: Clients; and Affiliates.

We also contact other companies on behalf of our clients. My thoughts were to have these companies in separate tables as well.

Another factor that is pushing me in the direction of separate tables is that there is different information that needs to be kept for each type of company.

I am having the same difficulty with contacts. Since contacts can be assoicated with a client an affiliate, or another third party company. How do I associate the contact with the right company information if I have separate tables for each company type?

For example in the contacts table there are fields for: Type of Contact, Name, Phone, etc. I need to associate the contact with a company that could be a client, or an affiliate so the company information may be on either table.

I may be over-thinking this, but have not made any headway in figuring out how this would work.

Any help is appreciated.

LAM

 

[Kryst51]

In this case should I have two separate company tables?

I need to associate some companies with other companies can I do this in the same table?
Here are some scenarios
Company A is our client, but is affiliated with Company B
Company C has a Sister Company that may or may not be our client
Company D sold all or part of their company to Company E
Company F bought all or part of Company G

Typically only one company in the equation will be our client.

How do I set up a table that can reference other companies in the table?
Is it better to have two separate tables: Clients; and Affiliates.

We also contact other companies on behalf of our clients. My thoughts were to have these companies in separate tables as well.

Another factor that is pushing me in the direction of separate tables is that there is different information that needs to be kept for each type of company.

I am having the same difficulty with contacts. Since contacts can be assoicated with a client an affiliate, or another third party company. How do I associate the contact with the right company information if I have separate tables for each company type?

For example in the contacts table there are fields for: Type of Contact, Name, Phone, etc. I need to associate the contact with a company that could be a client, or an affiliate so the company information may be on either table.

I may be over-thinking this, but have not made any headway in figuring out how this would work.

Any help is appreciated.

LAM

You are definitely not over thinking this. These are excellent questions. I have one for you, can a contact belong to more than one company? Can he/she be part of the company and an affiliate?

Can an affiliate and it parent company both be clients? or just one or the other?

 

[LAM]

Can a contact belong to both a client and an affiliate... We typically associate a contact with just one company. In the case of a parent company the contact would need to be associated with all the affiliated companies.

Can an affiliate and it's parent company both be clients.. The actual client is usually just one company, whatever company signs the contract. But in some cases we deal with both the parent company and the affiliate. Sometimes the parent company will hire us and we will perform services that include all of the companies they own. Conversely we can be hired by a company, but may need to deal with a parent company to acquire information or documents.


LAM

 

[Kryst51]

OK,

Here is what I suggest.

1.)One Company Table - fkCompanyRelationshipID in this table so that you can relate companies to eachother.

2.)One Contact Table -fkCompanyID in this table to link the two.

3.)One Company Relationship Table - Autonumber ID, pk in this one.

Someone else may have a cleaner idea.

 

[LAM]

I can see the logic in that, still a little unclear as to how the relationship table is joined to the other two tables. Maybe it will be more clear if I actually set up the tables and can see the relationships.

Another question, would I then also create other tables to handle information that pertains to only one type of company?
For instance we deal with insurance companies, and agencies in addition to the clients and affiliates. The insurance companies will need 3 different code fields which are not needed by agencies or clients.

Thanks for the input.

LAM

 

[Kryst51]

I can see the logic in that, still a little unclear as to how the relationship table is joined to the other two tables. Maybe it will be more clear if I actually set up the tables and can see the relationships.

The relationship table could potentially have only one field, a Primary Key, Autonumber ID.

You would probably relate companies in a form. Have the form bound to the relationship table,

Then have a subform or list box which has the companies. If you use a list box you can select multiple companies then design a button to assign the relationship ID to those companies foreign key field for that ID. This would "relate" the companies. Or you can get more complex and design a form that when you enter a company your can pop up a field of other companies, select a company, see the relationships that exist for it, and either add your current company to an exsisting relationship or create a new one.

Another question, would I then also create other tables to handle information that pertains to only one type of company?
For instance we deal with insurance companies, and agencies in addition to the clients and affiliates. The insurance companies will need 3 different code fields which are not needed by agencies or clients.

This is up to you, I see nothing wrong with creating a separate table As a one-to-one relationship for company type specific information. You could even create a table for company type, then as you are setting a company up in a form, select the type, then if you select say "Insurance Company" a subform could become visible, or pop-up with the additional fields for you to fill in. Alternatively you can keep those fields in the same table, and only use them on your form when you need to via visible or invisible options, or a different form for different company types (I would use visible and invisible options though, it would be cleaner.)

Thanks for the input.

Your Welcome for the Input.

 

[LAM]

Thanks again Kryst51, this gives me something to work on. I'll create the tables and relationships so I can try it in practice.

The relationship table could potentially have only one field, a Primary Key, Autonumber ID.

You would probably relate companies in a form. Have the form bound to the relationship table,

Then have a subform or list box which has the companies. If you use a list box you can select multiple companies then design a button to assign the relationship ID to those companies foreign key field for that ID. This would "relate" the companies. Or you can get more complex and design a form that when you enter a company your can pop up a field of other companies, select a company, see the relationships that exist for it, and either add your current company to an exsisting relationship or create a new one.



This is up to you, I see nothing wrong with creating a separate table As a one-to-one relationship for company type specific information. You could even create a table for company type, then as you are setting a company up in a form, select the type, then if you select say "Insurance Company" a subform could become visible, or pop-up with the additional fields for you to fill in. Alternatively you can keep those fields in the same table, and only use them on your form when you need to via visible or invisible options, or a different form for different company types (I would use visible and invisible options though, it would be cleaner.)




Your Welcome for the Input.

 

[Kryst51]

Thanks again Kryst51, this gives me something to work on. I'll create the tables and relationships so I can try it in practice.

Your welcome again!

Let us know if you have anymore questions or need more help.

 

[LAM]

Kryst51,

I have created the three tables:

tblCompany
CompanyID (PK)
CompanyType
AffiliateID (FK)
CompanyName
Address
(etc.)

tblAffiliate
AffiliateID (PK)

tblContacts
ContactID (PK)
CompanyID (FK)
ContactType
Name
Phone
(etc.)

So companies that share an affiliate Id are related, correct?
I also need to specify how they are related. Any suggestions?

LAM

 

[Kryst51]

Umm... Read my example again.... Keep the companies altogether, but make a relationship table so that:

tblCompanies
pkCompany ID - Autonumber, pk
CompanyName
fkCompanyType
fkCompanyRelationshipID - Number

tblCompanyRelationships
pkCompanyRelationshipID - Autonumber, pk
fkCompanyRelationshipTypeID - Number

tblContacts
pkContactID - Autonumber, pk
ContactFname
ContactLname
fkCompanyID - Number

tblCompanyTypes
pkCompanyTypeID - Autonumber, pk
ComapnyType - ex: Client, Insurance, etc.

tblCompanyRelationshipTypes
pkCompanyRelationshipTypeID - Autonumber, pk
CompanyRelationshipType - ex:Affiliated, Parent/Sister, etc.

Try something like this.... I may have too many typ tables, not understanding your paradigm, so if I do, just modify.

 

[LAM]

That may accomplish what I want, I'll give that a try.
By the way, I do only have one company table, my affiliates table is your companyrelationships table, I shouldn't have changed the name in my example.

Thank you very much your input has been helpful.

LAM

 

[Kryst51]

That may accomplish what I want, I'll give that a try.
By the way, I do only have one company table, my affiliates table is your companyrelationships table, I shouldn't have changed the name in my example.

Thank you very much your input has been helpful.

LAM

OK, well, good then! Good luck.