3

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)...:

sample of my outline

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...)

  • 1
    Ah, so this is essentially a hierarchy, and the depth is not fixed, too, it appears. Not sure if there's any way to process a hierarchy like that with a single statement in MySQL. Other SQL products support recursive queries, which is what would really be suitable here. But perhaps MySQL's got other tricks up its sleeve (something using variables, perhaps) to help cases like this. I've taken the liberty to add two tags to your question to help to provide better context. If you disagree with either's relevance, please feel free to remove it. – Andriy M Sep 25 '16 at 6:55
  • yes! in fact, i've just found this last night » explainextended.com/2009/03/17/hierarchical-queries-in-mysql » and am trying to figure out how to incorporate it for my needs. My table layouts are not at all set in stone. I'm going to add this Query to the bottom of my Question so it can be read - perhaps you can take a peek and offer a suggestion? as i said, my table construction is fully flexible... – glenn nall Sep 25 '16 at 10:27
  • @AndriyM, I've found a nice hierarchical table and query that seems pretty good - i"ve edited my initial Q again to reflect what I have going on. Just a couple of questions i've written if you wouldn't mind taking a look... very grateful for your time. – glenn nall Sep 25 '16 at 17:01
  • As for how to assign more than one parent to a row and avoid data duplication, I think you'll need a dedicated linking table. Linking tables are commonly used between different entities but it's perfectly fine to have one for the same entity to link any two rows as if they were different objects (including cases like yours where a row needs to be assigned to multiple parents). I'll take a look at other points but I can already say that your question's grown a bit too broad, since it's now essentially many distinct questions, even if related. – Andriy M Sep 25 '16 at 17:51
  • OK, I appreciate it. I didn't mean to get so spread out. All I'm really asking is, is it your opinion that stacking all the names in one table with duplicates is a bad idea that will cause problems in the future, and is that query ok to run up to 30 times on a page...? That's all I really need to know. Thanks again so much. – glenn nall Sep 25 '16 at 20:08
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.