Check My Database Design / PHP/MySQL

Question

Hello,

I'm currently working on improving my database to make room for growth. As it stands, different users have different 'permissions' to areas of the website. Some users have permissions to multiple areas of the website.

I'd like some feedback if I'm doing this in the most efficient way:

 tblUsers:
    usrID       usrFirst       usrLast       phone    //etc....
      1            John          Doe 
      2            Jane          Smith
      3            Bill          Jones          


 tblAreas: 
    id      name   
     1       Marketing
     2       Support
     3       Human Resources
     4       Media Relations

 tblPermissions:

    id       usrID       areaID   
    1          1           2
    2          1           4
    3          2           1
    4          3           3

Right now, for each "area", I have separate directories. However, I'd like to minimize all of these directories down to one main directory, and then redirect users on logging in to their appropriate 'area' based upon their permissions.

Does it sound like I'm doing this correctly? I've never created a multi-layered site with different permissions and different groups of people, thus, I'm certainly open to learning more on how to do this correctly.

Thanks very much!

Answer 1

The schema looks fine.

I would suggest that you put access control in the controller and base it of of URL path, so that you are not coding it into every section.

Answer 2

The general design is ok. The issues that pop out on me relate to naming.

• SQL doesn't need hungarian notation -- generally considered unnecessary / bad (tblUsers -> users).
• I wouldn't prefix table-names to column-names ...
• ... except for column "id" which should always include your table name (i.e. areaId)
• Your "first" and "last" column don't make sense (hint: firstName)
• I'd rename tblPermissions -> userAreas

Depending on your programming language and database, I'd also recommend using underscore instead of capitalization for your table/column-names.

As for using separate directories for different groups, I'd advise against it. Have the security-checks in your code instead of your directory layout.

Reasoning:

What happens when somebody decides that support is also allowed to do some marketing stuff? Should you change your code, or add a record into your database?

Or what if you have overlapping actions?

@brianpeiris: A couple of things come to mind:

• No need for column aliases in JOINs
• Makes it easier to search through code ("foo_id" gives less results than "id")
• JOIN USING (foo_id) instead of JOIN ON (foo.id=bar.id).

Answer 3

Yes, this seems like it is addressing your need perfectly from the database side.

The challenge will be using the data as simply and declaratively as possible. Where is the right place to declare what "area" you are in? Does each page do this, or is there a function that calculates it, or can your controllers do it? as someone suggests. The second part is evaluating the current user against this. Ideally you end up with a single function like "security_check_for_area(4)" that does it all.