I am creating a student marks database, but am wondering how best to handle permissions.

The current setup

Currently administrators, instructors, and students are all in their own tables. In the course table I have the instructorID as a primary key (but this only allows one instructor per course). I also have a table called classes_students which links students to specific classes.

Problems with this design

I see issue with this setup as it does not allow multiple instructors to share the teaching of a class. It also does not allow someone to have multiple roles (such as an instructor who may be taking another class).

Role based design

I am therefore trying to figure out how to properly implement more flexible roles in the database.

I have broken down the roles into at least a few categories (not sure if this is the right way to go about this):

  • administrator (has full access to do anything on the site - view, add, edit, delete anything on site (courses, assignments, marks, etc.)
  • course creator (has the ability to create and delete courses and assign or remove instructors and students to courses)
  • instructor (has full access to do anything with their own classes - view, add, edit, and delete assignments/marks and can add or remove students from courses)
  • non-editing instructor (has view access to the class)
  • student (has view access to their own marks)

Is this an acceptable way of breaking things down?

If so what would be a good way to implement it in a database structure? If not, what would be a better way to go about this?

Site-wide vs class-specific roles

Some of the roles I defined above seem to be site-wide roles, and others specific to individual classes. I am having trouble wrapping my mind around how these would work together in a database / query environment.

My new idea for table structure

Based on more thinking, here is what I am planning to do for tables:

course (id, course_name)
user (id, first_name, last_name)
site_roles (id) (examples: administrator, viewer, course_creator)
course_roles (id) (examples: owner, viewer)

Then to assign roles to users:

user_course_roles (FK: course.id, FK: user.id, FK: course_roles.id)
user_site_roles (FK: user.id, FK: site_roles.id)

Does this make sense or am I missing something?

Your Answer

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Browse other questions tagged or ask your own question.