Mysql – Multiple User Types – DB Design Advice

database-designMySQLuser-defined-type

I'm developing a web application that will support user authentication with corresponding roles for each user. Also, my users can be of different types and have different fields associated with them. Some of the fields that each user has will be the same, as in:

email, password, first_name, last_name, etc.

But some of the fields for different user types will be different. For example:

User Type: Instructor
Fields unique to this type of user
----------------------------------
hourly_rate, tax_status

==================================

User Type: Student   
Fields unique to this type of user
----------------------------------
instrument, monthly_charge, program

==================================

User Type: Employee
Fields unique to this type of user
----------------------------------
hourly_rate, location

This is a brief example of the types of fields that can be similar and unique between these types of users.

The possible setups I've thought of are:

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

or:

Table: `users`; contains all possible columns for all users and allow columns that could be filled for one user type but not another to be NULL

My question: Is one of these a better approach over the other or are both terrible and I should look at something else entirely?

Best Answer

I would advice 1st one is better approach , which is

Table: `users`; contains all similar fields as well as a `user_type_id` column (a foreign key on `id` in `user_types`
Table: `user_types`; contains an `id` and a `type` (Student, Instructor, etc.)
Table: `students`; contains fields only related to students as well as a `user_id` column (a foreign key of `id` on `users`)
Table: `instructors`; contains fields only related to instructors as well as a `user_id` column (a foreign key of `id` on `users`)
etc. for all `user_types`

Reason:-

User related data like columns in students, instructors etc tables may increase in future so it will be easy to manage with 1st approach.

and choosing the other options to create a table with too many numbers of columns which would be not good in terms of future database management and with increasing number of columns in future it will more problematic.