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?
Best Answer
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 CTEsDec 10, 2012
: MySQL: Tree-Hierarchical queryCAVEAT
position_inherits
table that does not have a parent, simply set that row'sinherit_privs_from
to zero(0). That way, you have establish root positions.