Brandon Jackson Brandon Jackson - 3 months ago 9
SQL Question

Adding new fields vs creating separate table

I am working on a project where there are several types of users (students and teachers). Currently to store the user's information, two tables are used. The

users
table stores the information that all users have in common. The
teachers
table stores information that only teachers have with a foreign key relating it to the
users
table.

users
table


  • id

  • name

  • email

  • 34 other fields



teachers
table


  • id

  • user_id

  • subject

  • 17 other fields



In the rest of the database, there are no references to
teachers.id
. All other tables who need to relate to a user use
users.id
. Since a user will only have one corresponding entry in the teachers table, should I just move the fields from the teachers table into the users table and leave them blank for users who aren't teachers?

e.g.

users



  • id

  • name

  • email

  • subject

  • 51 other fields



Is this too many fields for one table? Will this impede performance?

Answer

I think this design is fine, assuming that most of the time you only need the user data, and that you know when you need to show the teacher-specific fields.

In addition, you get only teachers just by doing a JOIN, which might come in handy.

Tomorrow you might have another kind of user who is not a teacher, and you'll be glad of the separation.

Edited to add: yes, this is an inheritance pattern, but since he didn't say what language he was using I didn't want to muddy the waters...

Comments