Re: Relational loops in a DB

Actually, now I see you want to keep track of qualifications to
prevent an unqualified trainer from offering a course.  I'll change my
schema by adding a fourth table, `expertise`, and change the foreign
keys of the `schedule` table to reference that instead of referencing
the `course_catalog` and `staff` tables.  Like so:

    sandbox=> \d expertise
           Table "public.expertise"
       Column   |     Type     | Modifiers
    ------------+--------------+-----------
     trainer_id | integer      | not null
     course_id  | character(3) | not null
    Indexes:
        "expertise_pkey" PRIMARY KEY, btree (trainer_id, course_id)
    Foreign-key constraints:
        "expertise_course_fkey" FOREIGN KEY (course_id) REFERENCES course_catalog(course_id)
        "expertise_trainer_fkey" FOREIGN KEY (trainer_id) REFERENCES staff(trainer_id)
    Referenced by:
        TABLE "schedule" CONSTRAINT "schedule_expertise_fkey" FOREIGN KEY (trainer_id, course_id) REFERENCES
expertise(trainer_id,course_id) 

    sandbox=> \d schedule
                 Table "public.schedule"
        Column    |         Type         | Modifiers
    --------------+----------------------+-----------
     offering_id  | character(4)         | not null
     course_id    | character(3)         | not null
     trainer_id   | integer              | not null
     classroom_id | character varying(5) | not null
     start_date   | date                 | not null
    Indexes:
        "schedule_pkey" PRIMARY KEY, btree (offering_id)
        "schedule_ukey" UNIQUE CONSTRAINT, btree (course_id, start_date)
    Foreign-key constraints:
        "schedule_expertise_fkey" FOREIGN KEY (trainer_id, course_id) REFERENCES expertise(trainer_id, course_id)

--
Adam Mackler