I need a clue on how to design a Many to Many relational database.

For example, I have a products table and a supplier table.

each product can be supplied by many suppliers
each supplier can supply many products

how can I orgranise this data in the tables?

I'm using MySQL.

The only way I can think of now is to create a third table and store the relationships ie.

'product_supplier' table with 2 fields: product_id, supplier_id

Any better ideas to share?

Thanks in advance!

9 Years
Discussion Span
Last Post by jakesee

After some searching, it appears that a third table is necessary.

I at first thought there could be some other way because in MS Access, you could specify a single field to take multiple values in design view.

But I'm convinced now that it must be using a third table in the background.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.