Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 6 of 6 total |
Need some help with database |
Fri, Sep 18 2009 10:17 PM | Permanent Link |
Eric | 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. |
Fri, Sep 18 2009 10:54 PM | Permanent Link |
Steve Forbes Team Elevate | 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. > |
Sat, Sep 19 2009 12:32 AM | Permanent Link |
"Raul" | 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 >> |
Sat, Sep 19 2009 9:14 AM | Permanent Link |
Steve Forbes Team Elevate | 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 > > |
Sat, Sep 19 2009 1:39 PM | Permanent Link |
Eric | Thanks for those answers.
|
Tue, Sep 22 2009 12:30 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |