Mutual Many to Many relationship

[jakesee]

Hi,

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!

[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.