How do I structure a bunch of items that chain together in a MySQL database?
Question by HartleySan
I apologize for the vagueness of my question title, but I don’t even know what to call what I’m trying to accomplish.
The best way to describe what I want is that I want to be able to chain a bunch of items together, and then (possibly) recursively find all the items that are part of any chains that contain the target item. For example, note item3 in the following chains:
item1 => item2 => item3 => item4
item5 => item3 => item6
item3 => item7 => item8
item3 => item9 => item10
item11 => item12 => item13 => item3If a user were to do a search for item3, then I’d want all five chains above to be displayed. In other words, I want to be able to find all descendants and ancestors of item3, so that I can display the data in an HTML table (or whatever HTML structure works best).
The thing that makes this tricky is that (as shown above) any given item may have many descendants and many ancestors. As such, I’m not sure that regular recursion in MySQL would work.
I did have a look at both of the articles linked to in the top answer for the following SO thread, but I don’t think that the suggested solutions will work for my desired data structure:
Mysql recursion?
Is there any way to structure this kind of data into a MySQL DB so that with fairly easy and lightweight queries (i.e., hopefully one query per item request), I can get the information and structure I’m looking for?
Thank you very much.
Answer by Starx
I have a suggestion.
Store item in the following structure.
+---------+-----------+
| id | item |
+---------+-----------+
| 1 | item3 |
+---------+-----------+
And add the link references in the following
+---------+-----------+------------+
| itemid | ancestor | descendant |
+---------+-----------+------------+
| 1 | 3 | 2 |
+---------+-----------+------------+
| 1 | 5 | 7 |
+---------+-----------+------------+
Create a index on all three columns. This will enable you to add same time as many times as it appears on a chain.
Also you can query a particular item to find all its related links.
