添加新字段与创建单独的表
我正在开发一个项目，其中有多种类型的用户（学生和教师）。目前使用两个表来存储用户信息。 users 表存储所有用户共有的信息。 teachers 表存储只有教师拥有的信息，并通过与 users 表相关的外键来存储信息。
users 表
- id
- name
- 34 其他字段
teachers table
- id
- user_id
- subject
- 17 other fields
在数据库的其余部分中，没有对 teachers.id 的引用代码>.所有其他需要与用户关联的表都使用users.id。由于用户在教师表中只有一个对应的条目，我是否应该将字段从教师表移动到用户表中，并为非教师用户将其留空？
例如
users
- id
- name
- subject
- 51 other fields
对于一张表来说，这个字段是否太多了？这会影响性能吗？
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
- 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
- subject
- 51 other fields
Is this too many fields for one table? Will this impede performance?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（6）
我认为这个设计很好，假设大多数时候您只需要
用户数据，并且您知道何时需要显示教师特定的字段。此外，您只需通过执行
JOIN即可获得教师，这可能会派上用场。明天你可能会遇到另一种不是老师的用户，你会很高兴分开。
编辑添加：是的，这是一种继承模式，但由于他没有说明他使用的是什么语言，我不想把水搅浑......
I think this design is fine, assuming that most of the time you only need the
userdata, and that you know when you need to show theteacher-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...
我期望与班级/部分的teacher_id相关......
您正在为高中或高等教育系统建模吗？我问的原因是因为在高等教育中，用户可以在许多学科中既是老师又是学生。
I would expect relating to the teacher_id for classes/sections...
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.
我认为只要您或其他任何人都没有屈服于将“空”列重复用于其他目的的诱惑，那就很好。
我的意思是，您的新表中将有仅为教师填充的列。有人可能会决定他们需要为非教师存储另一个值，并使用教师的列之一来保存它，因为毕竟这个非教师永远不会需要它，这样我们就不需要需要更改表，很快您的代码就会充满测试行类型以查找每列包含的内容的内容。
我已经在几个系统上看到过这样做（例如，当借阅图书馆书籍时，如果贷款是长期贷款，则到期日保留该书预计归还的日期。但如果是短期贷款，则到期日保留该时间预计它会回来，任何不知道这一点的人都会有祸）。
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).
对于一张表来说，字段并不算太多（尽管没有任何详细信息，它看起来确实有点可疑）。现阶段担心表现还为时过早。
您可能正在处理很少的行和非常少量的数据。您关心的问题应该是 1) 完成工作 2) 正确设计 3) 性能，按顺序排列。
这确实没什么大不了的（在这个阶段/规模）。
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).
我不会将所有字段都填充到一张表中。学生与教师的比例很高，因此对于 100 名教师来说，可能有 10000 名学生在这 17 个字段中为 NULL。
通常，模型看起来与此接近：
对于您的情况，没有针对学生的特定字段，因此您可以省略
Student表，因此模型将如下所示< a href="https://i.sstatic.net/xykYP.png" rel="nofollow noreferrer">
请注意，对于继承建模，
Teacher表具有UserID，与User表相同；与您的示例相反，该示例具有Teacher表的Id，然后是单独的user_id。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
Studenttable, so the model would look like thisNote that for inheritance modeling, the
Teachertable hasUserID, same as theUsertable; contrast that to your example which has anIdfor theTeachertable and then a separateuser_id.它不会真正损害性能，但如果您不重新设计它，其他程序员可能会伤害您:)（55 个字段表？？）
it won't really hurt the performance, but the other programmers might hurt you if you won't redisign it :) (55 fielded tables ??)