>
> As the SQL guru, how do I design a query with a recursive table
> (unlimited parent/child relationships). So that i.e. a group has a
> foreign key to the parent group. So that I can, in one query get all
> the parents of a specific group (a recursive join?)
If you mean that a group should have multiple parents, and each of
those parents should have multiple parents etc., you are going to end
up with a stored procedure (unless you have a database that implements
WITH ... RECURSIVE (Oracle does and there is an unofficial patch for
PostgreSQL, but in both cases it is named CONNECT BY ... PRIOR)).
If you mean that each group has one parent group, and each parent
group has one other parent group etc., google for nested set model.
Anyhow, I would probably set up permissions as a very large list of
rights that can only be true or false ("userInsertPriviledge",
"userUpdatePriviledge", "userDeletePriviledge", "updateSelfPriviledge"
etc.) and then use group membership as a template to assign these
permissions, but not as the authorative list of permissions. That way
it is easy to customize priviledges, and you can just query the
database for a list of assigned permissions and use those as roles in
you cfloginuser.
There is an article on DevNet (by Ben Forta?) that explains this in
more detail.
Jochem
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings]
