I'm trying to decide how to model relations where one type of entity is owned by one of several other types of entity. For example, my app involves scheduling. A schedule entry (which corresponds to one record in a schedule table) looks pretty much the same regardless what it belongs to: a meeting, a publication, a broadcast.
Options I considered:
-
One
scheduletable with three nullable FKs to themeetings,publications, andbroadcaststables. It doesn't enforce the constraint that for each row, one and only one of the three FKs must point to an existing PK in its corresponding foreign table. -
One
scheduletable with a "polymorphic FK" column and an FK type column indicating to which of the foreign tables the "polymorhpic FK" points. This admits no DB constrains and is just horrible. -
Three schedule tables
meeting_schedule,publication_scheduleandbroadcast_scheduleeach with one proper FK to its owner table. Solves the constraints problem but I need to run queries over all schedule