complex relationships

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.

I'll try my best to explain this to you...

What you need is a "bridging" table...

for many-to-many relationships, you need an extra table to link them together...

an easier example would be using an address book...
you'll have a lot of people in your book, and the people may have more than one email address...

one way to set this up would be to have a PEOPLE table and an EMAIL table...

so lets say there's three people, we'll call them (1) Tom, (2) ****, and (3) Harry...

Tom will have two email addresses...
**** will have one email address...
Harry will have three email addresses...

we'll make the email address:
1 tom@home.com
2 tom@work.com
3 ****@work.com
4 harry@home.com
5 harry@work.com
6 harry@school.com


So as i said, you'll have two tables, PEOPLE an EMAIL respectively... they'll have ID numbers (as indicated above)..

now, to link the two tables together we'll use a bridging table... let's call it PEOPLE_EMAIL... it will hold the id numbers that connect the people with their respective emails... so the data would be represented like this:



PEOPLE, EMAIL

1, 1
1, 2
2, 3
3, 4
3, 5
3, 6



Perhaps this helps you out a bit?
it's a giant leap into database normalization...

Hi,

Dont overuse many-to-many relationships. If there is a need for many-to-many relationships, always create a lookup or index table which effectively links both many-to-many tables.

To use your example :
<Quote>
for example "3 and 4 and 5 and ((6 and 7) or (7 and 8 and 9))"
</Quote>

CourseID, CourseName
-----------------------------
1, Course1
2, Course2
3, Course3
4, Course4
5, Course5
6, Course6
7, Course7
8, Course8
9, Course9
10, Course10

ComplexPreReqCourseID,CommaDelimitedCourseID ==> Index Lookup Table
--------------------------------------------
1, (3)
2, (4)
3, (5)
4, (6,7)
5, (3,5)
6, (7,8,9)

MappingCourseTableID, CourseID, ComplexPreReqCourseID, OperatorType
--------------------------------------------------------------
1,1,1,"AND"
1,1,2,"AND"
1,1,3,"AND"
1,1,4,"AND"
1,1,6,"OR"

These are a bit raw at this moment and may not be elegant and not what you are looking for. But the idea is to create the Index Lookup Table. The values in this table must be predefined before mapping can take place.

hth