Sample Header Ad - 728x90

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

2 votes
1 answer
102 views
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.
Asked by thshea (198 rep)
Jan 2, 2023, 05:44 AM
Last activity: Jan 16, 2023, 11:06 AM