Imagine I have a table
indoor_events inherit from. I have
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
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.