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!

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.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.