Logical Design For User Roles - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Logical Design For User Roles

Author

Topic

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-21 : 15:32:15

I'm curious ... after reviewing some User/Role models ... I'm thinking the one that I have been using breaks some form of normalization ...

CREATE TABLE Users (Alias VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED)
CREATE TABLE Roles (Role VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED)
CREATE TABLE UserRoles (Alias VARCHAR(50) NOT NULL REFERENCES Users(Alias), Role VARCHAR(50) NOT NULL REFERENCES Roles(Role), PRIMARY KEY CLUSTERED (Alias, Role))

... does it? would this model be a fix for that?

CREATE TABLE Users (Alias VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED)
CREATE TABLE Administrators (Alias VARCHAR(50) NOT NULL REFERENCES Users(Alias) PRIMARY KEY CLUSTERED)
CREATE TABLE Guests (Alias VARCHAR(50) NOT NULL REFERENCES Users(Alias) PRIMARY KEY CLUSTERED)
CREATE TABLE Analysts (Alias VARCHAR(50) NOT NULL REFERENCES Users(Alias) PRIMARY KEY CLUSTERED)

etc for each "role" it has its own table? Maybe I'm going through one of my moments...

CREATE TABLE Users (Alias VARCHAR(50) NOT NULL PRIMARY KEY CLUSTERED, Administrator BIT NOT NULL, Guest BIT NOT NULL, Analyst BIT NOT NULL)

... what about that? From my understanding, model 1 just seems, like it stores redundant data almost, so does model 2 for that matter, but model 2 keeps only information about the entity? well I guess model 1 does too, while model 3 is ok, you have to constantly change your schema when you add a new role, as you would with model 2 but it wouldn't impact the entire application as much ... ah, someone help me, give me some sanity... I know, it all depends on your business rules and data ... blah blah blah ... just looking for the logical model that I can grow from ...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-21 : 16:29:40

Well, definitely don't use the 2nd model (a separate table for each role), that will drive you crazy in short order.

As far as the 1st model, the data is not redundant because the two attributes (user and role) are defining a user-role relationship; this relationship is what the UserRoles table is modeling. Another example would be a dating database for men and women; how would you design a table to list which people went on a date together?

The 3rd model is different, because the roles are actually attributes of a user (like name, age, height, weight)

It's hard to say which one to use because they each have their advantages. In a way, the "logical model" and "normalization" are not really factors, because you can safely justify either design.

I personally would stick with the 1st model because not only is it easier to add new roles, it is also easier to add additional attributes to a user-role relationship (like startDate, EndDate, etc.) Another reason is that the 3rd model works if roles are attributes of users, but would NOT make sense if that were reversed. The 1st model doesn't treat either users or roles as particularly tied, or superior, to one another, and is therefore more flexible in how a relationship can be defined between them.

One advantage I *DO* see in the 3rd model though is that it would be easier to prevent an inappropriate combination or role memberships. It makes no sense for a user to be both an Administrator and a Guest, for example. If that kind of integrity check is important, it will probably be easier to accomplish with the 3rd model.

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-01-21 : 22:10:22

this is either a circular arguement
or relevant. I'm guessing...but the
idea won't go away.

CREATE TABLE Users
(
alias VARCHAR(50),
roles int
)

CREATE TABLE Roles
(
role varchar(50),
number int
)

INSERT INTO Users VALUES (jones, 1)
INSERT INTO Users VALUES (james, 2)
INSERT INTO Users VALUES (smyth, 5)

INSERT INTO Roles VALUES (admin, 4)
INSERT INTO Roles VALUES (poweruser, 2)
INSERT INTO Roles VALUES (user, 1)

So is it possible to return a set of roles based on
for example smyth's assignment of 5? Bitwise operators?

Voted best SQL forum nickname...."Tutorial-D"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-01-21 : 22:48:04

You could store the roles in a binary column, and use bitwise operators to retrieve them based on a mask...BUT:

-You can't take advantage of an index with a binary/bitmask column; you CAN index bit columns though
-8 separate bit columns would take up 1 byte like a binary(1) column would, so there's no difference in space, and the WHERE clause would be simpler with bit columns

SQL Server is really not optimized to use bitwise operators effectively, and they'd only add complexity to use them in a table design like the one described here.

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2003-01-21 : 23:11:32

quote:

So is it possible to return a set of roles based on
for example smyth's assignment of 5?

Do you mean like this?

CREATE TABLE Users
(
alias VARCHAR(50),
roles int
)

CREATE TABLE Roles
(
role varchar(50),
number int
)

INSERT INTO Users VALUES ('jones', 1)
INSERT INTO Users VALUES ('james', 2)
INSERT INTO Users VALUES ('smyth', 5)

INSERT INTO Roles VALUES ('admin', 4)
INSERT INTO Roles VALUES ('poweruser', 2)
INSERT INTO Roles VALUES ('user', 1)

SELECT AA.Alias, BB.Roles
FROM Users AA LEFT JOIN
(
SELECT A.Role As Roles, A.Number
FROM Roles A
UNION
SELECT B.Role + ', ' + A.Role As Roles, A.Number + B.Number
From Roles A, Roles B
WHERE A.Number > B.Number
UNION
SELECT C.Role + ', ' + B.Role + ', ' + A.Role As Roles, A.Number + B.Number + C.Number
FROM Roles A, Roles B, Roles C
WHERE A.Number > B.Number AND B.Number > C.Number
) BB ON AA.roles = BB.Number

Or Just to find out what the maximum role a user has

SELECT A.Alias, (SELECT TOP 1 B.Role From Roles B WHERE A.Roles >= B.Number ORDER BY B.Number DESC)
FROM Users A

Edited by - ValterBorges on 01/21/2003 23:35:28

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2003-01-22 : 05:53:59

http://www.pgro.uk7.net/perform_1903.htm

Jay White
{0}

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-01-22 : 07:48:59

Oh sh*t, this is gonna hurt.

Voted best SQL forum nickname...."Tutorial-D"

Onamuji
Aged Yak Warrior

504 Posts

Posted - 2003-01-22 : 08:06:30

thanks for the replies ... during my downtime at work I like to play with new ideas and ways to design things ... you know ... pretend work ...

Sitka
Aged Yak Warrior

571 Posts

Posted - 2003-01-22 : 08:47:39

From the link CB states

quote:
. The advantage of this is that, when I want to find out which people have a certain right, I don't have to perform any joins. I can simply execute a query that performs a bitwise AND operation between the RightsBitMask column and the bit value of the right(s) I'm querying for.

I think this is central to the idea, the promise of this query.

quote:
Practitioners should be particularly suspicious of "bit-twiddling schemes",

...Pascal

fair enough, this may be bits twiddling.

stuck in my mind though is a kind of searched aggregate syntax that would find the matching set of roles relating to that single bit mask.

Edited by - sitka on 01/22/2003 09:04:47

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources