Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

use Perl Log In

Log In

[ Create a new account ]

ChrisDolan (2855)

ChrisDolan
  (email not shown publicly)
http://www.chrisdolan.net/

Journal of ChrisDolan (2855)

Saturday June 14, 2008
12:21 AM

Polymorphic database tables?

[ #36687 ]

[I started asking this question on IRC, but it got too complicated... It seems like something basic that most DBAs should know, but I'm not a DBA and I couldn't find a good solution after some searching.]

What's the best way to represent polymorphism in a collection of database tables?

Consider a website where students answer surveys administered by faculty or departments. Start with three database tables: survey, faculty, and department. How do I indicate one-to-one ownership from faculty to survey and from department to survey? I like the strong-typing guarantees of foreign keys, so I really want to avoid un-keyed solutions.

I've thought about the following solutions, but I'm unhappy with all of them:

One null field
Put "faculty_id" and "department_id" foreign keys in the survey table and insist that exactly one is not null. This is awkward in code due to the pervasive conditionals, and problematic as I consider more things that both faculty and departments can own (e.g. student rosters)
Single owner table, two-to-one
The survey table has an owner_id which points to an owner table which has faculty_id and department_id fields, exactly one of them non-null. This is easier to code than the above because everything gets exactly one "owner".
Single owner table, two-to-many
Ownership is not represented in the survey table, but instead the owner table has faculty_id, department_id and survey_id fields. This seems to have no advantage over the "One null field" option.
Multiple owner tables
Create a faculty_survey and department_survey one-to-many tables. How do ensure that each survey is represented exactly once across those two tables?
Multiple survey tables
Partition the surveys into two tables, one for faculty surveys and one for department surveys. This is very painful as I add more things that can be owned.

Am I missing something obvious? What happens when I add another type that can be an owner?

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • You could use Postgres schemas to achieve this - keep your survey table in one schema, and your faculty/department tables, identically structured, in seperate schemas. When you want to work with the faculty stuff, set your search path to survey + faculty. If the survey schema holds unique keys, the same join queries should return different results based on which schema is active.
    • o The survey table has a context_id

      o The context table has:
      - the id pointed to by context_id
      - the owner_id
      - the owner_type_id

      o The owner_type table has:
      - the id pointed to by owner_type_id
      - the name of the owner type

      o The owner_id points to either the faculty
      table's id or the dept table's id (if you
      insist on having these in separate tables,
      an assumption which needs questioning)

      (o Alternately, the faculty and dept tables
      can have owner_type_id, of course)

      o The owner_type's name tells you what type
      of thing, fa