sql server programming: I have a user assigned multiple roles and a role can be inherited from
multiple parents (see below). How do I answer such questions as "How
many roles does the user belongs to?" (no re-enter allowed and no
deep level restriction)

I answered the above questions by using .NET but I think it can be
more efficient by using just SQL. I would appreciate if you can give
me an answer.

Thank you.


CREATE TABLE [dbo].[tb_User] (
[Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Name] nvarchar(99) NOT NULL UNIQUE,
[Password] nvarchar(99) NOT NULL,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_Role] (
[Id] [int] IDENTITY (1, 1) NOT NULL PRIMARY KEY,
[Name] nvarchar(99) NOT NULL UNIQUE,
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_User_Role] (
[UserId] [int] NOT NULL ,
[RoleId] [int] NOT NULL,
) ON [PRIMARY]
GO


ALTER TABLE [dbo].[tb_User_Role] WITH NOCHECK ADD
CONSTRAINT [PK_tb_User_Role] PRIMARY KEY CLUSTERED
(
[UserId],
[RoleId]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tb_Parent_Role] (
[RoleId] [int] NOT NULL ,
[ParentRoleId] [int] NOT NULL ,
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tb_Parent_Role] WITH NOCHECK ADD
CONSTRAINT [PK_tb_Parent_Role] PRIMARY KEY CLUSTERED
(
[RoleId],
[ParentRoleId]
) ON [PRIMARY]

John
Can you post sample data + expected result ?




[quoted text, click to view]

[quoted text, click to view]

Sql Server has no very efficient way to do this. In order to decide "is
user X in role Y", you have to get a list of role Y and all of its parent
roles, then join that to the membership table and see if user X is in any of
those roles. Expanding the role hierarchy is expensive and requires
multiple queries and SQL recursion.

This is going to be so expensive that you will want to calculate all of a
users roles just once and save the result on the client side. But once you
go that far, you might as well just calculate the role memberships on the
client side.

David

[quoted text, click to view]
from multiple parents (see below). How do I answer such questions as
"How many roles does the user belong to?" (no re-enter allowed and no
deep level restriction) <<

Please read ISO-11179 so your data element names will make sense. The
prefix that tells us a table is a table is redundant and useless.

Also, in what world are all strings NVARCHAR(99)? How much research did
you do at the Post Office, ISO, systems manuals, etc. for your data
elements? How many weeks did you spend looking for any standards or for
reasonable limits and datatypes to preserve your data integrity? What
system has passwords longer than CHAR(12)? Etc.

CREATE TABLE Users
(user_id INTEGER NOT NULL PRIMARY KEY,
name NVARCHAR(35) NOT NULL, -- foreign names allowed
password CHAR(12) NOT NULL);

CREATE TABLE Roles
(role_id INTEGER NOT NULL PRIMARY KEY,
role_name NVARCHAR(35) NOT NULL);

CREATE TABLE UsersRoles
(user_id INTEGER NOT NULL
REFERENCES Users (user_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
role_id INTEGER NOT NULL
REFERENCES Roles (role_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
PRIMARY KEY (user_id, role_id));

Now this is cleaned up, what are your rules? Trees do not allow
multiple parents and a path enumeration or nested sets model would give
us the entire path in one query. So, what about cycles? Is this a
lattice or a general graph?

What you might wind up with is a table that covers all the levels from a
given role thru all its ancestors. Ugly, but very quick and fairly easy
to maintain once built.

CREATE TABLE Role_Inheritance
(role_id INTEGER NOT NULL
REFERENCES Roles (role_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
ancestor_role_id INTEGER NOT NULL
REFERENCES Roles (role_id)
ON UPDATE CASCADE
ON DELETE CASCADE,
level INTEGER NOT NULL CHECK (level >= 0),
<< horrible check constraints for cycles and other rules >>,
PRIMARY KEY (role_id, ancestor_role_id));

--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.

*** Sent via Developersdex http://www.developersdex.com ***