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@xxxxxxxx
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend
