How to model a MySQL structure to deal with authen

Question

I need help to model a table structure for my database.

I have two tables: teacher and student. And to deal with authentication, I have another table called: user. I want to maintain the information about teacher and student separated of the information about login (user table).

For example:

     teacher
-----------------
|  id |   name  |
-----------------
| 10  |  lorem  |
| 20  |  ipsum  |
| 30  |  dolor  |
-----------------


    student
-----------------
|  id |   name  |
-----------------
|  5  |  amet   |
|  6  |  sitt   |
|  7  |  dolum  |
-----------------

Information separated in two tables. The user table:

     user
-----------------------------------------------------------------
|  id |   teacher_id  |  student_id |    login   |   password   |
-----------------------------------------------------------------
|  1  |     10        |             |  lorem_lo  |   *******    |
|  2  |     20        |             |  ipsum_lo  |   *******    |
|  3  |     30        |             |  dolor_lo  |   *******    |
|  4  |               |     5       |  amet_lo   |   *******    |
|  5  |               |     6       |  sitt_lo   |   *******    |
|  6  |               |     7       |  dolum_lo  |   *******    |
-----------------------------------------------------------------

The solution I thought was that. I ask your opinion about. Is that a good solution?

Thanks in advance.

P.S.: I'm using Mysql (InnoDB)

Answer 1

Yes, this looks satisfactory.

All you will need to do after logon is check whether you have a teacher_id or student_id and handle it appropriately.

Answer 2

To eliminate empty fields from user table, you can do this:

--------------------------------------------------
|  id  |  user  |  level  |  login  |  password  |
--------------------------------------------------

where user contains all your students and teachers ID's and level that contains 1 for teachers and 2 for students.

In this case, you can use level column to create access in your app.

Another method, is with one table... separate students and teacher trough levels but isn't best practice.

Answer 3

The normalized structure should be a bit different. I would create the login table in a different way: id, person_id, role, login and password. That way you can have only one id column without separating teacher and students. in the role column you indicate if a person is a student or a teacher, and use only a foreign key of person_id for each line