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 :
- Too many entries in the
role_user table for something seemingly simple.
- Too many JOINS for most of the requests
- (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.