Re: Schema design question

On Wed, 2005-08-17 at 10:49 -0700, Bill Moseley wrote:
> The parent object is a general course description, and the
> child object is a specific instance of a course
> ...
> tables that represent classes taught at a
> school. The parent object is a general course ... the
> child object is ... a "class" -- which
> is a course taught at a given time and location. A course can be
> taught multiple times ... A course (and thus a class) can have
> multiple instructors
>
> How would you layout the tables for somethings like this?

create table course (id serial primary key,
description varchar);
create table teacher (id serial primary key,
name varchar);
create table course_teacher (course_id integer not null,
teacher_id integer not null);
alter table course_teacher add primary key (course_id, teacher_id);
alter table course_teacher add foreign key (course_id)
references course
deferrable initially deferred;
create index course_teacher_teacher_ix on course_teacher (teacher_id);
alter table course_teacher add foreign key (teacher_id)
references teacher
deferrable initially deferred;
create table class (id serial primary key,
course_id integer not null,
teacher_id integer not null,
starts_on date,
location varchar);
create index class_course_ix on class (course_id);
alter table class add foreign key (course_id)
references course
deferrable initially deferred;
create index class_teacher_ix on class (teacher_id);
alter table class add foreign key (teacher_id)
references teacher
deferrable initially deferred;

> A class
> normally uses the course's default instructors, but may be different
> for specific classes instance.

When a class is created the user first specifies course_id. At that
point the app can look at course_teacher and offer the list of default
teachers. In case a non-default teacher is desired the app also offers
a lookup into teacher to see all available teachers. The teacher_id
column of class is thus populated. Set the "start_on" date and the
"location," and you're done.

> I can think (out loud) of three ways to set this up:

I'm sure there are many ways to get there. To me, the way I've
described is the most-direct way to represent the relationships you've
described.