-1

Let's say I have these models:

TABLE trainers (
  id -- PK
  -- many fields here...
);

TABLE players (
  id -- PK,
  trainer_id -- FK,
  -- many fields here...
);

TABLE tournaments (
  id -- PK,
  player_id -- FK
  -- many fields here...
);

TABLE games (
  id -- PK,
  tournament_id -- PK,
  -- many fields here...
);

TABLE goals (
  id -- PK,
  game_id -- FK
  -- many fields here...
);

Many times (using an ORM) I'm having complex queries like:

SELECT
  -- many fields here...
    "goal"."id",
    "goal"."game_id",
  -- many fields here...
    "game"."tournament_id" AS "game__tournament_id",
  -- many fields here...
    "game__tournament"."id" AS "game__tournament__id",
    "game__tournament"."player_id" AS "game__tournament__player_id",
    "game__tournament__player"."trainer_id" AS "game__tournament__player__trainer_id",
  -- many fields here...
    "game__tournament__player"."id" AS "game__tournament__player__id",
  -- many fields here...
FROM
    "goals" AS "goal"
    LEFT JOIN "games" AS "game" ON ( "game"."id" = "goal"."game_id" )
    LEFT JOIN "tournaments" AS "game__tournament" ON ( "game__tournament"."id" = "game"."tournament_id" )
    LEFT JOIN "players" AS "game__tournament__player" ON ( "game__tournament__player"."id" = "game__tournament"."player_id" ) 
WHERE
    ( "goal"."game_id" IN ( 1, 2, 3 ) )

and all this only because I need the trainer_id in my service logic that is working with goals.

What do you suggest?

Should I create a column for trainer_id in goal table too? Isn't this repetition bad?

Is there another way to avoid that complex queries?

Shadow
  • 33,525
  • 10
  • 51
  • 64
Fred Hors
  • 3,258
  • 3
  • 25
  • 71
  • 1
    Please clarify via edits, not comments. PS There are 3 question marks at the end of your post – philipxy May 10 '22 at 23:14
  • See my 1st comment. Pick & ask 1 question. PS These questions are faqs (even when off-topic). (A "duplicate question" normally means the same question or same-answer question asked on 1 site. I'm not referring to your earlier cross-post to another site.) Please before considering posting read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. If asking reflect research. [ask] [Help] – philipxy May 10 '22 at 23:17
  • [Can I use a counter in a database Many-to-Many field to reduce lookups?](https://stackoverflow.com/a/45415509/3404097) [Cross Table Dependency/Constraint in SQL Database](https://stackoverflow.com/a/45189447/3404097) [Storing "redundant" foreign keys to avoid joins](https://stackoverflow.com/a/40563731/3404097) (etc etc) – philipxy May 10 '22 at 23:30

1 Answers1

1

Well, it could be violating one of the normal forms depending on full table structures.

However, one of the aspects of databases is that adherence to strict design is sometimes unfeasible.

The situation with multiple frequent joins can be pretty unreasonable both in terms of complexity and speed.

In addition, you are lucky in that a goal is more or less static information. It is done by specific player at specific game and afterwards no information about that goal usually changes.

So, what I would probably do is create anoter -- lookup table -- with all relevant information that you use frequently, already inserted.

Create table goal_lookup
( Id, goal_id, player_id, trainer_id, ...);

Note also that if you are using full references to player table, as in your original design, then, unless you record trainer with goal, you might be getting wrong trainer ID for goal, when the trainer of the player has changed since that game!

Gnudiff
  • 4,297
  • 1
  • 24
  • 25
  • This is an example. In my situation the `player` does not change the trainer anymore after that goal. Is there another way without a new table? – Fred Hors May 10 '22 at 21:57
  • @FredHors Well, you can create views for your frequently used fields with all the joins inside there, so that the joins needn't be done in ORM calls. That might reduce complexity, but multiple joins can soon impact query speed, the more rows your table will have. I would probably go ahead and use trainer ID in goal table. I am often dealing with a midrange warehouse system -- a commercial product that runs on Oracle and is used in some national grocery chains. They are not shy about putting a duplicate field in biggest tables so that they get more speed. – Gnudiff May 10 '22 at 22:05
  • Yeah, this is my fear/shame. Duplicating columns because it LOGICALLY doesn't make sense! – Fred Hors May 10 '22 at 22:07
  • @FredHors Consider it this way. Goal is not an object. Goal table contains log entries for events. It is logical that you record all the needed info about the event in the log. – Gnudiff May 10 '22 at 22:09
  • yes. But in this case after one or more goal of the same game that player doesn't change anymore (this is just an example, don't think about real concept). – Fred Hors May 10 '22 at 22:17
  • Yes, I read that. However doesnt matter. There are only the solutions outlined, unless I've missed something. The issue is by now in your perception. Rest assured, it's good you are concerned about database cleanliness, but they are messy beasts in real life. :) – Gnudiff May 10 '22 at 22:27