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(at)hank(dot)org