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.
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
+---------+---------+