Re: Storing Users/Groups

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

Date: Tue, 1 Feb 2005 22:30:46 -0600

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
>
>
Loading