Results 1 to 17 of 17

Thread: User Management Database Design

  1. #1

    Thread Starter
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    User Management Database Design

    Hi all,

    Currently we have three tables that manages users. The schema below fits for a dozen of users. Now that the user list is expanding to almost a hundred and still growing, the schema below needs improvement.

    [dbo].[Users] – the list of users in the system
    [dbo].[Roles] – list of roles/permissions/resources in the system (Employee Dashboard or Employee Entry or Employee List, etc.)
    [dbo].[UserRoles] – assignment of a Role to a User

    We are on the process of brainstorming on applying grouping logic. Any ideas or suggestions on improving the schema above would be helpful.

    Thanks.

    - kgc
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  2. #2
    PowerPoster Zvoni's Avatar
    Join Date
    Sep 2012
    Location
    To the moon and then left
    Posts
    4,415

    Re: User Management Database Design

    I'm in no way a professional, but i would know of two ways.
    1) As described above with an m:n-Intermix-Table. The Table "UserRoles" consisting just (or at least) of Foreign Keys to Tables "Users" and "Roles", additional columns not withstanding
    2) Without an Intermix-Table (in your case "UserRoles"). In that case every Entry in "Roles" getting a unique numeric value, with an additional numeric column in "Users" named "Rights" (or whatever).
    The Idea being that the Field "Rights" in Table "Users" is just a Bitmask of the Values in "Roles"
    Kind of like (in "Roles") --> Note the "binary" notation of values
    Add Employee - Value 1
    Edit Employee - Value 2
    Delete Employee - Value 4
    etc.
    in Table "Users" the Field "Rights" would be like:
    User Zvoni - Rights=5 --> Meaning, i would have the rights to add and delete an Employee, but not edit him/her

    But as i said: I'm no professional, so i've no idea if my model is sound
    Last edited by Zvoni; Tomorrow at 31:69 PM.
    ----------------------------------------------------------------------------------------

    One System to rule them all, One Code to find them,
    One IDE to bring them all, and to the Framework bind them,
    in the Land of Redmond, where the Windows lie
    ---------------------------------------------------------------------------------
    People call me crazy because i'm jumping out of perfectly fine airplanes.
    ---------------------------------------------------------------------------------
    Code is like a joke: If you have to explain it, it's bad

  3. #3
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: User Management Database Design

    Quote Originally Posted by KGComputers View Post
    the schema below needs improvement.
    Why? What are you try to achieve that that schema can't do for you? That schema can handle as many users as you like.

  4. #4

    Thread Starter
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: User Management Database Design

    @jmc.

    Scenario.

    1. There are 10 or more modules in a current system/software.
    2. Each module can have 10 or more roles.
    3. Each user has access to each module and could have one or more or all
    roles for a specific module.

    The issue is that each user needs to have their permissions explicitly assigned.
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

  5. #5
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: User Management Database Design

    Right, OK.. still fail to see the problem.

    User table:
    ID - 1 UserName - TGnome
    ID - 2 UserName - KGComputers
    ID - 3 UserName - BillyBob

    Role table:
    ID - 1 Role - Admin
    ID - 2 Role - Shipping
    ID - 3 Role - Receiving
    ID - 4 Role - Accounting
    ID - 5 Role - Sales
    ID - 6 Role - Janitorial

    UseRole
    UserID - 1 RoleId - 1
    UserID - 1 RoleId - 4
    UserID - 1 RoleId - 5
    UserID - 3 RoleId - 6
    UserId - 2 RoleId - 3
    UserId - 2 RoleId - 5


    Multiple users... with one or more roles... it's a common setup.. Not sure what else you really need.

    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  6. #6
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: User Management Database Design

    Quote Originally Posted by KGComputers View Post
    @jmc.

    Scenario.

    1. There are 10 or more modules in a current system/software.
    2. Each module can have 10 or more roles.
    3. Each user has access to each module and could have one or more or all
    roles for a specific module.

    The issue is that each user needs to have their permissions explicitly assigned.
    So add a Module table and a ModuleId foreign key in the Role table. The number of users isn't really relevant. Your issue seems to be more how to structure a UI to set/edit these permissions than how to store them.

  7. #7
    Smooth Moperator techgnome's Avatar
    Join Date
    May 2002
    Posts
    34,531

    Re: User Management Database Design

    Ah... I must have issed that... I read it a little too fast...

    I'd keep the original three tables and structure and add two new tables:

    table Module:
    ID - Name

    table ModuleRole
    Id - ModuleId - RoleId


    then when you add a module to a user, it adds the roles to the user ... this allows you to add roles by module to a user, but then fine-tune the roles for a given user w/o affecting other users or having to create alternate one-off modules for individual users....


    -tg
    * I don't respond to private (PM) requests for help. It's not conducive to the general learning of others.*
    * I also don't respond to friend requests. Save a few bits and don't bother. I'll just end up rejecting anyways.*
    * How to get EFFECTIVE help: The Hitchhiker's Guide to Getting Help at VBF - Removing eels from your hovercraft *
    * How to Use Parameters * Create Disconnected ADO Recordset Clones * Set your VB6 ActiveX Compatibility * Get rid of those pesky VB Line Numbers * I swear I saved my data, where'd it run off to??? *

  8. #8
    PowerPoster jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    2,392

    Re: User Management Database Design

    Not sure how far you want to go in assigning access rights, but an idea to look at would be rights per function:

    Read Only (On/Off,Yes/No) (can view data, but not make changes)
    Update (On/Off,Yes/No) (Add/Change capability)
    Delete (On/Off,Yes/No) (Can Delete records)

    Sub-section of rights for various report types by user (users can run some reports, but not others).

    Also, you might want to consider User Groups. You can set up User Groups with a set of rights, then just assign a user to that group. That makes it a lot easier to set up rights for a new user that fits into a specific group.

    Just some ideas to think about, without knowing more about the exact roles for each user.

  9. #9
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: User Management Database Design

    I've got that exact same 3 user, role and userrole tables.

    I created a role group table that looks like this

    Code:
    use stufiles
    go
    drop table RoleGroups_T
    go
    create table RoleGroups_T
    (
    	RoleId int
    	,SubRoleId int
    
    	,primary key clustered (RoleId,SubRoleId)
    )
    
    go
    
    insert into RoleGroups_T select 90000, RoleId from Role_T where RoleId between 11000 and 11018
    insert into RoleGroups_T select 90001, RoleId from Role_T where RoleId between 11100 and 11114
    
    --insert into Role_T values(90001,'YEAR_ALL','Year All','')
    --insert into Role_T values(90000,'BLDG_ALL','Buildings All','')
    more in next post....

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  10. #10
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: User Management Database Design

    Then modify the query that pulls roles to UNION this table - care taken to exclude GROUP ROLES if the user has that specific role already (in like a DENIED state).

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  11. #11
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: User Management Database Design

    Quote Originally Posted by szlamany View Post
    Then modify the query that pulls roles to UNION this table - care taken to exclude GROUP ROLES if the user has that specific role already (in like a DENIED state).
    Name:  ur.jpg
