This particular example is really a hierarchy, and does not lend itself well to unknown depths. What if the company grows, and you need to add another layer of management? You’d have to go in and change all your queries that access the hierarchy.

I’ve been struggling with this problem for goat breeding. In this case, you have two hierarchies — one for each animal’s sire (father) and one for the dam (mother). I have found no practical solution in SQL. Although my dataset is small enough that performing multiple queries using recursion in a procedural language like PHP is possible, it grates on my aesthetic sense.

One possible solution I’ve been playing with is the OQGRAPH storage engine, which can be used as a plugin for MariaDB or MySQL 5.5. It isn’t really a storage engine per se, but rather, a computational engine, specialized for graph tracing.

In operation, OQGRAPH has exactly one allowable schema, which can be used any number of times (with different table names, of course):

[code]
CREATE TABLE db.tblname (
    latch   SMALLINT  UNSIGNED NULL,
    origid  BIGINT    UNSIGNED NULL,
    destid  BIGINT    UNSIGNED NULL,
    weight  DOUBLE    NULL,
    seq     BIGINT    UNSIGNED NULL,
    linkid  BIGINT    UNSIGNED NULL,
    KEY (latch, origid, destid) USING HASH,
    KEY (latch, destid, origid) USING HASH
  ) ENGINE=OQGRAPH;
[/code]

In graph theory terms, each OQGRAPH row describes an edge in a directed graph, with “origid” and “destid” being the primary keys of connected nodes. You populate this schema using INSERT… SELECT on the table with the self-join relationship, then query the OQGRAPH table by joining it back to your original table.

They have an example based on the Tree Of Life, where you can (for example), show the taxonomic path between humans and a banana. It seems to be lightning-fast, with no arbitrary depth-of-query limit. But I have not been able to bend OQGraph to my will in solving my rather simple goat breeding problem.

LornaJane, have you any experience with OQGRAPH for such things?