Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

thebigk's avatar
Level 13

Need opinions on database design for roles & permissions

I'm developing an app with Laravel which will have multiple sub-webapps. Think of it as a play store for web apps.

All the apps will share a common users table and each app will need users to have different roles and permissions.

I came up with two approaches to design the tables:

First: users table has role and permissions columns

//User Table

id | Name | Email | .. | .. |  Role | Permissions

Sample entries in Role and Permissions columns

Editor | 'edit_posts|publish_posts|delete_posts|add_authors|delete_authors'
Author | 'edit_posts'|'publish_posts'

Second: Multiple tables Viz. users, role, role_user, permissions

users
id | Name | Email
1 | Jack | [email protected]

role
id | Name 
1  | Admin
2 | Editor
3 | Author
4 | Member

role_user
id | role_id | user_id
1   | 1  | 1
2  | 1  | 2
3 | 3  | 3434

permissions
id | role_id | type
1 | 2 | 'edit_posts|publish_posts|delete_posts|add_authors|delete_authors'
2 | 3 | 'edit_posts|publish_posts''

The general advice on the Internet seems to be in favor of the second approach. My main worries with it are :

  1. Too many entries in the role_user table for something seemingly simple.
  2. Too many JOINS for most of the requests
  3. (maybe) complex code? Not sure about this.

Two important factors:

A. There will be many more sub-apps in the future and they'll require various roles and permissions

B. The app is likely to grow quickly, about ~1000-2000 new users daily. Will I be touching any mysql limits? Pardon my gross ignorance about mysql databases.

I've never done database design before and would really appreciate your suggestions and tips.

Addendum: I do not wish to use any of the roles/permissions packages available. I'd rather develop it on my own.

0 likes
1 reply
jkoech's avatar

The reason the second approach is favourable is because of scalability. You won't have any issues in the long-run. I highly recommend the second approach since it has been my go-to approach for most of my projects.

When running your queries, just eagerload the relationship. Eager loading alleviates the N + 1 query problem.

1 like

Please or to participate in this conversation.