I am currently working on designing a schema for a scheduling system involving a show and different types of competition.
The requirements are:
showcan contain n
competitionsof different types (e.g. running, jumping etc.)
competitionof a certain type (e.g.
running_competition) can only contain
running_competitorin its schedule
running_competitoris a parent of multiple sub competitors in this discipline. For example:
- Although those specific competitors share a lot of properties they can never be in the same schedule, thus a schedule inside a
running_competitioncan either only contain
My current design is:
show: id: PK_show competition: id: PK_competition show_id: FK_show running_competition: id: PK_running_competition jumping_competition: id: PK_jumping_competition running_competitor: id: PK_running_competitor type: String ("I" == Individual, "S" == Squad) running_competition_id: FK_running_competition jumping_competitor: 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
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