Re: How to cascade information like the user roles ?

Filip Rembiałkowski schrieb:
>
>
> 2010/1/19 Andreas <maps(dot)on(at)gmx(dot)net <mailto:maps(dot)on(at)gmx(dot)net>>
>
> Hi,
>
> 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.
>
> roles (role_id, role_name)
> option (option_id, option_name)
> role_has_option (role_fk, option_fk)
>
> 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
Thanks.
I am looking for a more general solution that expands even multiple
steps of inheritance like a more complex example:
role_1 --> option_1 + option_2
role_2 --> option_3 and inherits role_1
role_3 --> option_2 + option_4

role_4 --> option_5 and inherits role_2 and role_3

I need a general solution that gives all options for any given role
including every inherited options over a unlimited hierarchy of parents.
Sounds complex, I know, but this is what PG does with its user-roles.
So I'd do in this example a SELECT ... WHERE role_id = 4
and get
4, 5 directly
4, 3 from role_2
4, 1 from role_1 over role_2
4, 2 from role_1 over role_2
4, 2 from role_3 (inherited double occurance)
4, 4 from role_4