Get data from two tables with recursive relationships

Question

I have a MySQL 5.1 DB, being used behind a PHP application to track permissions and other items that need to be issued to staff that hold various positions within an org. To track what privileges a position has, I'm using this table:

mysql> desc position_privs;
+--------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------+---------+------+-----+---------+-------+
| position | int(11) | NO | | NULL | |
| priv | int(11) | NO | | NULL | |
| date_added | int(11) | NO | | 0 | |
| date_removed | int(11) | YES | | NULL | |
+--------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

position refers to an id column in a positions table, and priv refers to an id column in a privs table. Some positions also inherit privileges from other positions, which may in turn inherit from yet another position, etc. I have this table for that:

mysql> desc position_inherits;
+--------------------+---------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+--------------------+---------+------+-----+---------+-------+
| position | int(11) | NO | | NULL | |
| inherit_privs_from | int(11) | NO | | NULL | |
| date_added | int(11) | NO | | 0 | |
| date_removed | int(11) | YES | | NULL | |
+--------------------+---------+------+-----+---------+-------+
4 rows in set (0.00 sec)

If the inheritances were always only one level deep, I could do this with a simple JOIN, but I'm stumped as to handling positions that inherit from a position, which in turn inherits from another position.

Is there a convenient way of doing this using MySQL, or should I just stick to SELECT * FROM $table and doing it in the application code instead?

Answer 1

BAD NEWS

MySQL does not support recursive data or table structures. Neither does it support recursive SQL.

GOOD NEWS

There is a hope. I have written some stored functions on how to retrieve hierarchies of data

• Oct 24, 2011 : Find highest level of a hierarchical field: with vs without CTEs
• Dec 10, 2012 : MySQL: Tree-Hierarchical query

CAVEAT

• You would have to implement this against your tables.
• You also need to make sure you are entering data as a Tree and not as a Graph.
  • https://en.wikipedia.org/wiki/Tree_(data_structure)
  • http://wiki.answers.com/Q/DIFFERENCE_between_GRAPH_and_TREE_IN_DATA_STRUCTURES
  • https://stackoverflow.com/a/7707983/491757
• For any row in the position_inherits table that does not have a parent, simply set that row's inherit_privs_from to zero(0). That way, you have establish root positions.

• Anton Sarukhanov: Heh, looks like I've got a bit of reading to do. Much appreciated, looks like this should work for my purpose.