mysql table design concern

2
votes

I'm working on a "e-class" application, where you have admins,teachers,students and lessons. One student can have many teachers and many lessons, one teacher can have many students and many lessons, and one lesson can have one teacher but many students.

I want to make it so admins have their own login page, and the teachers/students share the same. (btw, is this the best approach?)

My question is, should I put all the login related info (username,password,email) into a "users" table, and have seperate tables("teachers","students") for all the rest info?

Should I include a key on the "users" table that points to the primary id of either the "students"/"teachers" table?

What would be the best approach to all of these? Help me out, mysql gurus :)

NOTE: I've seen many questions related to mine, but none of them really answered my question.

1
mysqldatabasedatabase-design
Is there any difference between the info you will be storing for students vs info stored for teachers? - Abe Miessler
yes, each student has a unique parentCode, along with the register date and the semester they are in. - Systemfreak
Does a student get a new record created every semester? - Abe Miessler
no, it's semester field gets updated each time. (btw thanks for your interest :) ) - Systemfreak

1 Answers

1
votes

I would go a step further than Abe Miessler suggests, and create a reference table between Users and Roles.

For example, it is not impossible that a teacher might enroll as a student, and thus be required to assume both roles at different times. 

USER_TABLE:
UserID PK
UserName
OtherUserData

ROLE_TABLE
RoleID PK
RoleName

USER_ROLES
UserID PK
RoleID PK

USER_ROLES has composite PK on UserTable.UserID AND RoleTable.RoleID