Storing users in a database
- by EMcKenna
Im wondering whats the best way of storing different types of users in my database.
I am writing an application that has 4 main user types (admin, school, teacher, student).
At the moment I have a table for each of these but i'm not sure thats the best way of storing user information.
For instance... Allowing students to PM other student is simple (store sender and receiver student_id) but enabling teachers to PM students requires another table (sender teacher_id, sender student_id).
Should all users be stored in one users table with a user_type field? If so, the teacher / student specific information will still have to be stored in another table.
users
user_id, password_hash, user_type
students
user_id, student_specific_stuff...
teachers
user_id, teacher_specific_stuff...
How do I stop a user who has a user_type = student from being accidentally being entered into the teachers table (as both have a user_id)
Just want to make sure I get the database correct before i go any further.
Thanks...