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?
