I am developing an online directory with PHP/Mysql and would like some advice on the most efficient way to structure the table relationships
I have a list of product categories from which the visitor makes a selection and this should generate a list of companies that fall into that category.
There will be many companies that fall into the same product category and some companies will be in more than one category.
My thoughts are that this could be done with 2 tables and have a comma separated id list in the product table which could be exploded, and refer to relevant matches in the company table
Product Table
product_id product_name company_id
1 product1 3,7,8,10,13
2 product2 1,7,5,2
Company Table
company_id company_name
3 company1
7 company2
8 company3
10 company4
However, I have a feeling that this could be very innefficient when the tables get bigger.
Is there a better way to do this?
