in reply to storing tree-like structures in tables
antonio -> <students>, <football players>at the next step
antonio -> <students>, <football players>, <people>(you only consider <people> once), and finally
antonio -> <students>, <football players>, <people>, <universal>
users: ------------------------------ | ID | name | fields... | ------------------------------ | 1 | antonio | | ... |________________|___________| groups: ------------------------------------- | ID | name | fields... | ------------------------------------- | 1 | students | | | 2 | football players | | | 3 | people | | | 4 | universal | | ... |____|__________________|___________| user_group_rel (user belongs to group) ---------------------- | ID_user | ID_group | ---------------------- | 1 | 1 | | 1 | 2 | ... |_________|__________| group_group_rel (group inherits from group) ------------------------------- | ID1 | ID2 ( parent group ) | ------------------------------- | 1 | 1 | | 1 | 3 | | 1 | 4 | | 2 | 2 | | 2 | 3 | | 2 | 4 | | 3 | 3 | | 3 | 4 | ... |______|______________________|Note that the group-group relationship contains all rows (n,n) and all possible group inclusions, including derived ones (e.g. <students>-><universal>).
SELECT UNIQUE group_group_rel.ID2 FROM group_group_rel, user_group_rel WHERE user_group_rel.ID_user=? AND user_group_rel.ID_group=group_group_rel.ID1If you want to keep track of what group inclusions are given and what are derived, you could add a boolean column in the group_group_rel table.
|
---|