Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    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.

  2. #2
    Join Date
    Jul 2003
    Posts
    18
    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

  3. #3
    Join Date
    Sep 2003
    Posts
    2
    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.

  4. #4
    Join Date
    Jul 2003
    Posts
    18
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •