Database Design - Linking student to teacher

Question

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.

you wouldn't happen to have a picture of teacher: Tyranno Saurus would you? — Johan
Your second table will work just fine. But if you're ever building this database for real, I'd advice a student and teach table that reference your user table. Imagine if you wanted to store information that was specific to students, can you image what your user table would look like with columns that only applied to some rows? — enamrik
@Johan confidential, I'm sowee. @enamrik You're absolutely right. I'm building this project as part of a course where we basically have to show off everything we can do as webdevelopers. I'm nearly done and have a week before the deadline, so I figured I'd expand into the teacher part. This isn't a 'real' project so I guess the DB will do for now as it is, but I realise this wouldn't work out in a real life situation. Thanks for pointing that out! — Joris Ooms

Will A Will A · Accepted Answer · 2011-05-14T17:39:41

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.

Database Design - Linking student to teacher

1 Answers