Re: Schema design question

On Wed, 2005-08-17 at 13:40 -0700, Bill Moseley wrote:
> 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.

If your design leads you to many shared attributes between class and
course, then ask yourself if the class-specific versions must always be
equal to the course-defaulted versions. If yes, then consider factoring
out the common columns into a third table and using foreign keys to
reference that table from the other two. However, if the course merely
suggests defaults for the class, and if any of these defaults can be
overridden by any class, then I would definitely duplicate the columns
in class. I would do this even if a large percentage of classes will
never override the defaults.

A similar situation arises if the child table is supposed to remember
what the values of the parent were at the time the child was inserted.
If the parent can change values over time but the child should be
unaffected then, as before, I would copy the info into each child
record.