Storing users in a database

Posted by EMcKenna on Stack Overflow See other posts from Stack Overflow or by EMcKenna
Published on 2010-04-17T16:12:46Z Indexed on 2010/04/17 16:33 UTC
Read the original article Hit count: 219

Filed under:
|
|

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...

© Stack Overflow or respective owner

Related posts about sql

Related posts about mysql