1

I am developing a school management system with multiple user types/levels; teachers, students, parents etc.

They all have common data such as name, email, telephone, gender etc. Each user type has its own table (tbl_students, tbl_teachers etc) for other data that is not common.

Should I include these fields in the tbl_users table which will be used to handle the login and subsequent redirection to a relevant part of the site (based on user type), OR have a very minimal tbl_users table with only user id and password?

If the tbl_users is minimal, would it be ok when it comes to performance to have two MySQL statements to insert data into two tables on new user registration?

This is my main doubt/question:

Would it be a drain on MySQL performance to constantly have to do joins when displaying simple things like user profiles, contact details etc if the common data is stored in the tbl_users field itself? Or would it be worse in performance if all the basic data is in the other tables such as tbl_teachers would have firstnames, telephone etc?

Bruno
  • 511
  • 2
  • 6
  • 19
  • Why you don't build ONE table for ALL users and simply make a attribut like "role" and then you save integers in there.. from 0 to X. Then you simply define in your code 0 = teacher, 1 = student etc. – Twinfriends Jun 21 '17 at 08:46
  • because not all the data is the same for teachers, students, parents etc. – Bruno Jun 21 '17 at 08:47
  • As per my understanding `tbl_users` use for common fields, login credentials and type. And create sub table for another details. Refer [Vertical Table](https://stackoverflow.com/questions/6811218/working-with-mysql-vertical-designed-table). – Jaydeep Mor Jun 21 '17 at 09:13
  • 1
    You question may have been answered here: https://stackoverflow.com/questions/13749525/relational-database-design-multiple-user-types/13752304#13752304 – Walter Mitty Jun 21 '17 at 11:10
  • @WalterMitty Thanks! That did in fact answer my question :) – Bruno Jun 21 '17 at 11:56

0 Answers0