I'm designing an application to provide students access to modules once they have completed certain online courses.
I have tables for: Students, Modules, Courses, and StudentCourseResults. Course requirements and modules will change over time and I believe it would be easier to track their relationships in the database than the application. Unfortunately this is not a simple 1-1 mapping. For example, for ModuleA you may need to complete Course 1 OR 2, but for ModuleB you may need to complete Course (1 OR 2) AND 3. Requirements may also be somewhat different depending on the type of student.
Has anyone tackled this kind of requirement before? I can't think of a solution other than creating a ModuleCourseRequirements table and putting the logic in a field (e.g. (1|2)&3) then parsing this logic in the application and performing subsequent queries to check the requirements are met.
Let me know what you think, thanks!
Best Answer
If you had billions of students and the rules were fixed, I'd recommend a stored procedure which performs the logic on rolling subsets, saving the results in another column or table. Since you likely have fewer students, a view could handle the logic and then you'd never need to store the result.
If the rules are extremely complicated, then you'll want to pull the data into code and perform the logic there, where you have better control.