Re: Storing Users/Groups

From: Louis Davidson (dr_dontspamme_sql_at_hotmail.com)
Date: 02/02/05 

Date: Wed, 2 Feb 2005 10:46:12 -0600

"Raterus" <raterus@hotmail.com> wrote in message
news:ePBzDYTCFHA.1836@tk2msftngp13.phx.gbl...
>You are awesome! Was this a problem you took a personal interest in, or
>did you just really, really want to help me out!

You should never ask questions like that. It was a little of both, of
course. This is the kind of thing I like to do, and don't get to do enough
of it. 

-- 
----------------------------------------------------------------------------
Louis Davidson - drsql@hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design - 
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in 
consulting services.  All other replies may be ignored :)
"Raterus" <raterus@hotmail.com> wrote in message 
news:ePBzDYTCFHA.1836@tk2msftngp13.phx.gbl...
You are awesome!  Was this a problem you took a personal interest in, or did 
you just really, really want to help me out!  Either way, this was exactly 
what I needed and I pray you will receive blessings from above for your 
work!
I understand what you are saying as far as performance goes, and how a 
separate table of user/website keys would be best for performance.  I'm 
likely to do that, since these groups aren't likely to change much, if ever, 
after being set up.
This will also help me as far as designing a framework around this so my 
websites (in asp.net) can interface with this setup.  In production, when I 
authenticate users, all I'm really interested in is the relation between 
website_key and user_key.  I have to keep all the "group" tables for 
creating this final relation, but as far as what I actually need to program 
around, I can just look at this generated table.
I'm sure I'll see more benefits to this as I get deeper into implementation, 
but for now, let me just thank you again, you are a blessing!
--Michael
"Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message 
news:OE$M5$NCFHA.1084@tk2msftngp13.phx.gbl...
> OK.  Here it is.  I think I got it (spent my entire night on it, but I 
> think
> I got it.)  I wrote a function to go through and flatten the total 
> heirarchy
> (I leave it to you to make sure you don't have any Appalachian Heirarchies
> in there (my ancestor is also my child.)   What I would do (if I was going
> to implement this code, unless someone gives you a better solution)is 
> build
> a physical table to store  web_app_key and user_key and rebuild using a
> trigger when groups change, or have a button to press/nightly job to 
> update
> this table.  You might be able to improve performance, but that would be 
> the
> fastest for the user.
>
> I left out the table creation, I just used your new table.  First the
> function.  If you need any explanation, let me know and I will do my best 
> to
> explain it:
>
> drop function dbo.group_groups$flatten
> go
> create function dbo.group_groups$flatten()
> returns @outtable table (root_node int, group_key int, primary key
> (root_node, group_key))
> as
> begin
>
> declare @groupHeirarchy table (group_key_in int, group_key int, root_node
> int, level int)
> DECLARE @treeLevel as int
> SET     @treelevel = 1
>
>
> --since we are flattening everything, we include everything as root nodes.
> Note, this might
> --cause a really big set to be returned, so be careful
> insert into @groupHeirarchy
> select  group_key_in, group_key, group_key_in, @treeLevel
> from  group_groups
>
>
>  WHILE (1 = 1) --imitates do...until construct
>   BEGIN
>
>   INSERT INTO @groupHeirarchy
>   SELECT groups.group_key_in, groups.group_key, root_node,  level + 1 as
> level
>   FROM  dbo.group_groups as groups
>     JOIN @groupHeirarchy as gh
>      ON groups.group_key_in = gh.group_key
>   --this where isolates a given level of the tree
>   WHERE EXISTS( SELECT  *
>      FROM   @groupHeirarchy AS holdTree
>      WHERE   level = @treelevel
>         AND   groups.group_key_in = holdtree.group_key)
>
>  IF @@rowcount = 0
>     BEGIN
>     BREAK
>     END
>   SET @treelevel = @treelevel + 1
>   END
> insert into @outtable (root_node, group_key)
> select distinct root_node, group_key from @groupHeirarchy
> return
> end
> go
>
>
> set nocount on
>
> insert into website_app values (10)
>
> insert into [user] values (66)
> insert into [user] values (77)
> insert into [user] values (88)
> insert into [user] values (99)
>
> insert into website_user (user_key,website_app_key) values (88,10)
>
> insert into [group] (group_key) values (20)
> insert into group_user values (20,99)
> insert into group_user values (20,77)
>
>
> insert into [group] (group_key) values (30)
> insert into group_user values (30,66)
> insert into group_user values (30,77)
>
> insert into [group] (group_key) values (90)
> insert into group_user values (90,88)
>
> insert into [group] values (50)
> insert into [group] values (40)
>
> --40 members are members of 50
> insert into group_groups (group_key, group_key_in) values (40,50)
>
> --30 members are members of 40
> insert into group_groups (group_key, group_key_in) values (30,40)
>
> --30 members are members of 40
> insert into group_groups (group_key, group_key_in) values (90,30)
>
> --20 members are members of 50
> insert into group_groups (group_key, group_key_in) values (20,50)
>
> --website 10 gets group 50
> insert into website_group (group_key, website_app_key) values (50,10)
>
> go
> select 'this set can get ugly for big sets'
> select * from dbo.group_groups$flatten()
>
> select  website_app.website_app_key, [user].user_key
> from  website_app
>    join website_group
>   on website_group.website_app_key = website_app.website_app_key
>    join [group]
>   on [group].group_key = website_group.group_key
>    join ( select  group_key as root_node, group_key
>  from  [group]
>  union all
>  select  root_node, group_key
>  from  dbo.group_groups$flatten() ) as flatGroups
>  on [group].group_key = flatGroups.root_node
>    join group_user
>   on flatGroups.group_key = group_user.group_key
>    join [user]
>   on [user].user_key = group_user.user_key
> union
> select  website_app.website_app_key, [user].user_key
> from  website_app
>   join website_user
>  on website_app.website_app_key = website_user.website_app_key
>   join [user]
>  on  [user].user_key = website_user.user_key
> go
>
>
> -- 
> ----------------------------------------------------------------------------
> Louis Davidson - drsql@hotmail.com
> SQL Server MVP
>
> Compass Technology Management - www.compass.net
> Pro SQL Server 2000 Database Design -
> http://www.apress.com/book/bookDisplay.html?bID=266
> Note: Please reply to the newsgroups only unless you are interested in
> consulting services.  All other replies may be ignored :)
>
> "Raterus" <raterus@hotmail.com> wrote in message
> news:OGd52dICFHA.3444@TK2MSFTNGP14.phx.gbl...
> Wow, Thank you very much!  I didn't expect anyone to spell it out so
> clearly!  I can see exactly what you are doing, so that isn't a problem.
> One thing though that I don't see is how I can add groups to another 
> group.
> To me, It seems as easy as adding an additional table..
>
> CREATE TABLE group_groups (
>      group_key            int NOT NULL,
>      group_key_in             int NOT NULL,
>      PRIMARY KEY (group_key, group_key_in),
>      FOREIGN KEY (group_key_in) REFERENCES [group]  (group_key),
>      FOREIGN KEY (group_key) REFERENCES [group]  (group_key)
> )
>
> Is it still possible to query all users of the websites with this 
> additional
> functionality?
> Thank you again for what you've done,
> --Michael
>
> "Louis Davidson" <dr_dontspamme_sql@hotmail.com> wrote in message
> news:u9JeWLICFHA.1400@TK2MSFTNGP11.phx.gbl...
>> Something like this should work.  Take a look and see what you think.  Do
>> you have a data modeling tool?  I just implemented keys, no data (to save
>> me
>> time) but it should be pretty clear what I am doing.
>>
>>
>> CREATE TABLE [group] (
>>       group_key            int NOT NULL primary key
>> )
>> go
>>
>> CREATE TABLE [user] (
>>       user_key             int NOT NULL primary key
>> )
>> go
>>
>> CREATE TABLE group_user (
>>       group_key            int NOT NULL,
>>       user_key             int NOT NULL,
>>       PRIMARY KEY (group_key, user_key),
>>       FOREIGN KEY (user_key) REFERENCES [user]  (user_key),
>>       FOREIGN KEY (group_key) REFERENCES [group]  (group_key)
>> )
>> go
>>
>> CREATE TABLE website_app (
>>       website_app_key      int NOT NULL primary key
>> )
>> go
>>
>> CREATE TABLE website_group (
>>       group_key            int NOT NULL,
>>       website_app_key      int NOT NULL,
>>       PRIMARY KEY (group_key, website_app_key),
>> FOREIGN KEY (website_app_key) REFERENCES website_app  (website_app_key),
>> FOREIGN KEY (group_key) REFERENCES [group]  (group_key)
>> )
>> go
>>
>> CREATE TABLE website_user (
>>       user_key             int NOT NULL,
>>       website_app_key      int NOT NULL,
>> PRIMARY KEY (user_key, website_app_key),
>> FOREIGN KEY (website_app_key) REFERENCES website_app  (website_app_key),
>> FOREIGN KEY (user_key) REFERENCES [user]  (user_key)
>> )
>> go
>>
>>
>> insert into website_app values (1)
>> insert into website_app values (2)
>>
>> insert into [user] values (66)
>> insert into [user] values (77)
>> insert into [user] values (88)
>> insert into [user] values (99)
>> insert into website_user (user_key,website_app_key) values (88,1)
>>
>> insert into [group] values (1)
>> insert into group_user values (1,99)
>> insert into group_user values (1,77)
>> insert into website_group (group_key, website_app_key) values (1,1)
>>
>> insert into [group] values (2)
>> insert into group_user values (2,66)
>> insert into group_user values (2,77)
>> insert into website_group (group_key, website_app_key) values (2,1)
>>
>> insert into [group] values (3)
>> insert into group_user values (3,66)
>> insert into group_user values (3,99)
>> insert into website_group (group_key, website_app_key) values (3,2)
>>
>> go
>> select  website_app.website_app_key, [user].user_key
>> from  website_app
>>    join website_group
>>  on website_group.website_app_key = website_app.website_app_key
>>    join [group]
>>  on [group].group_key = website_group.group_key
>>    join group_user
>>  on [group].group_key = group_user.group_key
>>    join [user]
>>  on [user].user_key = group_user.user_key
>> union
>> select  website_app.website_app_key, [user].user_key
>> from  website_app
>>   join website_user
>>  on website_app.website_app_key = website_user.website_app_key
>>   join [user]
>>  on  [user].user_key = website_user.user_key
>>
>>
>> -- 
>> ----------------------------------------------------------------------------
>> Louis Davidson - drsql@hotmail.com
>> SQL Server MVP
>>
>> Compass Technology Management - www.compass.net
>> Pro SQL Server 2000 Database Design -
>> http://www.apress.com/book/bookDisplay.html?bID=266
>> Note: Please reply to the newsgroups only unless you are interested in
>> consulting services.  All other replies may be ignored :)
>>
>> "Raterus" <raterus@hotmail.com> wrote in message
>> news:eaJqPjHCFHA.4004@tk2msftngp13.phx.gbl...
>> Hello,
>>
>> Here is my problem, I maintain many websites for my company, each have
>> their
>> own set of users.  Sometimes, one user needs to be able to access 
>> multiple
>> websites (For example...me!).  I'm attempting to come up with a table
>> structure that will allow for users and groups which I then can assign to
>> a
>> particular website.  All the website names are stored in a table
>> "WebApps",
>> I need to relate the key in that table to a structure that allows the
>> following:
>>
>> I'll have users, also I'll have groups.  Groups can contain users, and/or
>> they can contain other groups.
>>
>> Any ideas on the best way to represent this in sql server?  Also (and 
>> this
>> may be asking too much), but it would be really nice to have a select
>> query
>> to see all the users who have access to a particular website.
>>
>> Thank you,
>> --Michael
>>
>>
>
>