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:

course_id prereq_course_id
---------------------------------
1 2
1 3


This works fine if each course only had a plain list of requirements - but this isn't always the case.

A course may require, for example "3 and 4 and 5 and ((6 and 7) or (7 and 8 and 9))"

I'm completely at a loss as to how to represent these relationships in an SQL database. Any suggestions, or pointers in the right direction would be greatly appreciated.

Thanks.

COURSES
course_id

PREREQS
prereq_id
course_id
boolean_rel
prereq_id_rel

PREREQ_ITEMS
item_id
prereq_id
course_id


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.

Enjoy

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))"?

Thanks.

REVISED***

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.

COURSES
course_id

PREREQS
prereq_id
course_id

PREREQ_RELS
rel_id
prereq_id_1
prereq_id_2
rel_type

PREREQ_ITEMS
item_id
prereq_id
course_id

This will work, and I can't think of a better way to do it at the moment.