1

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)

Improve this question
2
  • 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. Commented Feb 10, 2016 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. Commented Feb 10, 2016 at 15:12

1 Answer 1

Reset to default
4

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.

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

4 Comments

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.