3

Suppose I have a users table and in that users table I have a role_id.

In my roles table I have:

users - id - role_id

roles - id - name

roles id | name 1 | admin 2 | normal

Now in code I want to disallow access to the admin.php page. So at the top I put

if ($user->role_id != 1){
    header('location: forbidden.php');    
}

It seems like bad practice to rely on role_id being 1. I would think it is better to do something like:

if ($user->role_name == 'admin'){ ...

Now I am relying on the text of the role, which isn't great. So I have created const classes like:

class RoleType{
    const ADMIN = "admin";
    const NORNAML = "normal";
}
....
if ($user->role_name == RoleType::ADMIN){ ...

The issue here is now if I change it in the database I have to change my RoleType class.

I feel like none of these is ideal.

What is the best way to handle this?

2

As designed with roles being a one to one relationship with users, my personal preference would be to ditch the lookup table for roles entirely and go with an enum data type in the users table. Internally in MySql it's going to be stored in a similar way, so it will not take more space. But it will return the string representation in the results making it clear that you intend to use the string as the key.

Of course, you'll still need to store those strings in your code, and I believe your solution of the constants in the RoleType class is a clean way to do it. Of course you'd want to put that in some shared shared and included file somewhere.

https://dev.mysql.com/doc/refman/5.7/en/enum.html

  • I've never experienced this issue but I have heard that enums can be bad practice in mysql because if you later need to add a new value to the enums, then the entire table needs to be reformatted (which is an expensive 1 time operation). Is that true? RoleType is one place where you could definitely need to add a new role later on. – ajon Jul 21 '15 at 15:34
  • Yes, you're right, it requires an Alter Table statement to add new values and it theoretically could be much more expensive than adding a value to the lookup table. But you need to weigh the size and eventual complexity of the table against the skill and discipline of the programmers. It's one of those areas that there might not be a one size fits all solution. For me it was the one to one relationship that said "enum" (not to mention the data that is unlikely to be modified by the end user) If there is the possibility of needing multiple roles per user in the future then stick with the table. – Bill Heller Jul 21 '15 at 15:42
  • Regardless of how you store it in the DB though, I think you have a good solution with the constants. – Bill Heller Jul 21 '15 at 15:43

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.