Storing users in a database

Question

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

Answer 1

You must ask yourself if a user must be of one (and only one) of those types, or if it might have zero or more than one of those types... In the later case (at least), you should separate the "user" from the "role".

But these and other questions are typically better answered in the global design phase of your application (define the domain objects and its relationships), and then mapped onto the DB. So, for example, you could decide that the User is an entity which has one/several Roles attached (User 'has' Role), or perhaps and Admin is a subclass of User (Admin 'is a' User).

You might also take a look at the common implementations of classes inheritance in relational databases (even if you are not explicityl doing OOP, even if you do not opt for the 'is a' modelling), it show you possible alternatives ... and shows you that there is no universally right approach.

Answer 2

There are two main strategies to store a class hierarchy in a database, the table per hierarchy strategy and the table per class strategy. Like you suggested, in the table per hierarchy you'd need a column to discriminate the user classes.

It all boils down to whether you deal with objects that are really different or have a common base. For instance are there operations that apply to any kind of user?

It's often subjective but almost all the time I go for the table per hierarchy strategy. You'll find quite often the need to query all users or add foreign keys to the user table.

Also, a variant of the table per hierarchy is to join subtables for for classes, eg. you could add a teacher table containing only the teacher specific columns and use joins with the user table.

Answer 3

for users you will have 2 tables: Users and Groups.

to write specific data about specific entities like teachers or students, use separate tables that all have a foreign key to the user in the Users table.

Answer 4

Your main users table should have a user_type_id column, that links the user to their type. Then when you insert a user you just make sure the correct value is stored for their user_type.

To store the extra data, you could add a column to the users table, called extra_information_id (or something). Then you could have 2 more tables, student_information and teacher_information. When inserting/updating/selecting a user, you could use the combination of the user_type_id column, and the extra_information_id column, to decide which of the 2 extra information tables to use for that particular users extra information.

Answer 5

Using ORM terms, I usually implement authentication in the following manner:

user = id, user id/name, password, created datetime, updated datetime, last login datetime

user has many roles

Using a join table between roles & users so a user could be a teacher & admin, or student & teacher.

roles can be something as simple as

role = id, role name,

or a matrix style permission system like MySQL's user table

role = id, name, can_write, can_read, etc...