10

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?

10

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.

| improve this answer | |
  • Thanks for you advice. I searched a little more and found this: dba.stackexchange.com/a/36577/46895. "Table inheritance" is the term I was missing. – tptcat Sep 5 '14 at 15:38
  • Thank you for this advice. – Promise Preston Jul 29 at 17:01
  • Nice design, but why create the user_types table since it has only one field which is really bound to the user, why not add the type field directly to users ? – Xsmael Aug 18 at 10:44
  • 1
    @Xsmael user_types table would be useful when we just want to list types of user in the application, On business side there could need for this as well for some reports data etc. On Normalization this will also help is saving some memory. as Id column can be and int or tinyint based on need. – vijayp Aug 20 at 9:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.