Welcome to a quick sharing of an ERD of a user roles and login system. Are you building your own user login system? Well, a bare essential login system actually only involves email and password… But I am sure you are looking for more, so here it is, a “simple but extended user system” – Read on!
TABLE OF CONTENTS
ER DIAGRAM
First, here is the entire ER diagram and a quick overview of the system.
USER ROLES & LOGIN SYSTEM

As promised, this is kept simple and “not complicated”. I have divided the system into 3 main groups:
- Users – Captain Obvious at your service. The customers, staff, admin, manager, and whoever else using the system.
- Roles & Permissions – What is the purpose of introducing roles to a system? To control access and set permissions.
- Sessions & History – Who is signed in, from which device, and who did what.
FOR REFERENCE ONLY
My developer senses are tingling. I can feel the master keyboard warriors banging on their keyboards – “This system is not accurate”, “this is stupid”. Well, here’s a quick disclaimer. This ERD is ultimately an example and is for reference only.
- If you don’t need roles, feel free to remove them.
- If you want to allow users to have multiple roles, feel free to add another “user to roles” table.
- If you don’t want the security and history, go ahead and remove it.
You are building your own system now. You decide the rules for yourself.
TABLE DETAILS
Now that we are done with the overview, let us get into more details on the entities or tables.
PART 1) USERS
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary key. |
role_code | VARCHAR | User’s role. Up to you – “C”ustomer, “M”anager, “S”taff, etc… |
user_name | VARCHAR | User’s full name. |
user_email | VARCHAR | Unique, user’s email. |
user_password | VARCHAR | Password. Encrypt in your own system. |
For some reason, some newbies like to create multiple user tables with the same dataset – Customers, Managers, Admin, etc… No, we don’t have to do that. If the dataset is the same, a single “user role” field will get the job done.
PART 2) ROLES & PERMISSSIONS
2A) ROLES TABLE
COLUMN | DATA TYPE | NOTES |
role_code | VARCHAR | Primary key. Once again, this is up to you to decide – “ADM”, “CUS”, “MGR”, etc… |
role_name | VARCHAR | The “full name” of the role – “Administrator”, “Customer”, “Manager”, etc… |
role_desc | VARCHAR | A short description of the role, optional. |
Pretty self-explanatory, the user roles that you wish to implement in your system.
2B) PERMISSIONS TABLE
COLUMN | DATA TYPE | NOTES |
perm_id | VARCHAR | Primary Key, up to you to decide. Maybe “INV” for access to the inventory module, and “FIN” for the finance module. |
perm_desc | VARCHAR | A short and optional description of the permission. |
Now, some people like to micro-manage and add every single action to the permissions table – Add user, edit user, delete user, etc… Years of experience have taught me that it’s better to control in terms of modules instead of individual actions. E.G. Only users with “USR” permission can add/edit/delete users.
2C) ROLES-PERMISSIONS TABLE
COLUMN | DATA TYPE | NOTES |
role_code | VARCHAR | Primary and Foreign Key. |
perm_id | VARCHAR | Primary and Foreign Key. |
Lastly, this table specifies which roles have which access permissions.
PART 3) ACCESS & HISTORY
3A) SESSIONS TABLE
COLUMN | DATA TYPE | NOTES |
sess_id | VARCHAR | Primary Key. This can be a unique token ID for the session cookie. |
user_id | VARCHAR | Primary and foreign key. |
sess_date | DATE TIME | When the session started, can also be used to calculate the expiry date. |
sess_device | VARCHAR | Device info on where the user is signed in from (user agent). |
sess_ip | VARCHAR | User’s IP address. |
The main purpose of this table is not so much to facilitate the login, but for security. The theory here:
- Yes, it’s no longer the Stone Age of the Internet, users should be able to login from multiple devices.
- We will generate a unique session ID, also save the user’s device info plus IP address.
- Key security check – If the session suddenly changes to a different device, something is wrong.
- Security consideration – If the IP address suddenly “jumps” into a different region/country, do a security challenge (click on a link in email). The user may be using a VPN, or the session got hijacked somewhere.
3B) HISTORY
COLUMN | DATA TYPE | NOTES |
user_id | INTEGER | Primary and foreign key. |
history_date | DATE TIME | Primary key, timestamp of the action. |
history_action | VARCHAR | Whatever you want to record. For example, “created a new user”. |
history_notes | VARCHAR | Additional notes, if any. |
Finally, we have a table to keep track of user actions – For accountability.
EXTRAS
That’s all for the tutorial, and here is a small section on some extras and links that may be useful to you.
LINKS & REFERENCES
- Entity-Relationship Diagram Symbols and Notation – Lucid Chart
- PHP MySQL User Role Management System – Code Boxx
THE END
Thank you for reading, and we have come to the end. I hope that it has helped you to better understand, and if you want to share anything with this guide, please feel free to comment below. Good luck and happy coding!