Hierarchical Storage and Query

Question

I am editing and providing new info to this question because i've found what I need, just have a couple of questions about efficiency.

Now, I have this table:

category
--------------------------
category_id   name         parent
1           Phillips       (null)
2           Barnes          1
3           Moore           2
4           de Mohrenschildt   3
5           Oswald          9
6           Hunt            2
7           Burnham         6
8           Buckley         7
9           Paine           4
10          Sturgis         6
11          Hunt            3

I have this query:

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Phillips'
ORDER BY lev2

that returns:

lev1        lev2    lev3    lev4         lev5      lev6
Phillips    Barnes  Hunt    Sturgis      (null)    (null)
Phillips    Barnes  Moore   de Mohr'ldt  Paine     Oswald
Phillips    Barnes  Hunt    Burnham      Buckley   (null)

and, for a new "node":

SELECT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4,
  t5.name AS lev5,
  t6.name AS lev6
FROM category t1
  LEFT JOIN category t2
    ON t2.parent = t1.category_id
  LEFT JOIN category t3
    ON t3.parent = t2.category_id
  LEFT JOIN category t4
    ON t4.parent = t3.category_id
  LEFT JOIN category t5
    ON t5.parent = t4.category_id
  LEFT JOIN category t6
    ON t6.parent = t5.category_id
WHERE t1.name = 'Moore'
ORDER BY lev2;

which returns

lev1    lev2        lev3    lev4    lev5    lev6
Moore   Hunt        (null)  (null)  (null)  (null)
Moore   de Mohr'dt  Paine   Oswald  (null)  (null)

Which, as you can see, is awfully close to this, in form, with some html:

• Phillips supervised:
  • Barnes supervised:
    • Moore supervised
      • de Mohrenschildt knew:
        • Oswald ...
    • Sturgis knew
      • Hunt knew
        • Moore worked for...

And can hopefully mature into this one day (what's below the tabs in the image)...:

I have a couple of questions about this table and query.

1) as can be seen in the name "Hunt", the problem I may have is that when someone is assigned to more than one parent, I'm going to have to enter a new record for him. And as there are going to be, I'm guessing 5 or 600 unique persons, to repeat many of them seems to me to be unnecessary. Can someone see a better way to handle this issue, or is it even an issue? I just don't like redundancy...

2) along these lines, if you look at the image, you'll see that some of the names are in RED, denoting an AJAX dropdown paragraph of pertinent information regarding that immediate relationship. So "Hunt" may have a paragraph or two under this particular branch, and another on another branch somewhere else. I'm thinking maybe the need to repeat "Hunt" wherever necessary in the table would allow for calling another set of data to fill the AJAX dropdown. Am I asking for trouble with what I have here so far...?

3) since there will be so many branches starting with different names, is this query efficient enough if I have to run the query perhaps 40 or 50 times on one, (of many) pages, especially if it's calling some extra data as mentioned above?

I like this "schema," if that's the right word, because it seems to be really extensible. This database has the potential to grow indefinitely.

I really appreciate anyone's help with this. I'm so happy I've finally found something that's working for me (I never even knew what an Hierarchical DB is until last night, and that that's what I was looking for...)

Answer 1

Since the depth is variable and an item can have multiple parents, I recommend a general "relationship", not a parent-child hierarchy.

See this for the optimal way to write a many-to-many table.

Then you need to write multiple queries, not a single, fixed-depth, query. There is no "good" way to traverse a tree/DAG/web (until 8.0, with CTEs). And the speed of multiple queries, with suitable indexes and suitable caching, should be quite adequate.