How to model Users with Roles and how to fetch them from the Database

Question

I guess this is a common Problem:

• consider an application where Users have a Role

You will likely have something like

User
  username : String
  role     : String

but in your database you have

CREATE TABLE IF NOT EXISTS roles (
    role_id   TINYINT(1)  UNSIGNED AUTO_INCREMENT,
    role_name VARCHAR(10) NOT NULL,
    PRIMARY KEY(role_id),
) ENGINE=InnoDB

and

CREATE TABLE IF NOT EXISTS users (
    username VARCHAR(20),
    role_id  TINYINT(1) UNSIGNED NOT NULL
    PRIMARY KEY(username)
    INDEX idx_role (role_id)
    CONSTRAINT fk_user_role
    FOREIGN KEY idx_role (role_id) REFERENCES roles(role_id)
) ENGINE=InnoDB

What I would like to know is:

What would be the most appropriate approach of creating the User model instance from the database and why?

While this seems to be a trivial and basic problem, I can think of five ways of doing this:

• Query Users and Roles and merge results in a Datamapper into a User instance
• Join Roles on User in Query and map Role name to User role property in a Data Mapper
• Create User from query and lazy load (with caching) the Role name when it's needed
• Preload and cache all Roles to a Repository on App start and look them up when needed
• Hardcode Roles into a Enum-like class in the App instead of the DB

Thanks!

Answer 1

I've done this in the past with a slight exception - users having multiple roles. I had a User table, a Role table, and a Permissions table which linked a user ID to a role ID - multiple times per user as necessary.

When the application came to lookup the user details, it used a view which returned a record-set like:

User | Role           | .... other user fields
andy | Cashier        | .... etc.
andy | Shift Manager  | .... etc.
mary | Branch Manager | .... etc.

If the view didn't return any records then the user wasn't valid because they had to have at least one role.

Answer 2

The model I use is a littlebit different because a user may have several roles

so in the database we have a thre tables

1. users
2. roles
3. rel_users_roles

Then I also have the feature to group useres together in groups and give the roles to the groups with another relation table but this might not be needed in your application.

Speedwise it depends if you just query one user and like to know the roles. In this case I would lazy load the roles for this user.

If you want to get a complete user-role picture at once then a join query would be most efficient (imho).

Answer 3

I would have selected this approach:

Preload and cache all Roles to a Repository on App start and look them up when needed

Based on the assumption that User and Roles information is usually a small set of data that rarely changed but tends to be read often during the lifetime of the application (well, based on my experience anyway). Thus, caching them would be most appropriate as it will avoid overhead in reading them from database over and over again.

However if there exists concern that the data might be changed in other application instance I would also add global flag that indicates when the last time User and Role data was updated. This will allow the application to check the status of the cached information (whether or not it's stale) in a relatively "cheap" manner.