0

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!

  • 5
    You might find these similar (broad) questions helpful: one, two, three. My two cents on top of those is that usually biz logic is better off in the application, and I think that definitely applies to your case here. – LowlyDBA Mar 15 '19 at 19:22
  • 1
    Can the "passed" condition be arbitrary complex, or is it always conjunctions of disjunctions? Would for example the following be a valid condition: (1|(2&(3|(4&(5|6)))))? – Lennart Mar 16 '19 at 10:52
  • @Lennart rules are always just ANDs an ORs like your example – Tom Sitter Mar 17 '19 at 16:25
  • 1
    @TomSitter, so it can be arbitrarily complex. If you want to express that in your data model you will need recursive relationships. It may be better to use for example an XML attribute in module, for the model. – Lennart Mar 17 '19 at 16:36
1

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.

| improve this answer | |
  • Thanks, this is a relatively small dataset (~50K students, 5 courses and 5 modules as of today). I realized I can create a few additional entities (e.g. AccessType, StudentType) that let me break these rules down into more direct relations I can store in tables. It came out to 8 tables (so far) but now I can write a stored procedure to see if a student has met the course criteria for a specific application. Given the amount of students and modules we have I believe this is a viable option. For a much larger dataset this may be a problematic solution. – Tom Sitter Mar 18 '19 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.