Roles Scheme For College Course Management And Grading System

- 1 answer

I've got a system that allows classroom instructors and graders to log in and manage classes and grade papers. A user can be both an instructor and a grader, and can be so for particular semesters. Other users, like students, are also bound to this semester scheme. Still others, like administrators, have accounts that don't run by semester.

When someone is no longer a grader or instructor, she still needs to be able to access past grading or classroom records, albeit with fewer privileges (view only).

I've thought about implementing a roles table with a semester as a part of the key, but that doesn't apply to all users. I've also thought about keeping the semester data separate from the roles, but making roles like "PastGrader" and "PastInstructor" to cover those people who need to have access to past information but should not be allowed to participate in this semester.

What is the optimal data model/roles model for this application?



I think you're on the right track. I would keep the semester out of the roles table, but use them together where needed.

Here is what I did recently that will also work in your scenario:

Create a FunctionalRole table that has the following columns:

 FunctionalRoleId, FunctionalRoleName

These roles will be like jobs. Teacher, Grader, etc.

Add another table called FeatureRole, with the following columns:

 FeatureRoleId, FeatureRoleName

These roles will be for specific features in the application. GradePapers, ViewPapers, etc.

Then create a third table... call it RoleMember, that has these columns:

 FunctionalRoleId, FeatureRoleId

That way, the admin can assign roles more simply by the job and all of the feature roles will automatically be assigned.

And like I said, keep the semester information separate.