Hi,

  I have a little problem and hope someone would be kind to help me with it.  I know the base of SQL but I am not a pro.

  I want to do the following:  I have a list of users and a list of groups.  I would like to be able to put users in groups (so far its easy) but also I would like
to be able to put groups in groups (here is my problem).  I dont know how to do that. I guess I need a table for the users and one for the groups, but how
can I put groups into groups?  Also a group can contain both users and groups.

Thanks.

Hi Eric,

This is a pretty common data storage requirement, and there are a couple
of ways to handle it ..

The first is to create two tables (GROUP and USER), and make the GROUP
table self-referencing i.e.

GROUP table

Id (AUTOINC) Primary Key
ParentGroup  Foreign Key into GROUP table
GroupName

e.g.

1 - NULL - Head Group
2 - 1 - Head Sub-Group 1
3 - 1 - Head Sub-Group 2
4 - 3 - Head Sub-Group 2 Sub-Group 1

etc. etc.

The USER table would contain a GroupId column which is a foreign key
into the GROUP table

The PROS for this method are that it is easy to use and maintain, the
CONS are that it is more difficult to construct comprehensive SQL using
this structure, and retrieval can be slower than the second method
(particularly if the heirarchy is deep).

--

The second method is the Nested Set Model, which is a bit complicated to
describe here, but the following URL contain a reasonable explanation
(http://www.sitepoint.com/print/hierarchical-data-database). This model
is a bit more difficult to maintain, but simplifies the interrogation of
heirarchical data, and the resulting queries are usually quite a bit
more efficient.

I hope this gives you some insight, good luck

Best regards

Steve

Eric wrote:
> Hi,
>
>    I have a little problem and hope someone would be kind to help me with it.  I know the base of SQL but I am not a pro.
>
>    I want to do the following:  I have a list of users and a list of groups.  I would like to be able to put users in groups (so far its easy) but also I would like
> to be able to put groups in groups (here is my problem).  I dont know how to do that. I guess I need a table for the users and one for the groups, but how
> can I put groups into groups?  Also a group can contain both users and groups.
>
> Thanks.
>

Nice explanations.

Depending on original requirements the group memberships might not be
strictly single path hierarchical : if same group can be member of multiple
parent groups then both methods might be limiting.

Using a 3rd table for group members (simply consiting of 2 groupID whereby
first ID is the group and 2nd is parent group) would .allow more flexibility
at the cost of (little more) complexity.

Similarly if user can be member of multiple groups a 4th table similar to
3rd might be useful as well - again 2 IDs, first is the userID and 2nd group
ID

Based on personal experience you may also have to now worry about recursion
whereby 2 groups could be both parent and member of each other or in case of
allowing more than 1 level of membership nesting (e.g. A is member of B, B
is member of C and C is member of A, etc) an infinite recusrion. if this is
desirable then SQL becomes very complex though in code resolving the
mmeberships is OK - just stop your resolution based on seeing same group
twice or once you've hit a "reasonable" recursion level for your app.

Raul


"Steve Forbes" <ozmosysspamfree@optusnet.com.au> wrote in message
news:4DD8BAD7-8140-4B1F-B545-89D75C9B7146@news.elevatesoft.com...
> Hi Eric,
>
> This is a pretty common data storage requirement, and there are a couple
> of ways to handle it ..
>
> The first is to create two tables (GROUP and USER), and make the GROUP
> table self-referencing i.e.
>
> GROUP table
>
> Id (AUTOINC) Primary Key
> ParentGroup  Foreign Key into GROUP table
> GroupName
>
> e.g.
>
> 1 - NULL - Head Group
> 2 - 1 - Head Sub-Group 1
> 3 - 1 - Head Sub-Group 2
> 4 - 3 - Head Sub-Group 2 Sub-Group 1
>
> etc. etc.
>
> The USER table would contain a GroupId column which is a foreign key into
> the GROUP table
>
> The PROS for this method are that it is easy to use and maintain, the CONS
> are that it is more difficult to construct comprehensive SQL using this
> structure, and retrieval can be slower than the second method
> (particularly if the heirarchy is deep).
>
> --
>
> The second method is the Nested Set Model, which is a bit complicated to
> describe here, but the following URL contain a reasonable explanation
> (http://www.sitepoint.com/print/hierarchical-data-database). This model is
> a bit more difficult to maintain, but simplifies the interrogation of
> heirarchical data, and the resulting queries are usually quite a bit more
> efficient.
>
> I hope this gives you some insight, good luck
>
> Best regards
>
> Steve
>>

Hi Raul,

You are perfectly correct, .. I wasn't thinking of many-to-many
relationships in my response. The additional tables you suggest would
certainly suit this scenario.

Best regards

Steve

Raul wrote:
> Nice explanations.
>
> Depending on original requirements the group memberships might not be
> strictly single path hierarchical : if same group can be member of multiple
> parent groups then both methods might be limiting.
>
> Using a 3rd table for group members (simply consiting of 2 groupID whereby
> first ID is the group and 2nd is parent group) would .allow more flexibility
> at the cost of (little more) complexity.
>
> Similarly if user can be member of multiple groups a 4th table similar to
> 3rd might be useful as well - again 2 IDs, first is the userID and 2nd group
> ID
>
> Based on personal experience you may also have to now worry about recursion
> whereby 2 groups could be both parent and member of each other or in case of
> allowing more than 1 level of membership nesting (e.g. A is member of B, B
> is member of C and C is member of A, etc) an infinite recusrion. if this is
> desirable then SQL becomes very complex though in code resolving the
> mmeberships is OK - just stop your resolution based on seeing same group
> twice or once you've hit a "reasonable" recursion level for your app.
>
> Raul
>
>
> "Steve Forbes" <ozmosysspamfree@optusnet.com.au> wrote in message
> news:4DD8BAD7-8140-4B1F-B545-89D75C9B7146@news.elevatesoft.com...
>> Hi Eric,
>>
>> This is a pretty common data storage requirement, and there are a couple
>> of ways to handle it ..
>>
>> The first is to create two tables (GROUP and USER), and make the GROUP
>> table self-referencing i.e.
>>
>> GROUP table
>>
>> Id (AUTOINC) Primary Key
>> ParentGroup  Foreign Key into GROUP table
>> GroupName
>>
>> e.g.
>>
>> 1 - NULL - Head Group
>> 2 - 1 - Head Sub-Group 1
>> 3 - 1 - Head Sub-Group 2
>> 4 - 3 - Head Sub-Group 2 Sub-Group 1
>>
>> etc. etc.
>>
>> The USER table would contain a GroupId column which is a foreign key into
>> the GROUP table
>>
>> The PROS for this method are that it is easy to use and maintain, the CONS
>> are that it is more difficult to construct comprehensive SQL using this
>> structure, and retrieval can be slower than the second method
>> (particularly if the heirarchy is deep).
>>
>> --
>>
>> The second method is the Nested Set Model, which is a bit complicated to
>> describe here, but the following URL contain a reasonable explanation
>> (http://www.sitepoint.com/print/hierarchical-data-database). This model is
>> a bit more difficult to maintain, but simplifies the interrogation of
>> heirarchical data, and the resulting queries are usually quite a bit more
>> efficient.
>>
>> I hope this gives you some insight, good luck
>>
>> Best regards
>>
>> Steve
>
>

Thanks for those answers.

Eric,

<< I want to do the following:  I have a list of users and a list of groups.
I would like to be able to put users in groups (so far its easy) but also I
would like to be able to put groups in groups (here is my problem).  I dont
know how to do that. I guess I need a table for the users and one for the
groups, but how can I put groups into groups?  Also a group can contain both
users and groups. >>

If it's any help, what they do in the SQL standard is treat both users and
groups (roles) as "authorizations", and simply enforce that only groups can
contain other groups.  So you could, theoretically, get away with one table
that includes both and has a Type column that specifies the type of
authorization.  Then, just define BEFORE INSERT and BEFORE UPDATE triggers
for the table that check to make sure that any insert or update that is
specifying a parent authorization refers to a valid group and not a user.

--
Tim Young
Elevate Software
www.elevatesoft.com