DB Design Question - 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)

DB Design Question

Author

Topic

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-14 : 14:50:59

I have a Users table and a Lessons table.
Users by default "have access" to all lessons. Sometimes, I'm going to deny access to certain lessons for certain users.

To do this I created a table called UserLessonPermission:

CREATE TABLE [dbo].[UserLessonPermission] (
[UserID] [uniqueidentifier] NOT NULL ,
[LessonID] [uniqueidentifier] NOT NULL ,
[isDenyed] [bit] NOT NULL ,
[cre_date] [datetime] NOT NULL ,
[cng_date] [datetime] NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[UserLessonPermission] WITH NOCHECK ADD
CONSTRAINT [PK_UserLessonPermission] PRIMARY KEY CLUSTERED
(
[UserID],
[LessonID]
) ON [PRIMARY]
GO

Does anyone see any issues with this? I want to make sure I'm not shooting myself in the foot here. I've never used composite keys (I think that's the right term) in SQL before. Good idea? Bad idea?
What does my JOIN syntax look like?

Thanks for any insights!
Michael

Page47
Master Smack Fu Yak Hacker

2878 Posts

Posted - 2002-06-14 : 15:36:47

If it is truely a situation where all users have access to all reports (with an infrequent exception) creating a maintaining a userlessonpermissions table would me kinda a waste.

Maybe a PermissionDenied table with lessionid and userid in it would be better. Then a correlated subquery like where not exists (select 1 from PermissionDenied where lessionid = outertable.lessionid and userid = @userid) . . .

I dunno, it's friday and I want to go home.

<O>

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-14 : 15:49:58

Page47,
That's basically what I've got. It sounds like I need to remove the isDenyed field though. If I HAVE a record it is denyed. if not, then the user has access.

Thx for the tip on that EXISTS subquery. That's what I was really looking for.

Michael

MichaelP
Jedi Yak

2489 Posts

Posted - 2002-06-14 : 15:54:15

SELECT l.LessonID, l.LessonName
FROM Lesson l
WHERE NOT EXISTS (SELECT 1 FROM UserLessonPermission ulp WHERE ulp.LessonID = l.LessonID AND UserID = @UserID)

Works Perfect! Thx Page47!

Michael

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources