Saturday June 14, 2008
Polymorphic database tables?
[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?