Roles scheme for college course management and grading system

Question

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?

Answer 1

I think that you need an additional entity to help you out. You clearly have the domain object of a CLASS to represent a specific class (ie: Composition II is a class). You also clearly have a domain object for PARTICIPANT to represent the people that attend the class. I think you have already identified those two.

Next, you need a domain object to represent a specific semester/timeslot/classroom where a CLASS will be held. Let’s call that domain object a CLASSSCHEDULE. You will need a relationship from CLASS to CLASSSCHEDULE to show what topic will be taught in that room during that semester.

Now you need a domain object to represent how a PRATICIPANT interacts with a CLASSSCHEDULE. We can call this domain object an ENROLLMENT. The ENROLLMENT object is where you put your privileges for the PARTICIPANT. The PARTICIPANT is ENROLLed in the CLASSSCHEDULE as a learner or a grader. Your role is attached to the ENROLLMENT object. In this way, each PARTICIPANT will have a different privilege level for each CLASSSCHEDULE that they are involved with.

Answer 2

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.

Gabriel