Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
add comment

1 Answer

up vote 0 down vote accepted

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

CAVEAT

share|improve this answer
    
Heh, looks like I've got a bit of reading to do. Much appreciated, looks like this should work for my purpose. –  Anton Sarukhanov May 1 '13 at 13:40
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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