DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 2 of 2

Thread: Which is the best data scheme?

  1. #1
    Join Date
    Feb 2005
    Posts
    1

    Which is the best data scheme?

    We are looking at setting up a unified system of security and user/privilege tables for our applications. There are both dozens of users & applications involved. These applications cover a broad spectrum (financial, MRP-type, lab analytical work, point-of-sale, etc.) Applications & users are a many-to-many relationship. One application may have very few aspects which need to be differentiated between users. Another application may have dozens of distinctions made between which users can do what. Users who have extensive privileges in one application may have very limited privileges in another application, & vice versa, due to the sphere of content handled by the particular applications & the user's role in the company.

    Given the above information, we are reviewing two potential ways of organizing user/application/privilege table data. We would appreciate wisdom as to which approach is better, & why.

    Option A. Primary table contains application name + user id (or a unique key field denoting such), as well as one particular privilege setting for that application for that user. Each user could potentially have many records for a given application, each indicating a different privilege. Privileges for which the user has not access, would not appear in the table. One table, plus some supporting tables are foreseen.

    Option B. Primary table contains application name + user id (or a unique key field denoting such), as well as columns for each potential differentiating function in that application requiring a potential privilege setting. These settings could be T/F or numeric 0-9. Each user has a single record per application. One table per application, plus some supporting tables are foreseen.

    Option A "wastes no data space", plus some have said it is more "normalized." Option B requires more tables, but seems more manageable, in that all user-application settings are in a single record, which lends itself to single-screen management, easier reporting, posting column settings to application/object properties, etc.

    For either option (A or B) there would be a user-application table with records indicating which users have access to which applications, before the A or B table is accessed at all.

    Thoughts & wisdom are welcomed, as to whether to use approach A, B, or something else.

    Thanks,
    Randy Witt

  2. #2
    Join Date
    May 2004
    Location
    Texas
    Posts
    21
    I would have 4 tables.

    Table 1 is a table of groups that users can be assigned to.

    Table 2 a table of user ids ( and info ) and has a foriegn key to groups. Always build a group first so users with similar privileges can be assigne to multiple groups if necessary. Also has user names, adresses, and other physical attributes.

    Table 3 is a table of applications.

    Table 4 a table of application attributes with a foreign key link to groups ( #1 ) and applications ( #3 ). This table has one foreign key column linking it to an application name, the rest of the columns designating attributes ( boolean type ) such as access, editing, printing, ( whatever )etc... You would create one new entry for each group that has a specific/unique application role that can be defined.
    Ex:

    record 1 is linked to application X is is read only
    record 2 is linked to application Y it is read and print
    record 3 is linked to application X it is read, editing, and printing etc...

    #1 is linked to Group "A" , #2 is linked to Group "B", #3 is linked to Group "C".

    Each record is now linked to a specific group. Now one user record can belong to that group or others as needed .

    User 1 in in group A.
    User 2 is in both Group B and C.
    User 3 is in Group C.
    User 4 is in Group C.
    User 5 is in Group B and C.
    User 6 is in Group A. etc...

    This allows for you to only enter as user once and assign a unique id and utilize the creating of groups to manage who can do what. You also onyl keep your applications in one place but define many roles/attributes. Basically normalized.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center