Preserving foreign key properties when using Class Table Inheritance (or alternatives)

Question

Imagine I have a table events, which outdoor_events and indoor_events inherit from. I have activities then outdoor_activities and indoor_activities. Assume it makes sense for outdoor and indoor to be wholly different structures, though I would be interested in alternative ways to model it than this inheritance.

If I want to preserve the property that all activities are linked to an event, the most straightforward method seems to be to use a foreign key in activities to events.

If I want to constrain outdoor activities to outdoor events, and same for indoor, it makes more sense to put a foreign key in each of the children. However, then the fact that every activity must be correlated with an event is left up to the table's children to implement it.

I could do both, but that in its simplest form is not an acceptable option to me because it is not normalized and which key to use is ambiguous – what if they differ?

To me the second option makes more sense, because it sounds stronger and will satisfy both conditions with a proper implementation.

What might I do to ensure that both of these properties are satisfied? Or is this structure flawed in principle?

Note: I don't believe that this is a duplicate of Using the same table for entities... because that question doesn't address pairing similar inherited tables.

Answer 1

With table inheritance often there is a "type" on the root entity showing which derived entity is populated for this key. For this example it would be something like events.event_type with values "indoor" and "outdoor". Activities would be likewise.

Expand the foreign key (and corresponding primary key in the referenced table) to include this type. Make the foreign key columns NOT NULL and the first goal is achieved.

You say "I want to constrain" and your choice of phrase is apposite. Most DBMS support table constraints whereby one can declare Boolean conditions which the system will enforce when data is written. In this case it will be something like activity.activity_type == activity.event_type_fk. Now an activity's and it's event's types must match i.e. in to in or out to out, satisfying the second requirement.