Hi every one
This would be a very nice feature in future.
However, the current database structure of K2 and Joomla does not allow for a many-to-many relationship to exist. It is inherently a restriction of MySQL and many other relational databases. (The primary key has to be unique as it is set to auto-increment and it is indexed)
So if one examines the database structure of K2, a part of it looks like this:
I have added in MySQL relationships to illustrate a part of the structure.
Now let us examine the problem area in a bit more detail:
At point A in the diagram, you will see the one-to-many relationship between the k2_categories table and the k2_items table. A simple on-to-many relationship between the primary key of k2_categories and the foreign key (catid) in k2_items.
A possible way to apply the multiple categories to multiple items relationship, is to establish a "property relationship" table as I indicated in the diagram with the K2_cat_item_prop table. It has its own unique primary key and through two independent foreign keys (fki_category_ID and fki_item_ID), it establishes an indirect many-to-many relationship through the relations identified by B an C. You can then thereafter, delete the original relationship and just ignore the foreign key in the k2_items table, if you so wish.
There is one more trick to employ, and that is to create a trigger in both the k2_categories and k2_items tables, that fire an INSERT (new record) into the K2_cat_item_prop table, every time a new record is inserted in either the k2_categories or k2_items tables. This trigger then updates the fki_category_ID with the value of the new ID (primary key) from the k2_categories table. The same will apply for the k2_items table. The key reason for employing triggers here, is to enforce the relational integrity between the three tables.
I am an absolute new boarder in php, so I would not know where to start, but I do have quite some experience with databases and SQL. So if anyone out there is willing to pick up the php side of this, I will gladly contribute where I can on the SQL side. (By the way, I have used this methodology in many instances before, and it works great and allows for some stunning other features that flow from this, but which will be outside of the scope of this topic)
With J1.6 coming soon, maybe even the Joomla team can use this methodology and I'm sure that the team at K2 will find a way to make it work.
I hope this can help
Joe Stokes