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