mysql table design concern [closed]

Question

Closed. This question is opinion-based. It is not currently accepting answers.

Want to improve this question? Because this question may lead to opinionated discussion, debate, and answers, it has been closed. You may edit the question if you feel you can improve it so that it requires answers that include facts and citations or a detailed explanation of the proposed solution. If edited, the question will be reviewed and might be reopened.

Closed 7 months ago.

Improve this question

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.

Is there any difference between the info you will be storing for students vs info stored for teachers? — Abe Miessler
– Abe Miessler, Commented Jan 25, 2013 at 18:30
yes, each student has a unique parentCode, along with the register date and the semester they are in. — Systemfreak
– Systemfreak, Commented Jan 25, 2013 at 18:36
no, it's semester field gets updated each time. (btw thanks for your interest :) ) — Systemfreak
– Systemfreak, Commented Jan 25, 2013 at 18:40

XIVSolutions · Accepted Answer · 2013-01-25 18:48:48Z

1

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

answered Jan 25, 2013 at 18:48

XIVSolutions

4,52221 silver badges24 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Horus Over a year ago

Just as an aside, and definitely not required, but I often would pk the cross reference table (user_roles here) because it can often make it way easier to deal with some orms that way. +1 to your solution though

XIVSolutions Over a year ago

@Horus - Yeah, I've done the same thing. Also might even put an "Enabled" bit field on there, in case there is a reason to enable/disable without actually removing access.

Systemfreak Over a year ago

Seems like I hadn't marked this as correct, well here you go :)

Collectives™ on Stack Overflow

mysql table design concern [closed]

1 Answer 1

3 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Related