Still focusing on an internet application like a school project, also it just appears to help keep growing.

I must give a layer for instructors to allow them to manage and coach students with the application. So, I must link instructors for their students.

I'm wondering what the easiest way is always to do that. I've one table for those customers right now. The bare fundamentals of the table are:

id | email | firstname | lastname | role
1  | s@s.s | dromiceio | mimus    | 1
2  | d@d.d | tyranno   | saurus   | 2
3  | a@a.a | utah      | raptor   | 1

Role may be the number I assign for them to provide them certain permissions. 1 = student, 2 = teacher, 3 = admin.

Presuming that certain student has one teacher only one teacher has numerous students, an amount be the easiest method to design my database table?

I believed of making a brand new table and merely putting a studentOrinstructor ids inside it:

For instance, if teacher Tyranno Saurus (id 2) has got the two students within the table above associated with him, I'd create a table such as this:

pk_id | teacherid | studentid
  1   |     2     |     1
  2   |     2     |     3

This way, I'd realize that teacher Tyranno (id 2) has two students, namely a student with userid 1 and userid 3.

On the other hand, I have not really done anything such as this so I'm wondering if anybody could produce some insight relating to this and when you can do that in an easy method.

I am building my application in PHP (CodeIgniter) with MySQL if that is worth focusing on.

Thanks.

If your student has zero-or-one teacher training them, then I recommend adding a CoachID column towards the student table that's an overseas-key compared to that teacher. The intermediate table you've recommended does not do anything whatsoever to simplify this straightforward relationship, it really causes it to be that tiny bit more difficult.

Should you be tying students to classes (where each class has multiple students and every student takes multiple classes) then medium difficulty many-to-many mapping table will be a must.