More data in 1 row or less?

Question

Im currently making a tool for my esports team and I'm curious how I should store/access some data. My data looks currently like this for 1 game:

Match(id (Primary Key), game_id, team_id, blue, game_date, duration, game_type, win, ban_id1, ban_id2, ban_id3, ban_id4, ban_id5, ban_id6, ban_id7, ban_id8, ban_id9, ban_id10, pick_id1, pick_id2, pick_id3, pick_id4, pick_id5, pick_id6, pick_id7, pick_id8, pick_id9, pick_id10, few_more_team_specific_data)

Performance(id (Primary Key), puuid, match_id, few_more_player_specific_data)

I will create 2 entries of Match for each game (1 for each team). Every team has 5 bans and 5 picks for an ingame character (total 10 bans and 10 picks). Later I want to read the data to know how often a character was banned or picked in total. Of course since there are 2 entries the bans and picks are the same in both entries.

Should I then:

1. leave it as it is because to get all bans/picks I would have to only request "SELECT ban_id1, ..., ban_id10, pick_id1, ..., pick_id10 FROM Match WHERE team_id = ?" to get all data needed.

2. store it for each team seperately: "SELECT match_id FROM Match WHERE team_id = ?" and then for each "SELECT ban_id1, ..., ban_id5, pick_id1, ..., pick_id5 FROM Match WHERE match_id = ?"

3. make a different setup with 3 Tables: Match(id (Primary Key), game_id, team_id, blue_team_performance_id, red_team_performance_id, game_date, duration, game_type, ban_id1, ban_id2, ban_id3, ban_id4, ban_id5, ban_id6, ban_id7, ban_id8, ban_id9, ban_id10, pick_id1, pick_id2, pick_id3, pick_id4, pick_id5, pick_id6, pick_id7, pick_id8, pick_id9, pick_id10) - Teamperformance(id (Primary Key), team_id, win, few_more_team_specific_data) - Performance(id (Primary Key), puuid, team_performance_id, few_more_player_specific_data)

The amount of data is around 100,000 - 400,000 match and around 200,000 - 800,000 individual performance entries.

Answer 1

It is generally a bad practice (in schema design) to spread an array (eg, bans or picks) across columns. It is better to make another table with one row each.

The resulting queries will involve JOINs.

And they may involve GROUP BY and GROUP_CONCAT(...) for stringing the, say, picks into a commalist.

If there are actually up to 5 (or 10) thingies, then the one-row-per has the advantage of taking fewer rows when there are fewer than 5 (or 10). Also, it allows going beyond 5 (or 10).

A million rows, assuming decent indexes, is rarely a performance problem.

Work out what the SELECTs will look. That will help you decide on the schema design. Show them to us for tips on indexing.

(Suggest calling the table Matches since MATCH is a keyword in MySQL.)