5

I am trying to model a courses structure and dependencies of a university.

For example, in the Computer Engineering department you need to do Data Structures before doing Artificial Intelligence. But you also need to finish Introduction to Logic OR Mathematical Logic and Proofs.

I was thinking about using a table for the courses (Id, Course Number, Course Name, Hours, Professor) and another table for the dependencies.

There lies the problem: How to model this dependencies/prerequisites? The only -viable- solution that I came up with was making this as a string, written in plain English; but that clearly doesn't seem to be a decent solution and I also believe that it violates the atomicity principle.

Notice that the prerequisites of Artificial Intelligence are of the form IA <- DS && (IL || MLP), but other courses will have a different structure like Calculus 2, that requires analytical geometry and calculus 1 done before: C2 <- AG && C1.

I don't know if this is relevant or not, but I will use MySQL.

Improve this question
3
  • I think due to the large amount of combinations you would have for the boolean logic in a real world scenario that part might be better handled by the application, but someone more knowledgeable than me might think differently. So, if you can hand off the boolean logic to the application I would have a table for the courses, and a table for course prerequisites. The prereq table would hold a course_id and the course_id or the prereq- multiple possible prereqs means multiple entries. If done like this you'll easily be able to do a good amount of queries but the logic thing is still tough.
    – LainIwakura
    Commented Jul 21, 2015 at 14:41
  • So (if I got it right) you recommend making a table with the prereqs holding a course_id and the course_id of the prereq, like for the example given: (IA, DS), (IA, IL), (IA, MLP). But how can I know the way they interact? Even if I handle the logic using an external application, I still need to somehow know the information (if the IA depends on DS AND IL or DS OR IL ...).
    – Xaphanius
    Commented Jul 21, 2015 at 15:00
  • Just thought of this..maybe have 3 tables. courses, course_prereq_required, course_prereq_optional (these 2 have the same 2 column structure I first described). Put the required ones in course_prereq_required and optional ones in course_prereq_optional. I'm guessing a use case for this would be checking if a students list of completed courses satisfies the prerequisite requirement, so you'd get the IDs from course_prereq_required and ensure it's in the list- then with course_prereq_optional those ids just need to have one of their existing items in the students list (of completed courses).
    – LainIwakura
    Commented Jul 21, 2015 at 15:45

1 Answer 1

Reset to default
3

You have some Courses:

COURSES
-------
course_id
course_number
course_name

You need to combine one or more of these Courses with a logical operator:

COMBINATIONS
------------
combination_id
logical_operator (AND, OR or NOT)

COMBINATION_COURSES
-------------------
combination_id
course_id

You may also need to combine some of these combinations (so changing the COMBINATION_COURSES table):

COMBINATION_COURSES
-------------------
combination_id
course_id
sub_combination_id

where one and only one of course_id and sub_combination_id is not null.

A Course may have one of the combinations as a prerequisite (so changing the COMBINATIONS table):

COURSES
-------
course_id
course_number
course_name
prerequisite_combination_id

EDIT

So, given your original examples your tables would be populated:

COURSES
=======
course_id course_name                    prerequisite_combination_id
--------- ------------------------------ ---------------------------
        1 Artificial Intelligence                                 12
        2 Data Structures
        3 Introduction to Logic
        4 Mathematical Logic and Proofs 
        5 Calculus 1
        6 Calculus 2                                              13
        7 Analytical Geometry

COMBINATIONS
============
combination_id logical_operator
-------------- ----------------
            11 OR
            12 AND
            13 AND

COMBINATION_COURSES
===================
combination_id course_id sub_combination_id
-------------- --------- ------------------
            11         3
            11         4
            12         2
            12                           11
            13         7
            13         5

Course 1 - Artificial Intelligence has a prerequisite of the Combination 12. Combination 12 is Course 2 - Data Structures AND Combination 11. Combination 11 is Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs.

Hence, the prerequisite of Course 1 - Artificial Intelligence is Course 2 - Data Structures AND (Course 3 - Introduction to Logic OR Course 4 - Mathematical Logic and Proofs).

This structure should allow you to store any prerequisite that is the any logical combination of any number of courses.

Improve this answer
3
  • I like your idea and I think I am able to adapt it. But I still don't see how would it model my problem. Maybe I didn't understand, but I believe there are some flaws on it: 1- how would I model two combinations such as ((A OR B) & (C OR D))? 2- Also, I think that we would need more than just the combination_id, course_id and sub_combination_id in order to represent a combination. Maybe two course_id and two sub_combination_id, but that seems to be lame. Again, I am not sure if I understood what you said (sorry) and an example would help a lot!
    – Xaphanius
    Commented Jul 22, 2015 at 15:14
  • @Xaphanius please see edit
    – DrabJay
    Commented Jul 22, 2015 at 19:04
  • Now that is crystal clear. I thought that I should NOT repeat a combination_id on the COMBINATION_COURSES because I thought it was the key of the COMBINATION_COURSES table, thus the confusion. With the example everything makes sense. Thanks pal!
    – Xaphanius
    Commented Jul 22, 2015 at 20:09

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.