Many-to-Many relation 1

[HomeGrowth]

I am seeking advice on relationship on a Company Profile Database. The objective is to do minimum data entry to link the all companies which have relationship among others.

Initially, I have two tables: One called tblCompanyInfo to store the company name, address, and service provided. Another call tblCompanyRelationship to store the relationship among others. I used two fields to make up the primary key, there are FirstCompany and SecondCompany . The 3rd filed is the relationship description, it included (for examples):
1. Company A is purchased by Company B (Company B’s old name is Company A)
2. Company C is the manufacturer for Company D (D is the distributor)
3. Company E is supplier for Company F (the source material that Company F makes is coming from Company E)

I already created the tables and form to try it out. Here is my problem for relationship # 1 above. I have a list of companies which was purchased one to another up to 4 level. For example:

Company A is purchased by Company B, Company B is purchased by Company C, Company C is purchased by Company D. In the tblCompanyRelationship, I have to enter 3 records for each company to record the relation, which means I have enter 12 records in tblCompanyRelationship to get the full referencing all 4 companies. This will become maintenance nightmare. It could nice that for each record, all I need to do is entering one relationship record (who is purchased who), and then when you take a look for a company, you would know this company is now Company D every time you look at Companies A, B, or C.

Is it called Many-to-Many relationship? How do you do it? Many thanks.

 

[fneily]

Have you seen:
Fundamentals of Relational Database Design

http://r937.com/relational.html

Can a supplier be a manufacturer or a distributer? Or can any other combination be made? You could have one main table:
tblCompanyInfo
CompID Primary Key
Name
Address
etc.

Now have four category tables:
tblSupplier
SuppID Primary Key
CompanyID Foreign Key
SupplierID Foreign Key (filled in with a CompanyID)

tblManufacturer
ManID Primary Key
CompanyID Foreign Key
ManufacturerID Foreign Key (a CompanyID)

tblDistributor
DistID Primary Key
CompanyID Foreign Key
DistributorID Foreign Key ( a CompanyID)

Reason - all of these can be many-to-many.

Now for the M&A part.
tblCompanyRelationship
CRID Primary Key
CompanyID Foreign Key
PurchaserID Foreign Key (a CompanyID)

This will be a self-referencing table that can have many levels. See MAJP code in thread702-1516577
to show the different purchasers.

 

[HomeGrowth]

I am looking at the posts to figuring out how a company can be referencing among other companies.

How did you link the 5 tables to get the referencing among each other? Can I have the first 3 catogory table into one table because it have the same fields, what is the advantage to separate them?

Thank you

 

[fneily]

tblCompanyInfo ---> tblSupplier <---- tblCompanyInfo
CompanyID -----> CompanyID
SupplierID <---- CompanyID

Same scenario for Distributers and Manufacturers.

"because it have the same fields". You have different Categories - Suppliers, Distributers, Manufacturers.

Maybe you can post the table structure you think it should be. And is is normalized? Any blank cells? How do you handle a company that has more then one supplier? etc.

MajP code shows how to relate the purchasers. So you'd get a list of CompanyD bought CompanyC who bought CompanyB who bought CompanyA.

 

[HomeGrowth]

I see a little light, but I want to digest further with the related posts.

on "You have different Categories - Suppliers, Distributers, Manufacturers."

Can I add a Category field in order to combine all the Category tables into one table? the Category field will capture whether this record for Supplier, Distributer, Manufacturer, or Acquirer(Purchaser).

another question: what is the meaning of SuppID, ManID, DistID, and CRID? Can I combine CompanyID and SupplerID (or ManufacturerID, DistributorID, PurchaserID - I will name it RelatedCompanyID). This way, it will prevent duplicate records.

tblCompanyInfo --> tblCompanyRelationship <-- tblCompanyInfo
CompanyID -----> CompanyID
RelatedCompanyID <---- CompanyID

I thought the self-join is the idea for the whole process. right? Will it work?

 

[fneily]

another question: what is the meaning of SuppID, ManID, DistID, and CRID?" Those are primary keys of their respective tables. All Access tables have primary keys.

Oops. Noticed a typo. In tblCompanyInfo, it should be CompanyID not CompID. My fault.

SupplierID, ManufacturerID, DistributorID,,PurchaserID are just field names They can't be called CompanyID because then you would have duplicate field names which is not allowed in a single table. But they all contain CompanyID from the tblCompanyInfo table.

Let's say you have one field called Category. How do you show that CompanyA has, let's say, 3 suppliers? My tblSupplier, which is called a junction table, can handle this with no problem. How would your table do it?
Also, can a supplier also be a manufacturer?
Can a distributer be a supplier? Etc.
Separating the categories into their own tables can take care of all these combinations. In fact, an Access table is a category/topic.

The purchase table would be the only true self-referencing table. It's structure is basically:
PrimaryKey Child(Company) Parent(who bought the company)

If you post what you think the table structure should look like, I can get a better idea of what you're thinking.

 

[HomeGrowth]

My original thinking is to have 2 tables to do the relationship among each others

In the 2 tables: I would have the following key fields

tblCompanyInfo
CompanyID Primary Key
Name
Address
etc.

tblCompanyRelationship
CompanyID Primary Key (a CompanyID)
RelatedCompanyID Primary Key (a CompanyID)
Category Primary Key (Value List: “is Supplier”; “Is Manafacturer”; “Is Contributor”; “Purchased”)

In the relationship, I would link them this way

tblCompanyInfo --> tblCompanyRelationship <-- tblCompanyInfo
CompanyID -----> CompanyID
RelatedCompanyID <---- CompanyID

In record you would see:

tblCompanyInfo(CompanyID is the primary key)

CompanyID Address
CompanyA 123 ABC Street
CompanyB 100 Main Street
CompanyC 280 Industrial Road
CompanyD 3000 First Street
ComPanyE One Washington Avenue
CompanyF 2001 Jefferson Blvd
CompanyG 101 Bay Avenue
CompanyH 1212 Santa Road

tblCompanyRelationship(Remember, all 3 fields combined to become primary key)

CompanyID Category RelatedCompanyID
CompanyB is Supplier CompanyA
CompanyC is Supplier CompanyA
CompanyA is Distributor CompanyG
CompanyG is Manufacturer CompanyH
CompanyE Purchased CompanyD
CompanyF Purchased CompanyE

Do you see my table structure? Back to my question earlier: I thought the self-join is the (your) idea for the whole process. I am going to test it out.

 

[fneily]

IF that's all the data you are storing, I'll buy it. BUT if you are going to add fields, for example:
supplier part cost
or
manufacturer part
or
distributor part cost
or
purchaser date price
etc. etc.

then you can not use that table. Adding any fields to it will make it non-normalized. So it's not very flexible for future development.

So, in your case, you'd just pick off the records with "Puchased" and use the code to create the hierarchy.

 

[HomeGrowth]

Thank you for your advice. For now, this table is only capturing that relationship. This database is going to track the relationship, for new comers to learn the business among all the companies that we are dealing with.