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?

回答1:

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

回答2:

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.

I would expect relating to the teacher_id for classes/sections...

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?

Are you modelling a system for a high school, or post-secondary? Reason I ask is because in post-secondary, a user can be both a teacher and a student... in numerous subjects.

回答3:

I would think it fine provided neither you or anyone else succumbs to the temptation to reuse 'empty' columns for other purposes.

By this I mean, there will in your new table be columns that are only populated for teachers. Someone may decide that there is another value they need to store for non-teachers, and use one of the teacher's columns to hold it, because after all it'll never be needed for this non-teacher, and that way we don't need to change the table, and pretty soon your code fills up with things testing row types to find what each column holds.

I've seen this done on several systems (for instance, when loaning a library book, if the loan is a long loan the due date holds the date the book is expected back. but if it's a short loan the due date holds the time it's expected back, and woe betide anyone who doesn't somehow know that).

回答4:

It's not too many fields for one table (although without any details it does seem kind of suspicious). And worrying about performance at this stage is premature.

You're probably dealing with very few rows and a very small amount of data. You concerns should be 1) getting the job done 2) designing it correctly 3) performance, in that order.

It's really not that big of a deal (at this stage/scale).

回答5:

I would not stuff all fields in one table. Student to teacher ratio is high, so for 100 teachers there may be 10000 students with NULLs in those 17 fields. Usually, a model would look close to this:

I your case, there are no specific fields for students, so you can omit the Student table, so the model would look like this

Note that for inheritance modeling, the Teacher table has UserID, same as the User table; contrast that to your example which has an Id for the Teacher table and then a separate user_id.

回答6:

it won't really hurt the performance, but the other programmers might hurt you if you won't redisign it :) (55 fielded tables ??)