Which database model is more efficient

Question

I'm designing a league system for a game.
There will be many leagues and a league will include 50 players.

I have two alternatives:

1#

Table player{  
  playerid number primary key,
  leagueid number foreign key,
  score number
}

Table league{   
  leageuid number primary key  
}

Whenever a player with leagueid "x" wants to get the current leaderboard.

1. The backend will select all players with leagueid "x"
2. Sort the data using the player's current score and return.

2#

 Table player{  
      playerid number primary key,
      leagueuid number foreign key,
      score number
    }

 Table league{   
      leagueid number primary key
      playersList array of playerid's
    }

Whenever a player with leagueid "x" wants to get the current leaderboard.

1. The backend will select the league with leagueid "x"
2. For each playerid in "playerList", get the current score of "playerid" from the "player" table.
3. Sort all the scores and return

Which of the alternatives is better? If there is a more efficient way, please let me know.

Answer 1

If you use solution #2, you have redundant information in league.playersList.

What should you do if you discover one day that that two leagues have the same player in their list? How can you be sure which of these is correct?

What if you then check the same player in the player table and find that it references yet a third league, and that league doesn't even list the player in its playersList? Now how can you be sure any of your data is accurate? You'd have to do a complete search of the playersList of every league, and compare it to the leagueid in that player's record.

How did it happen? Will it happen again? How will you know if it happens again? Do you need to re-verify all the playersLists against their respective player records every day?

This shows the problems you get when you store values in "lists" in a database. You end up with denormalized data, and this creates a lot of potential error cases that you need to watch for and inevitably you need to fix.

Use solution #1. Put an index on player.leagueid to make the search efficient.

---

Re your comment:

Performance issue ... Selecting the players with the same "leagueid" and sorting them every time a user views the leaderboard?

That's where an index would help you greatly. The purpose of the index is to allow your search for leagueid to find the matching rows without having to read all 1 million rows.

A compound index on (leagueid, score) would help even more by avoiding the sort. By ensuring that the index returns rows in the order you want to sort anyway, the sort becomes a no-op.

You might like my presentation How to Design Indexes, Really, or the video of me presenting it.

---

P.S. See my answer to Is storing a delimited list in a database column really that bad? for more problems with storing values in lists.