MySQL question regarding tables

[coolo]

So, this is what I'm trying to do:

I have a list of companies in one table with fields for address and contact and phone, etc.

Each of these companies can have an infinite number of products that they carry, and I'd like to create a second table that I can relate back to the first with this information. But it seems to me, each company would have to have its own table to list all the products. Am I thinking about this right?

How do I go about connecting these two sets of information. I'm thinking I need to join tables, but can't figure out how that works. As usualy, any and all help is appreciated.

[willybfriendly]

Not sure if I understand fully.

Perhaps an ID field for each company, and then a CompanyID field for each product. This would allow all products to be listed in one table, and index back to the company table.

WBF

[coolo]

Basically, I want to organize all of the following information into a database, and am trying to figure out the best way.

I have a list of companies that I expect to continue growing (and grow quite large).

I have contact info (including address, email, phone, etc.) for each company.

And I have a list of products that each company carries. However, this list of products is continually growing as well, and is expected to be quite large for some of the companies. And, some of the products will be listed with multiple companies. With this being the case, I can't see how I can fit this product information into the same table as the contact info.

What I'm thinking is that I should have one table that lists all the companies with their contact info, and then each company breaks into it's own table to list all of the products it carries. Does this make sense? Is it feasible?

[coopster]

You may even want to consider three tables, contacts, products and a cross reference (if the same product could be carried by different companies). The structure might be something like this...

contacts

contact_id 
contact_name 
address 
etc.

products

product_id 
product_name 
description 
etc.

products_carried

contact_id 
product_id

Populating the first two tables is fairly straightforward, enter your contacts and enter the products. The last table then would be "connecting" each contact with a list of the products they sell.

[coolo]

Thanks for the idea Coopster. If I get what you mean, then the third database would contain each combination of company and product that exists. Therefore if I had a db with company ids x, y, and z, and product ids of e, f, and g, then I might have a table that looked like the following...

+------------+------------+
¦ Company_Id ¦ Product_Id ¦
+------------+------------+
¦ ____x_____ ¦ ____e____ ¦
+------------+------------+
¦ ____x_____ ¦ ____g____ ¦
+------------+------------+
¦ ____y_____ ¦ ____g____ ¦
+------------+------------+
¦ ____z_____ ¦ ____e____ ¦
+------------+------------+
¦ ____z_____ ¦ ____f____ ¦
+------------+------------+
¦ ____z_____ ¦ ____g____ ¦
+------------+------------+

So, there would be multiple entries for each company that carried multiple products. Just trying to make sure I understand the concept.

[timster]

You've got it, coolo. Of course, you'll want to use integers instead of letters when you implement it.

To prevent duplciates, create a multifield key across Product_id and Company_id.

BTW, the term for what we're up to here is a "Many-to-many relationship," in case you want to research the concept more thoroughly.

[coolo]

Cool, thanks. I think I understand what I need to do now.