User Roles & Login System ERD (Simple Version)

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

 

 

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.

 

 

 

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.

 

 

 

LINKS & REFERENCES

• Entity-Relationship Diagram Symbols and Notation – Lucid Chart
• PHP MySQL User Role Management System – Code Boxx