Expanding Hierachy with multiple parents

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?"

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]
GO"John Smith" <hai_hoang@.hotmail.com> wrote in message
news:5661eadb.0311292155.4231580@.posting.google.co m...
> 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?"
> 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]
> GO

CREATE TABLE Users
(
user_name VARCHAR(35) NOT NULL PRIMARY KEY,
password VARCHAR(10) NOT NULL
)

CREATE TABLE Roles
(
role_name VARCHAR(20) NOT NULL PRIMARY KEY
)

CREATE TABLE ParentRoles
(
parent_role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name),
role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name),
CHECK (parent_role_name <> role_name),
PRIMARY KEY (role_name, parent_role_name)
)

CREATE TABLE UserRoles
(
user_name VARCHAR(35) NOT NULL REFERENCES Users (user_name),
role_name VARCHAR(20) NOT NULL REFERENCES Roles (role_name)
PRIMARY KEY (user_name, role_name)
)

-- UDF to return all roles for each user
-- A user can be directly assigned multiple roles and each role can have
-- multiple parents
CREATE FUNCTION AllUserRoles()
RETURNS @.roles TABLE
(user_name VARCHAR(35) NOT NULL,
role_name VARCHAR(20) NOT NULL,
distance INT NOT NULL CHECK (distance >= 0),
PRIMARY KEY (user_name, role_name))
AS
BEGIN
DECLARE @.distance INT, @.next_distance INT
SET @.distance = 0
SET @.next_distance = @.distance + 1
INSERT INTO @.roles (user_name, role_name, distance)
SELECT user_name, role_name, @.distance
FROM UserRoles
WHILE EXISTS (SELECT * FROM @.roles WHERE distance = @.distance)
BEGIN
INSERT INTO @.roles (user_name, role_name, distance)
SELECT DISTINCT R.user_name, P.parent_role_name, @.next_distance
FROM @.roles AS R
INNER JOIN
ParentRoles AS P
ON R.distance = @.distance AND
R.role_name = P.role_name AND
NOT EXISTS (SELECT *
FROM @.roles
WHERE user_name = R.user_name AND
role_name = P.parent_role_name)
SET @.distance = @.next_distance
SET @.next_distance = @.next_distance + 1
END
RETURN
END

-- Example

-- Users
INSERT INTO Users (user_name, password)
VALUES ('moe', 'forget')
INSERT INTO Users (user_name, password)
VALUES ('larry', 'ignore')

-- Roles
INSERT INTO Roles (role_name)
VALUES ('role1')
INSERT INTO Roles (role_name)
VALUES ('role2')
INSERT INTO Roles (role_name)
VALUES ('role3')
INSERT INTO Roles (role_name)
VALUES ('role4')
INSERT INTO Roles (role_name)
VALUES ('role5')
INSERT INTO Roles (role_name)
VALUES ('role6')

-- Parent roles
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role3', 'role4')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role3', 'role5')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role1', 'role3')
INSERT INTO ParentRoles (parent_role_name, role_name)
VALUES ('role2', 'role3')

-- User roles
INSERT INTO UserRoles (user_name, role_name)
VALUES ('moe', 'role4')
INSERT INTO UserRoles (user_name, role_name)
VALUES ('larry', 'role5')
INSERT INTO UserRoles (user_name, role_name)
VALUES ('larry', 'role6')

SELECT user_name, role_name
FROM AllUserRoles()
ORDER BY user_name, distance, role_name

user_name role_name
larry role5
larry role6
larry role3
larry role1
larry role2
moe role4
moe role3
moe role1
moe role2

Regards,
jag