Re: Schema design question

On Wed, Aug 17, 2005 at 07:41:20PM +0000, Matt Miller wrote:

Thanks for responding, Matt:


> 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);
[...]
> create table class (id serial primary key,
>                     course_id integer not null,
>                     teacher_id integer not null,
>                     starts_on date,
>                     location varchar);

There may be more than one teacher in the class so instead I'd need
another "class_teacher" link table.

I guess what "bugged" me about this type of layout is that a course
and class share so many columns.  Duplication just looks wrong -- and
I worry about changing a column type on one table and forgetting to
change it on the other table.  Also have to remember to copy all
columns every time a specific class is created.

On the other hand, if I used a single table to represent both types of
entities, then selects are always going to have something like WHERE
type = 'course' added onto the WHERE.  That's extra processing for no
good reason.

> 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.

And thanks very much for you help.

--
Bill Moseley
moseley@hank.org