0

I've been stuck on this for a while now so I decided to ask for some help.

My SetTemplate needs to EITHER be connected directly to exerciseTemplate OR connected to exerciseTemplate through a progression. If it's connected directly, it should have a relationship with "Linear"

Right now it's split into this, which has duplicated tables (two exercise tables and two set tables). I need it in one single connected schema without duplicates. enter image description here

Here are some conditions:

Multiple exercises can have the same progression template (which means same sets aswell).

If it doesn't have an progression, it should have a "linear" which all exercise templates share.

If it doesn't have an progression, it should have set templates attatched to it, the sets are unique for that exercise.

Is there any nice way to do this?

Improve this question
5
  • 2
    You could have both FK columns "exerciseID" and "progressionId" in the SetTemplate table, and ensure in your application code or by using appropriate check constraints that one and only one of them is used. You might apply the same transformation to the ExerciseTemplate as well, so it has columns linearID and progressionID where only one is used. This can also be checked with a check constraint. It's probably harder to check that a MainExercise isn't referenced directly from a SetTemplate, so that may be something which is only ensured by the application.
    – Hans-Martin Mosner
    Commented Jul 9 at 15:54
  • @Hans-Martin Mosner, Doesn't this break 3NF though?
    – Jocko
    Commented Jul 9 at 16:10
  • please don't cross-post: stackoverflow.com/questions/78723249/… "Cross-posting is frowned upon as it leads to fragmented answers splattered all over the network..."
    – gnat
    Commented Jul 9 at 16:42
  • @Jocko I'm in no way a RDB expert - I just use them in ways that work for me and the applications I write :-) So maybe you're right, and another solution would be preferrable. You need to weigh the pros and cons.
    – Hans-Martin Mosner
    Commented Jul 10 at 5:53
  • @Jocko could you elaborate why the suggestion by Hand would not be 3NF ?
    – Christophe
    Commented Jul 10 at 17:50

0

Reset to default

Browse other questions tagged or ask your own question.