Expanding Hierachy with multiple parents

[John Smith]

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 Gilson]

"John Smith" <ha*******@hotmail.com> wrote in message
news:56*************************@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