2

I'm working on a system with the following entities:

  • Projects
  • Sections
  • Activities

There is one important constraint that needs to be enforced:

Activities are always part of one project. Activities can also be part of one section, but only if that section is part of the activity's project.

I came up with the following solution:

Schema

One FK is a reference from activities.projects_id to projects.id.

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.

The table sections has a composite PK of id, projects_id.

The solution seems to do exactly what I want. I assume that the constraint on activities.project_id is satisfied when either projects.id or 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?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.