Hello
I am confronted with the many to many relationship, namely Products and Categories in that
One product can belong to one or More categories
and
One category can have one or more products.
So yes, the approach that I choose is that of creating an Associative table by
first assigning the id for that table plus the primary keys of the former above named tables as FK.
However, I am not quite satisfied with how I have named the associative table because as you know, I have then to verify that I made the right choice by testing whether I have now a 1 to Many relationship between Products and the new table and Categories and the new table.
I chose to call it: assignment table as
One product can have many assignments
One category can have many products assigned.
But I dont like it much.
Aside from that, I will still have to improve it by advanced benchmarking by:
making an EXPLAIN and seeing how the query is performed as shown here
http://www.joinfu.com/2005/12/managing-many-to-many-relationships-in-mysql-part-1/
but so far I would be happy if I can get a proper associative table.
(for other cases I can see more easily the creation of an associative table, but not quite with products and categories)
Thank you.
Regards