SitePoint Sponsor

User Tag List

Results 1 to 2 of 2
  1. #1
    SitePoint Member
    Join Date
    Dec 2009
    Boston, MA
    0 Post(s)
    0 Thread(s)

    Question Database Design Question


    I am designing a large database and I was looking for some opinions on how to go about the design of one of the tables.

    I have the entire database design completed in Visio and ended up with about 50 or so tables. Overall the database has a very good design but I ran into one problem on the permissions table.

    I have one table (related to usergroup) which controls the permissions of the (usergroup) table. I know that its considered bad design to have a table that contains data which is considered to be meaningless to the user.

    For example, on a Select * query, the user should be able to look at all of the columns returned and without any additonal processing they should know what all of the columns mean.

    I need create/edit/delete/view and sometimes private permissions on all of my tables, to give the admins complete control over what usergroups get what access. In order to do this I need to design the permission table in one of two ways.

    1. Create the permissions table so each permission for each table is in a seperate column, for example:
    • canCreateCustomers
    • canEditCustomers
    • canDeleteCustomers
    • canViewCustomers
    • canCreateEmployees
    • canEditEmployees
    • canDeleteEmployees
    • canViewEmployess
    • canViewEmployeesPrivateData

    permissions * tables = 200 + ~30 unique permissions

    I will have ~230 columns in this table, making it vary large and hard to manage.

    2. Create a table which combines permissions in a UNIX style by implementing a permissions interface similar to:


    Basically, the field having ~4 peices of data stored in a binary format, 1 meaning on, 0 meaning off.

    1. perCustomers (1110)
    2. perEmployees (11110)


    For this usergroup this person can create/edit/view customers but they cannot hard delete records. The person can also create/edit/view/delete employees, but they cannot view any private employee data (SSN, etc...)

    Both solutions have pros and cons. With the second solution the data as it stands in the table is meaningless to any person running a query. They will need to perform additional calculations to decode the permissions for that usergroup.

    I'm not looking forward to maintaining a table with ~230 repeating columns, but at the same time I don't want to violate the SQL standards by storing meaningless data in fields. If the permission system is changed at a later date, the entire database could break down.

    I've pretty much made up my mind that I will be using the first design method, but I'm just looking for opinions on what should be done, or perhaps the suggestion of a third design.

    Thanks in advance.

  2. #2
    Programming Since 1978 silver trophybronze trophy felgall's Avatar
    Join Date
    Sep 2005
    Sydney, NSW, Australia
    25 Post(s)
    1 Thread(s)
    Your option 1 ought to be normalised to get rid of the repeating group. That will give you a table with three columns.

    User Group
    Permission Type (eg. canCreateCustomers)
    Permission Value (yes or no)
    Stephen J Chapman, Book Reviews, follow me on Twitter
    HTML Help, CSS Help, JavaScript Help, PHP/mySQL Help, blog
    <input name="html5" type="text" required pattern="^$">


Posting Permissions

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