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 agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.