数据库设计 - 连接学生和老师
仍在将网络应用程序作为学校项目进行开发，并且它似乎在不断扩展。
我必须为教师添加一个层，以便他们可以通过应用程序管理和指导学生。因此，我必须将老师与学生联系起来。
我想知道最好的方法是什么。我现在有一张桌子供所有用户使用。该表的基本内容是：
id | email | firstname | lastname | role
1 | [email protected] | dromiceio | mimus | 1
2 | [email protected] | tyranno | saurus | 2
3 | [email protected] | utah | raptor | 1
角色是我分配给他们以授予他们某些权限的数字。 1 = 学生，2 = 教师，3 = 管理员。
假设一名学生有一名老师，但一名老师有很多学生，设计数据库表的最佳方法是什么？
我正在考虑创建一个新表，然后将学生/教师 id 放入其中：
例如，如果 Tyranno Saurus 老师（id 2）将上表中的两个学生链接到他，我会创建一个如下表：
pk_id | teacherid | studentid
1 | 2 | 1
2 | 2 | 3
这样，我就知道 Tyranno 老师（id 2）有两个学生，即 userid 1 和 userid 3 的学生。
话又说回来，我从来没有真正从事过这样的事情，所以我想知道是否有人可以给我一些见解关于这一点以及是否可以以更好的方式做到这一点。
我正在用 PHP (CodeIgniter) 和 MySQL 构建我的应用程序；如果这很重要的话。
多谢。
Still working on a web application as a school project, and it just seems to keep expanding.
I have to add a layer for teachers so they can manage and coach students through the application. So, I have to link teachers to their students.
I was wondering what the best way would be to do this. I have one table for all users at the moment. The bare basics of this table are:
id | email | firstname | lastname | role
1 | [email protected] | dromiceio | mimus | 1
2 | [email protected] | tyranno | saurus | 2
3 | [email protected] | utah | raptor | 1
Role is the number I assign to them to give them certain permissions. 1 = student, 2 = teacher, 3 = admin.
Assuming that one student has one teacher but one teacher has many students, what would be the best way to design my database table?
I was thinking of creating a new table and just putting the student/teacher ids in it:
For example, if teacher Tyranno Saurus (id 2) has the two students in the table above linked to him, I would make a table like this:
pk_id | teacherid | studentid
1 | 2 | 1
2 | 2 | 3
That way, I would know that teacher Tyranno (id 2) has two students, namely the student with userid 1 and userid 3.
Then again, I have never really worked on anything like this so I was wondering if anyone could give me some insight about this and if it's possible to do this in a better way.
I'm building my app in PHP (CodeIgniter) with MySQL; if that's of importance.
Thanks a lot.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
如果一个学生有零个或一个老师指导他们，那么我建议在学生表中添加一个 CoachID 列，该列是该特定老师的外键。您建议的中间表没有做任何事情来简化这个简单的关系，它实际上使它变得更加复杂。
如果您将学生与班级联系起来（每个班级有多个学生，每个学生选修多个班级），那么必须有一个中间多对多映射表。
If a student has zero-or-one teacher coaching them, then I would suggest adding a CoachID column to the student table that is a foreign-key to that particular teacher. The intermediate table you've suggested doesn't do anything to simplify this simple relationship, it actually makes it that little bit more complicated.
If you were tying students to classes (where each class has multiple students and each student takes multiple classes) then an intermediate many-to-many mapping table would be a must.