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!

Improve this question
4
  • 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 - John M
    Commented Mar 15, 2019 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 - Slava Ukraini
    Commented Mar 16, 2019 at 10:52
  • @Lennart rules are always just ANDs an ORs like your example
    – Tom Sitter
    Commented Mar 17, 2019 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 - Slava Ukraini
    Commented Mar 17, 2019 at 16:36

1 Answer 1

Reset to default
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
1
  • 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
    Commented Mar 18, 2019 at 15:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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