Polymorphic database tables?

ChrisDolan on 2008-06-14T05:21:19

[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?

---

Which database engine are you using?

mattk on 2008-06-14T07:54:19

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.

Here's what I'd try first

Ron Savage on 2008-06-15T01:37:24

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, faculty or dept, to instantiate
to handle the desired behaviour of the
owner

o The owner_type table can be effortlessly
extended, as per the very last comment in
your original post

o The golden rule is that every list of
things goes in it's own table. Hence the
tables for surveys, owner_types, faculties
and depts (if the last 2 are really so
different)

o From that follows the owner_type_id
field and the owner_id field

o Your design fault, which you seem committed
to, is the put a list into a list of foreign
keys, faculty_id and dept_id, rather than
into a table. It's a classic mistake :-)

o Reposted via emacs after use.$curse.perl.org
let me preview and submit my last post but still
threw it away, AFAICT

use views

hdp on 2008-06-14T11:48:01

http://www.kineticode.com/docs/polymorphic_database_design.pdf