Schema design question

I originally was planning on asking about Postgresql inheritance, but
after reading the docs again I do not think that it applies to my
design.

So, now I'm asking a rather basic schema design question.

I have two related objects where one inherits column values from
another.  No, that's incorrect.  The "child" receives default values
from the "parent" when the child is created.

A more concrete example: tables that represent classes taught at a
school.  The parent object is a general course description, and the
child object is a specific instance of a course -- a "class" -- which
is a course taught at a given time and location.  A course can be
taught multiple times, obviously.

A course (and thus a class) can have multiple instructors -- a
many-to-many relationship.  So I have a link table for that.  A class
normally uses the course's default instructors, but may be different
for specific classes instance.

How would you layout the tables for somethings like this?


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

1) Separate tables for "course" and "class" and when a class is
created simply copy column data from the course to the class.

  Pro: Selects are simple

  Con: Column duplication in the two tables -- two tables look a lot alike
       Need to have duplicate link tables (one pointing to each table)


2) Create a third "common_values" table that both "course" and "class"
tables reference.  Then when creating a class from a course clone the
common values row to a new row that the class can reference.

  Pro: No duplication of columns in multiple tables.
       Only need one linking table for instructors (but still need to
       create new links when creating the new row)

  Con: Need to always do joins on selects (not really a problem)

3) Create a single table with a flag to indicate if the row is a
"course" or a "class".

  Pro: Simple selects and no column duplication between tables

  Con: Columns for a course might be ok as NULL, but would be required
       for a specific class.

Again, a "course" and "class" are very similar.  But, once a class is
created from a course it really is its own entity.  For example, if
the course description changes in the future I don't want it to change
on previous classes.  There also needs to be a link between the two.
For example, you might want to show a list of courses, and then see
what classes are scheduled for a given course, so a class should
reference its parent course.

Thanks very much,


--
Bill Moseley
moseley@hank.org