0

I am developing a mobile app for teachers and students... the database contains tables like teacher, student and principal.All these tables have different columns depending upon the role.

Now I want a table called posts that stores posts from all these users.

Problem 1 : I can not define a table called users with a column ROLE to identify a particular user because these roles have different columns/properties.

Also... for these users I am defining 2 columns, one is id that is unique for all teachers and students...and user_id like T23 (for teachers with id 23) and S44 (for students with id 44).

Now I have created posts table with following columns: id, user_is, description, date_created, Status.

Now I am not sure... how to Get all the Posts along with the user name (How Join should work here)

Vishal Kumar
  • 4,419
  • 1
  • 25
  • 31
  • Create a table `ROLES` with 2 columns (role_id, role) put student, teacher and principal in there. Alter your user table with a column role_id. Now in your posts table user_id is always 1 user (teacher, student or principal). Just add the different columns to the user table but make them nullable if they are not needed for 1 user role. – Daan Feb 10 '16 at 13:24
  • POST seems generic to me; almost like a document database storing BLOBs. A USER would have many POSTs, each with a timestamp and a foreign key reference to the USER that wrote it. That is independent of GROUP or ROLE. – duffymo Feb 10 '16 at 15:12

1 Answers1

3

I think the better solution is USER-GROUP-ROLE tables.

USER is obvious; these are your students and teachers.

GROUP is many-to-many JOIN table that relates USER to ROLE. (A GROUP can have many USERs and many ROLEs).

ROLE would be the usual suspects: read/write/delete/execute.

You assign roles to groups, then add users to groups. That way you can allow a student read-only access to their records, but teachers can have read/write access to all their students' records.

You can partition teachers from administrators: teachers can only read/write to students they teach, but administrators should be able to read/write all students in the school. The other advantage to this arrangement is that you don't have to edit USER rows every time they change roles. All you do is modify the GROUP they are assigned to.

duffymo
  • 305,152
  • 44
  • 369
  • 561
  • Looks nice...as I saw the structure in wordpress or Drupal. But Students has columns like Score, attendance, etc. That teacher or admin does not have. How can I manage the varying columns ... can you plz give an example. – Vishal Kumar Feb 10 '16 at 14:22
  • Score? Or Scores? I think a normalized design would break results out from students. A student can enroll in multiple courses, with multiple scores for each one. Your design seems too simple to be useful. – duffymo Feb 10 '16 at 15:04
  • There is no courses in the requirement.... only exams, parents, students, teachers, admin, superadmin, questions, badges etc. The score column for the student shows overall percentage...so it can be a property of each student. The exam table will take care of the individual scores. Just not getting it clear ... How to implement Users -> Teacher, Parent, Student, Admin – Vishal Kumar Feb 10 '16 at 15:43
  • 2
    Exactly. Students take more than one exam? Then score should not be on the USER table. Badges? Don't understand what that is. Students, teachers, admin, super admin, mega admin - all ROLEs. An EXAM would have one ore more QUESTION rows. Would the score on an EXAM be rolled up from the result for each QUESTION? There's an EXAM row for each student? Or do you mean the master that the teacher makes up? Fuzzy thinking; bad design. – duffymo Feb 10 '16 at 15:46