Should I make 1 or 2 tables for Lecturers and Students in MySql?

Question

I am currently working on a project where you save the details of a lecturer and student. I am not sure if I should use one table User or two tables Lecturer and Student.

When you log in as a lecturer you have special privileges as its a group management page for projects, on the group page when it loads that a student will not have. In User tbl there will be a column status where on register, the page you can choose to be student or lecturer and enter a special lecturer code. I am are using PHP with mySql.

In Summary, should I use 1 User table for both Student and lecturer, or have 2 separate Student and Lecturer tables.

Additional Information: 1 course could have many lecturers and students, but 1 student would have 1 course where as lecturer has many courses.

Answer 1

Great question!

It may seem over complicated, but if you want to scale this system, I highly suggest modeling this a little more "normalized". You are already on the right track by realizing that both lecturers and students are the same entity (people/users). The trick is that you should model "roles", and then model user's roles as well. That makes 3 total tables for this small portion of your model.

USERS               USER_ROLES           ROLES
+------------+      +----------+         +--------+
| id         | <--> | user_id  |     /-->| id     |
| login_name |      | role_id  | <--/    | name   |
| etc        |      +----------+         +--------+
+------------+

users
======
id
login_name
etc

roles
=======
id
name

user_roles
===========
user_id
role_id
since

Sample Data

USERS
+----+------------+
| id | login_name |
+----+------------+
| 1  | Chris      |
+----+------------+
| 2  | Cherri     |
+----+------------+


ROLES
+----+------------+
| id | name       |
+----+------------+
| 1  | Lecturer   |
+----+------------+
| 2  | Student    |
+----+------------+

USER_ROLES
+---------+---------+
| user_id | role_id |
+---------+---------+
|    1    |     1   | <-- Chris is a Lecturer
+---------+---------+
|    2    |     2   | <-- Cherri is a student
+---------+---------+
|    2    |     1   | <-- Cherri is also a lecturer
+---------+---------+

Answer 2

Use a single table with a field that indicates if it's a student or lecturer. It can be a simple integer column name "role" where role=0 means student and role=1 means lecturer.

This is simple, quick to implement, and meets the requirements.