I am currently working on designing a schema for a scheduling system involving a show and different types of competition.

The requirements are:

  1. A show can contain n competitions of different types (e.g. running, jumping etc.)
  2. A competition of a certain type (e.g. running_competition) can only contain running_competitor in its schedule
  3. running_competitor is a parent of multiple sub competitors in this discipline. For example: individual_running_competitor, squad_running_competitor.
  4. Although those specific competitors share a lot of properties they can never be in the same schedule, thus a schedule inside a running_competition can either only contain individual_running_competitor or only squad_running_competitor

My current design is:

   id: PK_show

   id: PK_competition
   show_id: FK_show

   id: PK_running_competition

   id: PK_jumping_competition

   id: PK_running_competitor
   type: String ("I" == Individual, "S" == Squad)
   running_competition_id: FK_running_competition

   id: PK_jumping_competitor
   type: String ("I" == Individual, "S" == Squad)
   jumping_competition_id: FK_jumping_competition

This schema does not prevent a schedule to mix between individual and squad competitors. One obvious solution would be to have a type of running_competition for each of the specific types (maybe with a type identifier as i did with in the competitor tables), but this would create a lot of classes on the code side (only differing in the type of schedule items) How can i solve this?

To make matters even worse: I would also like to have some kind of shared break item inside a schedule of a competition which can be used regardless of type. I thought about just using a separate list for those break items inside the competition, but this seems a little bit awkward.

Thanks for any hints or suggestions

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.