Skip to content

Instantly share code, notes, and snippets.

@tom--

tom--/modeling problem.md

Last active November 28, 2015 16:58
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save tom--/bbc9a6959f3c5dcaade2 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save tom--/bbc9a6959f3c5dcaade2 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
modeling problem.md

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:

  1. One schedule table with three nullable FKs to the meetings, publications, and broadcasts tables. 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.

  2. One schedule table 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.

  3. Three schedule tables meeting_schedule, publication_schedule and broadcast_schedule each with one proper FK to its owner table. Solves the constraints problem but I need to run queries over all schedules, e.g. selecting everything (meetings, publications, broadcasts) scheduled in a given range of date-times. These would be run from the application and results combined in software.

  4. Same as 3 but add a view combining the three schedule tables. Can this solve the problem with Option 3? It would be a UNION view, I guess. Is that efficient for the combined queries mentioned in 3?

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment