Storing Login details in a Database

Question

I have 3 user roles, ADMIN, GURDIAN, STUDENT. I have the GurdianProfile & StudentProfile tables linked using uniqueID's (i.e. GurdianID, StudentID). That is the link that connects the 'gurdianLogin' tables and 'studentLogin' tables.

My Question is: What is the standard way to structure a 'User Login' table? Should I use one single table to store different types of users (i.e. all the 3 types of users) ? Or is it better to use 3 different tables to use different type of users on a single database ?

What is the best-practice and strategy or recommendations ?

Note: I'm using ColdFusion, MySQL

Answer 1

If you have a single logon point, I would see having credentials spread across 3 different tables as a flaw because if you ever added a 4th user type, you would then have to update your login system to incorporate the new user type. So, i would go with having a single user table, and then if you need separate tables for your guardian/student/admin profiles, link them to the user table.

_User_
id
email
username
password (this better not store the password in plain text..)

_GuardianProfile_
id
userid (links to user table)
(GuardianProfile fields)

_StudentProfile_
id
userid (links to user table)
(StudentProfile fields)

_AdminProfile_
id
userid (links to user table)
(AdminProfile fields)

If the columns in the three profile types are the same, it might make sense to condense that to one table with a type column.