I'm working on a system with the following entities:
There is one important constraint that needs to be enforced:
Activities are always part of one
Activities can also be part of one
section, but only if that
section is part of the
I came up with the following solution:
One FK is a reference from
The other (composite) FK is a reference from
activities.projects_id, activities.sections_id to
sections.projects_id, sections.id. The column
activities.sections_id is NULLable.
sections has a composite PK of
The solution seems to do exactly what I want. I assume that the constraint on
activities.project_id is satisfied when either
sections.projects_id has a value? Any light on this?
Are there any drawbacks to this solution? Any improvements or alternatives I'd need to consider?