deebow's avatar

Separate table over json column

Hello,

I have a Game, Room, Athlete, and Round entities.

Game -> Many Rooms
Room -> Many Athlete
Athlete -> Belongs to many Room

EDITED:
WRONG: (Room -> Many Rounds) 
CORRECT: Game -> Many Rounds (That means, all rooms in the game all have the same rounds)

So the tables for that would be:

games
rooms
athletes
athlete_room
rounds

now, what If athletes in the room could gain scores for each round.

so then I would have athlete_room_rounds

So let's put in some numbers and do the math, for daily metrics:

  • Average 15 games daily
  • Average 100 rooms per game
  • Average 15 athletes per room
  • Average 10 rounds per game

So that would be:

15 games x 100 rooms x 15 athletes * 10 rounds = 225,000 rows daily

My question are:

  1. Is 225k rows daily considered heavy?
  2. Should I go with athlete_room_round table? or should I simply add new round_scores json column in athlete_room?
  3. What are my other options?
1 like
10 replies
Glukinho's avatar

If I understood correctly you need another relation Athlete -> belongsToMany -> Round with pivot table athlete_round.

Don't mess with complex pivot tables linking more than two tables, Eloquent don't support them as far as I know. Stick to simple standard relations and combine them to fetch what you need.

Also json column is bad idea for you. Data must have a strict structure, this is general rule.

Well thought indexes and constraints are vital for you.

Also, what is "scores"? Is it another entity/model or just calculated value based on results of game/round?

1 like
deebow's avatar

Scores are not entity, it's simply a score a player gets per round.

1 like
deebow's avatar

Hello @glukinho , I incorrectly defined the relationship of Round. Please see updated version above.

1 like
Glukinho's avatar

It doesn't matter, my answer is the same: create proper relations and use them. Don't use complex tables like athlete_room_round and don't use JSON columns unless you absolutely need them.

About performance, you should note that your app should handle peak load, not average. So, 225 000 rows/day is roughly 3 rows/second, but in peaks your app should be able to handle x10, I'd say 30 rows/second. Is it much or few depends on your servers performance and database structure.

1 like
martinbean's avatar

@deebow This seems like it’s a follow-on question to https://laracasts.com/discuss/channels/laravel/two-way-many-to-many-polymorphism-table-structure-suggestion. It would really help if you just said what space you were working in, and then we could better answer your questions on your entity and database schema modelling.

now, what If players in the room could gain scores each round.

Well then that sounds like you have some sort of “game” or other event players participate in. But this is just a guess based on what I mentioned above. So you’ll need to have some sort of entity for this game or whatever, that you attach players to, and also designate what “round” the game was played, so that you can then record any points scored in that individual game.

1 like
deebow's avatar

Hello @martinbean

First of all, apologies for not sharing the full context.

In the previous post, I ended up with a polymorphic pivot game_participants, where participants could either be athlete or club. This pivot table acts as the catalogue from which players select participants to build their team.

A Team is also composed of participants chosen from that catalogue (game_participants), so I have another pivot table: team_participants.

My challenge now is how to assign a score to each team_participant for every round. I’m considering two approaches:

  1. A dedicated pivot table, e.g. team_participant_round, referencing both team_participant and round.
    • My question: Is it acceptable or common to have a foreign key referencing the primary key of another pivot table (in this case, team_participants.id)?
  2. Use a JSON column (e.g. round_scores) within team_participants, storing key–value pairs where the key is the round ID and the value is the score.
1 like
deebow's avatar

JUST HOW DUMB AM I?

Why would I even need a team_participant_round table when I already have game_participants and rounds tables. I can simply record per-round participant scoring in a game_participant_round table.

That way, I avoid creating a bulky team_participant_round table that would explode in size by a factor of (number of teams created × number of participants per team × number of rounds per game).

Thanks @martinbean for pointing what I missed out:

So you’ll need to have some sort of entity for this game or whatever, that you attach players to, and also designate what “round” the game was played.

2 likes
martinbean's avatar

@deebow Drop me a DM on 𝕏 (@martinbean). I’ve worked with complicated sports and competition schemas in the past so happy to lend a hand and advice if you’d like.

1 like
mileswebhosting's avatar

225k rows/day is nothing scary for a modern relational DB.

For your use case, athlete_room_round (or scores) as a proper table is the right design, not a JSON column.

You can always add caching/summary tables later if analytics get heavy.

1 like

Please or to participate in this conversation.