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.
| Author |
Topic |
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
 Posted - 2004-08-13 : 16:56:14
|
| I have a table I'm creating that is going to represent access levels to the web app I am working on.I have about 100,000 users, and about 10 of which will need special access. I've had it hardcoded in my .NET code before, but the 10 people is switching so much its time to move it to SQL Server (plus other reasons)I'm not sure exactly what is the correct way to design this. I was thinking of creating a table as such below, or would it be better to have 3 seperate tables. (1 for each access level)userID, Level1, Level2, Level3100, T, T, T 101, T, F, F102, F, T, T103, T, T, T104, F, F, T105, T, T, F106, T, T, Fetc...Any tips on this one is really helpful and greatly appreciated!Thanks once again guys, have a great weekend ! mike123 |
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-13 : 16:58:33
|
| userIDAccessLevelEnabledThis is how I would build the table. You could also just store an XML string for each user that holds the permissions. You could then just pass that to the application on startup.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
 |
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-08-13 : 17:02:41
|
| your quick! That design does seem alot better, it will make my inserts a bit trickier I believe, for users with multiple access levels etc..I'll figure it out tho.thanks for the help! mike123 |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-08-13 : 17:15:16
|
| Would it make more sense to have just userID and accessLevel ? And only store the "True" values ?Thanks again!mike123 |
|
|
|
derrickleggett
Pointy Haired Yak DBA
4184 Posts |
Posted - 2004-08-13 : 17:20:53
|
| That would also work. You could have an AccessLevel table with all the access listed. Then just have a UserAccess table with the UserID and AccessLevelID.MeanOldDBAderrickleggett@hotmail.comWhen life gives you a lemon, fire the DBA. |
|
|
|
mike123
Master Smack Fu Yak Hacker
1462 Posts |
Posted - 2004-08-13 : 17:24:09
|
| Ya I'm only controlling access to 3 different pages, but there is no consistency between users with access to one page and access to another. So I will just do something like thisuserID, pageAllowed1000, 11000, 21000, 31001, 11002, 31003, 11003, 3Thanks~!mike123 |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2004-08-15 : 07:49:05
|
| I prefer Roles. I don't think I've ever had a "levels" soution that didn't have to be changed to "Can Bob at Level 4 also have access to PageX that is at Level 2 please ..."So that would give you:Roles Table - jsut a list of IDs and DescriptionsUserRoles - A list of UserID + RolePageRoles - A list of PageID + Roleand then a user is allowed access to a page if they have a rolw that can be found in the Page Roles.(In practice I cache the Roles for a user when they login - disadvantage is that they have to logoff if I need to add a new role for them)Kristen |
|
|
|
|
|
|
|
|
design help