According to this article Managing hierarchical data in mysql the most suitable solution for holding regular hierarchical data is a Nested Set Model, which i totaly like, but unfortunately my task is slightly more difficult. I need to manage a hierarchical model where some of the sub-categories may have multiple parents, something like crossing sets. Similiar problems described here.
To be exact i have some structure of categories in which each item can belong to multiple categories (and also on my way i'll need to provide some mean of category inheritance, if item belongs to TVs then it also belongs to Home_electronics, so the regular tag-cloud won't do here).
tl;dr: need a simple way / approach (maybe complex in realization, but simple in managment, like delete, add and find path) to manage model of categories with M:M relations.
Sadly i'm limited to MySQL only, but if this task can't be solved with SQL only, i'll move on to implementing this functionality in PHP (and thus i'll be glad to hear any out-of-the-box solutions of this problem - libraries or just sources, but thats for the worst case scenario).
Looks like the thing i'm looking for is named Directed Acyclic Graph (which was pretty obvious but i was probably too dumb to think about it :)).
It'll be good to see some implementation of it which has good managability.
And by the way, regular ID, ParentID, Data thing is not an option because MySQL doesn't have recursion and thus can't retrieve data by one query (well it can if you make PHP create a query with 1000 JOINs and pass it to MySQL, but thats retarded).
PS: Using only MySQL is not my decision, it's simply given, i know that any NoSQL DBMS would be more suitable.
