2

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.

Walter Mitty
  • 18,205
  • 2
  • 28
  • 58
drapu
  • 51
  • 6
  • This is based on your data. If all the user has the same master data, and only the type of them is changing, then use 1 table with a flag field. – vaso123 Nov 17 '14 at 16:05
  • 2
    Yes, very data-dependent. Is it possible for a lecturer to be a student in another lecturer's class? If so, you'll have one person saved as two entities if you go for two different tables, which is normally a bad idea. I'd certainly lean towards a single table, but it will depend on the details of your model and your reality. @ctrahey's suggestion of adding the idea of a "role" to a single table of users is a very traditional and well-tested solution to the problem. – Matt Gibson Nov 17 '14 at 16:08
  • Some of the information is different in the Student table as the Student has CourseID as a foreign so I will presume I will need to keep Student and Lecturer in separate tables. A student has many lecturers and a lecturers have many students and I'm currently using a linker table in the database to connect the two. I had assumed that I could put both in a single User table. – drapu Nov 17 '14 at 16:12
  • I taught at a University that I was a student at. One of the lecturers sat in on my class as a student. – Neil McGuigan Nov 17 '14 at 23:05
  • I added two tags to your question, namely single-table-inheritance and class-table-inheritance. These are two design patterns that may hold the key to answering your own question. – Walter Mitty Nov 18 '14 at 11:29

2 Answers2

7

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
+---------+---------+
Chris Trahey
  • 18,202
  • 1
  • 42
  • 55
  • 1
    @minboost this is not complicated. This is a very flexible solution that is also very unambiguous – james Nov 17 '14 at 16:20
  • 1
    This solution requires 3 tables, and presumably a front end to manage roles. It provides NO ADVANTAGE over my single column solution (which also allows multiple roles if you treat it as bitwise). The only time this would matter is if you had an additional table to manage permissions for roles, but in this case you're just creating two extra tables to put a label on the role. – minboost Nov 17 '14 at 16:22
  • 1
    @minboost That's a fair contrast to point out, but I want to advocate for durable software patterns, esp. on SO where many future visitors will find this post, and they may have slightly different needs. Good data modeling is one of the most durable pieces of value in a software package. I think it's worth the investment. – Chris Trahey Nov 17 '14 at 20:14
-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.

minboost
  • 2,555
  • 1
  • 15
  • 15