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?