I'm trying to model school courses and their prerequisites.
Originally I had a courses table, and a course_prereq table.
The course_prereq table had a course_id and prereq_course_id - just a simple many-to-many link for courses. For example, if course id=1 required courses 2 and 3, there'd be the following entries in the course_prereq table:
This is a many to many to many relationship. It allows each course to have many pre requisites and each pre requisite to have many components.
The boolean_rel and prereq_id_rel will allow you to relate each prereq item with another using the prereq_id_rel as a FK to another prereq_id and the boolean_rel field to define the relationship as being AND, OR etc.
If you like you can add boolean_rel and item_id_rel fields to the PREREQ_ITEMS table as well, but I doubt it is necessary.
Thanks for the reply. I'm sorry to drag this out, but I don't quite fully understand the relationship between the tables and their fields - especially how to do grouping "like "((6 and 7) or (7 and 8 and 9))".
If it's not too much trouble, could you (or someone) write out the sample tables with values using the example i posted ""3 and 4 and 5 and ((6 and 7) or (7 and 8 and 9))"?
This is perhaps a better way to do it. I haven't got time to fully explain it, however I will possibly have time to do a full dummy data set tomorrow. This table set simply has courses linked to prereqs, each prereq is linked to one or more courses. If there are a multiple courses that are a joint prereq for something else then you'll have multiple courses linked to an entry in PREREQS.
If you have several entries in PREREQ that are to be treated as being related with an AND or an OR relationship then use the PREREQ_RELS table to relate them and define the relationship.