Views: 1966
Size:  25.7 KB

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  12. #12
    MS SQL Powerposter szlamany's Avatar
    Join Date
    Mar 2004
    Location
    Connecticut
    Posts
    18,263

    Re: User Management Database Design

    Forums s#$%ks when posting code!!!!!!!!!!!!!!


    Argh!!!

    *** Read the sticky in the DB forum about how to get your question answered quickly!! ***

    Please remember to rate posts! Rate any post you find helpful - even in old threads! Use the link to the left - "Rate this Post".

    Some Informative Links:
    [ SQL Rules to Live By ] [ Reserved SQL keywords ] [ When to use INDEX HINTS! ] [ Passing Multi-item Parameters to STORED PROCEDURES ]
    [ Solution to non-domain Windows Authentication ] [ Crazy things we do to shrink log files ] [ SQL 2005 Features ] [ Loading Pictures from DB ]

    MS MVP 2006, 2007, 2008

  13. #13
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: User Management Database Design

    In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
    I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.

    In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...

    Name:  zzUntitled.jpg
Views: 12150
Size:  31.4 KB
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  14. #14
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: User Management Database Design

    Quote Originally Posted by sapator View Post
    In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
    I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.

    In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...

    Name:  zzUntitled.jpg
Views: 12150
Size:  31.4 KB
    That has been superseded by Identity, which also incorporates third-party membership providers, e.g. Facebook and Twitter, as well as two-factor authentication. I've used it a couple of projects already and am currently using it with two-factor authentication for the first time right now.

  15. #15
    Super Moderator jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    110,297

    Re: User Management Database Design

    Quote Originally Posted by szlamany View Post
    Forums s#$%ks when posting code!!!!!!!!!!!!!!


    Argh!!!
    Not really. Here's some SQL:
    sql Code:
    1. IF EXISTS
    2. (
    3.     SELECT *
    4.     FROM information_schema.TABLES
    5.     WHERE TABLE_NAME = 'Species'
    6.     AND TABLE_SCHEMA = 'dbo'
    7. )
    8.     BEGIN
    9.         DECLARE @sql NVARCHAR(MAX)
    10.         DECLARE @schema NVARCHAR(3) = 'dbo'
    11.  
    12.         SET @sql = N''
    13.  
    14.         SELECT @sql = @sql + N'
    15. ALTER TABLE ' + QUOTENAME(s.name) + N'.' + QUOTENAME(t.name) + N' DROP CONSTRAINT ' + QUOTENAME(c.name)
    16.         FROM sys.objects AS c INNER JOIN sys.tables AS t
    17.         ON c.parent_object_id = t.[object_id] INNER JOIN sys.schemas AS s
    18.         ON t.[schema_id] = s.[schema_id]
    19.         WHERE c.[type] IN ('D', 'C', 'F', 'PK', 'UQ')
    20.         AND s.[name] = @schema
    21.         ORDER BY c.[type]
    22.        
    23.         SELECT @sql = @sql + N'
    24. DROP VIEW ' + QUOTENAME(s.name) + N'.' + QUOTENAME(v.name)
    25.         FROM sys.views AS v INNER JOIN sys.schemas AS s
    26.         ON v.[schema_id] = s.[schema_id]
    27.         WHERE s.[name] = @schema
    28.  
    29.         EXEC sys.sp_executesql @sql
    30.  
    31.         -- drop all tables
    32.         EXEC sp_MSForEachTable 'DROP TABLE ?'
    33.     END
    34. GO
    and here's some VB:
    vb.net Code:
    1. Private Sub startAllButton_Click(sender As Object, e As EventArgs) Handles startAllButton.Click
    2.     If Me.worker.IsBusy() Then
    3.         'Only run one test at a time.
    4.         MessageBox.Show("Cannot start new tasks.  Please wait for all current tasks to complete.",
    5.                         "Start Tasks Failed",
    6.                         MessageBoxButtons.OK,
    7.                         MessageBoxIcon.Error)
    8.     Else
    9.         'A new test cannot be started while this test is running.
    10.         Me.startAllButton.Enabled = False
    11.  
    12.         'The running test can be cancelled.
    13.         Me.cancelAllButton.Enabled = True
    14.  
    15.         'Clear all previous data.
    16.         Me.runningTasksGrid.Rows.Clear()
    17.         Me.completedTasksGrid.Rows.Clear()
    18.  
    19.         'Start the specified number of background tasks.
    20.         For i = 1 To CInt(Me.taskCountSpinner.Value)
    21.             'Generate a random token to identify each task.
    22.             Me.worker.RunWorkerAsync(Guid.NewGuid())
    23.         Next
    24.     End If
    25. End Sub
    Just use HIGHLIGHT tags with the appropriate option.

  16. #16
    King of sapila
    Join Date
    Oct 2006
    Location
    Greece
    Posts
    6,597

    Re: User Management Database Design

    No problem.
    If you prefer that, here is the schema:

    Code:
    IF OBJECT_ID('AspNetUserRoles', 'U') IS NOT NULL
    BEGIN
    DROP TABLE AspNetUserRoles;
    END
    
    
    IF OBJECT_ID('AspNetUserClaims', 'U') IS NOT NULL
    BEGIN
    DROP TABLE AspNetUserClaims;
    END
    
    
    IF OBJECT_ID('AspNetUserLogins', 'U') IS NOT NULL
    BEGIN
    DROP TABLE AspNetUserLogins;
    END
    
    
    IF OBJECT_ID('AspNetRoles', 'U') IS NOT NULL
    BEGIN
    DROP TABLE AspNetRoles;
    END
    
    
    IF OBJECT_ID('AspNetUsers', 'U') IS NOT NULL
    BEGIN
    DROP TABLE AspNetUsers;
    END
    
    CREATE TABLE [dbo].[AspNetUsers] (
        [Id]            NVARCHAR (128) NOT NULL,
        [UserName]      NVARCHAR (MAX) NULL,
        [PasswordHash]  NVARCHAR (MAX) NULL,
        [SecurityStamp] NVARCHAR (MAX) NULL,
        [Discriminator] NVARCHAR (128) NOT NULL,
        [ApplicationId]                          UNIQUEIDENTIFIER NOT NULL,
        [LegacyPasswordHash]  NVARCHAR (MAX) NULL,
        [IsAnonymous]      BIT              DEFAULT ((0)) NOT NULL,
        [LastActivityDate] DATETIME2         NOT NULL,
        [Email]                                  NVARCHAR (256)   NULL,
        [PasswordQuestion]                       NVARCHAR (256)   NULL,
        [PasswordAnswer]                         NVARCHAR (128)   NULL,
        [IsApproved]                             BIT              NOT NULL,
        [IsLockedOut]                            BIT              NOT NULL,
        [CreateDate]                             DATETIME2	         NOT NULL,
        [LastLoginDate]                          DATETIME2         NOT NULL,
        [LastPasswordChangedDate]                DATETIME2         NOT NULL,
        [LastLockoutDate]                        DATETIME2         NOT NULL,
        [FailedPasswordAttemptCount]             INT              NOT NULL,
        [FailedPasswordAttemptWindowStart]       DATETIME2         NOT NULL,
        [FailedPasswordAnswerAttemptCount]       INT              NOT NULL,
        [FailedPasswordAnswerAttemptWindowStart] DATETIME2         NOT NULL,
        [Comment]                                NTEXT            NULL,
        CONSTRAINT [PK_dbo.AspNetUsers] PRIMARY KEY CLUSTERED ([Id] ASC),
        FOREIGN KEY ([ApplicationId]) REFERENCES [dbo].[Applications] ([ApplicationId]),
    );
    
    INSERT INTO AspNetUsers(Id,UserName,PasswordHash,Discriminator,SecurityStamp,
    ApplicationId,IsAnonymous,LastActivityDate,LegacyPasswordHash,
    Email,PasswordQuestion,PasswordAnswer,IsApproved,IsLockedOut,CreateDate,
    LastLoginDate,LastPasswordChangedDate,LastLockoutDate,FailedPasswordAttemptCount,
    FailedPasswordAnswerAttemptWindowStart,FailedPasswordAnswerAttemptCount,FailedPasswordAttemptWindowStart,Comment)
    SELECT Users.UserId,Users.UserName,(Memberships.Password+'|'+CAST(Memberships.PasswordFormat as 
    
    varchar)+'|'+Memberships.PasswordSalt),'User',NewID(),Users.ApplicationId,
    Users.IsAnonymous,Users.LastActivityDate,Memberships.Password,
    Memberships.Email,Memberships.PasswordQuestion,Memberships.PasswordAnswer,
    Memberships.IsApproved,Memberships.IsLockedOut,Memberships.CreateDate,Memberships.LastLoginDate,Memberships.LastPasswordCha
    
    ngedDate,
    Memberships.LastLockoutDate,Memberships.FailedPasswordAttemptCount, Memberships.FailedPasswordAnswerAttemptWindowsStart,
    Memberships.FailedPasswordAnswerAttemptCount,Memberships.FailedPasswordAttemptWindowStart,Memberships.Comment
    FROM Users
    LEFT OUTER JOIN Memberships ON Memberships.ApplicationId = Users.ApplicationId 
    AND Users.UserId = Memberships.UserId;
    
    CREATE TABLE [dbo].[AspNetRoles] (
        [Id]   NVARCHAR (128) NOT NULL,
        [Name] NVARCHAR (MAX) NOT NULL,
        PRIMARY KEY NONCLUSTERED ([Id] ASC),
    );
    
    INSERT INTO AspNetRoles(Id,Name)
    SELECT RoleId,RoleName
    FROM Roles;
    
    CREATE TABLE [dbo].[AspNetUserRoles] (
        [UserId] NVARCHAR (128) NOT NULL,
        [RoleId] NVARCHAR (128) NOT NULL,
        CONSTRAINT [PK_dbo.AspNetUserRoles] PRIMARY KEY CLUSTERED ([UserId] ASC, [RoleId] ASC),
        CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] FOREIGN KEY ([RoleId]) REFERENCES [dbo].[AspNetRoles] ([Id]) 
    
    ON DELETE CASCADE,
        CONSTRAINT [FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] ([Id]) 
    
    ON DELETE CASCADE
    );
    
    INSERT INTO AspNetUserRoles(UserId,RoleId)
    SELECT UserId,RoleId
    FROM UsersInRoles;
    
    CREATE TABLE [dbo].[AspNetUserClaims] (
        [Id]         INT            IDENTITY (1, 1) NOT NULL,
        [ClaimType]  NVARCHAR (MAX) NULL,
        [ClaimValue] NVARCHAR (MAX) NULL,
        [User_Id]    NVARCHAR (128) NOT NULL,
        CONSTRAINT [PK_dbo.AspNetUserClaims] PRIMARY KEY CLUSTERED ([Id] ASC),
        CONSTRAINT [FK_dbo.AspNetUserClaims_dbo.AspNetUsers_User_Id] FOREIGN KEY ([User_Id]) REFERENCES [dbo].[AspNetUsers] 
    
    ([Id]) ON DELETE CASCADE
    );
    
    GO
    CREATE NONCLUSTERED INDEX [IX_User_Id]
        ON [dbo].[AspNetUserClaims]([User_Id] ASC);
    
    CREATE TABLE [dbo].[AspNetUserLogins] (
        [UserId]        NVARCHAR (128) NOT NULL,
        [LoginProvider] NVARCHAR (128) NOT NULL,
        [ProviderKey]   NVARCHAR (128) NOT NULL,
        CONSTRAINT [PK_dbo.AspNetUserLogins] PRIMARY KEY CLUSTERED ([UserId] ASC, [LoginProvider] ASC, [ProviderKey] ASC),
        CONSTRAINT [FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] FOREIGN KEY ([UserId]) REFERENCES [dbo].[AspNetUsers] 
    
    ([Id]) ON DELETE CASCADE
    );
    
    GO
    CREATE NONCLUSTERED INDEX [IX_UserId]
        ON [dbo].[AspNetUserLogins]([UserId] ASC);
    
    
    ALTER TABLE AspNetUsers
    ADD [Profile_DateOfBirth] DateTime NULL,
        [Profile_City] NVARCHAR (MAX) NULL,
        [Profile_UserStats_Height] INT NULL,
        [Profile_UserStats_Weight] INT NULL;
    ἄνδρα μοι ἔννεπε, μοῦσα, πολύτροπον, ὃς μάλα πολλὰ
    πλάγχθη, ἐπεὶ Τροίης ἱερὸν πτολίεθρον ἔπερσεν·

  17. #17

    Thread Starter
    Frenzied Member KGComputers's Avatar
    Join Date
    Dec 2005
    Location
    Cebu, PH
    Posts
    2,020

    Re: User Management Database Design

    The number of users isn't really relevant. Your issue seems to be more how to structure a UI to set/edit these permissions than how to store them.
    @jmc yes - that's one of the concerns we have aside from the DB. That's one of the priorities next year.

    In earlier asp.net versions (classic asp.net NOT MVC) there was a user rights database that was ready to go.
    I can't recall now how it was set up but I was using it for a few years as I was keeping a User Management as part of on website I was developing and it was quite to the point. I don't know what happened to the database in later asp.net versions but it was very useful because you had a ready to go user management. Of course the downside was some "insane" SQL code(that in the end I became quite expert on managing it and remember giving advice to the forum here a few times) for managing the users but you can just look at the schema to get you going.

    In fact....... Found my old app. Here is the schema diagram: I don't know how it will show in the forum but...
    @sapator - yes we have an existing project using the asp.net identity. but this does not fit our client's needs.

    Also, you might want to consider User Groups. You can set up User Groups with a set of rights, then just assign a user to that group. That makes it a lot easier to set up rights for a new user that fits into a specific group.
    Just some ideas to think about, without knowing more about the exact roles for each user.
    @jdc - this is one of the ideas brought up during our brain storm sessions with the team. Thanks for bringing this up.

    I've got that exact same 3 user, role and userrole tables.

    I created a role group table that looks like this

    Code:
    use stufiles
    go
    drop table RoleGroups_T
    go
    create table RoleGroups_T
    (
    RoleId int
    ,SubRoleId int

    ,primary key clustered (RoleId,SubRoleId)
    )

    go

    insert into RoleGroups_T select 90000, RoleId from Role_T where RoleId between 11000 and 11018
    insert into RoleGroups_T select 90001, RoleId from Role_T where RoleId between 11100 and 11114

    --insert into Role_T values(90001,'YEAR_ALL','Year All','')
    --insert into Role_T values(90000,'BLDG_ALL','Buildings All','')
    more in next post....
    @szlamany - i'll include this idea on our meeting next year.
    CodeBank: VB.NET & C#.NET | ASP.NET
    Programming: C# | VB.NET
    Blogs: Personal | Programming
    Projects: GitHub | jsFiddle
    ___________________________________________________________________________________

    Rating someone's post is a way of saying Thanks...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width