I need something like the user-roles of PG to store options of my users. I guess i need a table with roles, options and one that stores the refernces from roles to options.
so far is easy. Now I can let role1 have option1 and option2 ...
But I'd further like to let role2 inherit role1's options and also have option3. role_inherits_role (parent_role_fk, child_role_fk) 1, 2
What SELECT would deliver all options for role2 inkluding the inherited ones? like role_id, option_id 2, 1 2, 2 2, 3
select role_fk as role_id, option_fk as option_id from role_has_option where role_fk = 2 union select inh.child_role_fk, opt.option_fk from role_has_option opt join role_inherits_role inh on inh.parent_role_fk = opt.role_fk where inh.child_role_fk = 2