473,396 Members | 1,770 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,396 software developers and data experts.

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
Jul 20 '05 #1
1 2525
"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
Jul 20 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: sql-db2-dba | last post by:
There are 2 tables A and B with A being the parent of B. Table A ( Col1 varchar(5) Not Null ) Table B ( B_PK varchar(5) Not Null ,
4
by: Abbey Krystowiak | last post by:
Does anyone know if I can have a field in a row where I can add two lines of info without adding a whole new line? and then there would be a drop down menu? *** Sent via Developersdex...
0
by: Hananiel | last post by:
TABLE1 has ID Data Table2 has ID Data Table3 has
1
by: Homam | last post by:
So I have a composite paging control that shoulld be positioned on the page like this: PagNav ResultSetDisplay PagNav I know that I can't resuse the PagNav more than once in the form, so I...
1
by: TWIOF | last post by:
Hi I'm making a nightlife listings system. I have a table that stores listings, the type of listing can be either an event at a night-club event, at a gig or at a bar. So i could just assign a...
1
by: The alMIGHTY N | last post by:
<store> <frequent_shopper_discount value="5"/> <premium_member_discount value="10"/> <inventory> <item> <msrp value="3.99"/> </item> <item> <msrp value="2.78"/> </item>
26
by: JJ | last post by:
Is there any way you can expand a parent node on the treeview control _without a postback_ by clicking on the node text (NOT clicking the expand image URL) ? I want to format the treeview node...
3
by: Andrew Meador | last post by:
I have searched and found where this has been discussed, but the suggestions I have found have all ended up tuning into debated about whether it needs to be done - but - as far as I can tell - I...
0
muaddubby
by: muaddubby | last post by:
Hi I've got a situation with a dataset that contains two tables with a many-to-one relationship, and am trying to extract the data in xml format. When I execute the GetXml() method, and exception...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...
0
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
0
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.