Re: Schema design question - Mailing list pgsql-general

From Matt Miller
Subject Re: Schema design question
Date
Msg-id 1124307680.3074.24.camel@dbamm01-linux
Whole thread Raw
In response to Schema design question  (Bill Moseley <moseley@hank.org>)
Responses Re: Schema design question
List pgsql-general
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.


pgsql-general by date:

Previous
From: "Joshua D. Drake"
Date:
Subject: Re: PostgreSQL 8.0.3 limiting max_connections to 64 ?
Next
From: Sebastian Hennebrueder
Date:
Subject: Re: Schema design question