I've been developing simple Android app that will let me store players, games and results so I can then display them with some statistics (like results, games played, last match date, wins, losses etc). I've been thinking about the best approach. I've came up with few ideas and then I started wondering which would be the best performance wise. How to store that kind of data? What is the best way to manage SQLite queries?
I have a DB containing 3 tables PLAYERS_TABLE, GAMES_TABLE and RESULTS_TABLE. Results table looks like this:
| MATCH_ID | GAME_ID | PLAYER_IDS | SCORES |
|---|---|---|---|
| 0 | 1 | 1,2,3 | 45, 33, 24 |
| 1 | 1 | 3,2,1 | 61, 43, 36 |
| 2 | 2 | 1,2,3 | 55, 48, 40 |
Now what would be the best way to get how many total matches each player participated? My initial idea was to just get PLAYER_IDS from whole RESULTS_TABLE table then just for loop each record and check if it contains the player ID / game ID I'm interested in.
And then the first problem came to me. What If the table grows to let's say 500-1000-2000 records? is this the best way to do this? Is getting whole table just to get portion of the data efficient. What if I remove a player for the database? Should I check all records in RESULTS_TABLE and updated them or maybe when displaying scores for specified match check if player exists and simply ignore it keeping some useless data in DB?
So I came up with another idea. I added two more columns to PLAYERS_TABLE that keeps date of last match and a counter for all matches played. It solves a problem with counting played matches and checking when the player last played but then what If I remove a game for the database everything would have to be recalculated.
What made me thinking again. What if I would instead store results of each player separately like:
| PLAYER_ID | MATCH_ID | GAME_ID | SCORE |
|---|---|---|---|
| 1 | 1 | 1 | 45 |
| 2 | 1 | 1 | 61 |
| 3 | 1 | 1 | 55 |
There would be like 3 or 4 times more records in the table but this way if I would ever remove a game or a player there wouldn't be any need to updated anything. Or if I wanted to count all matches played by a specified player queries would be more specified: no need to get whole table and iterate through it looking for specified data.
Or maybe there is another way I can't wrap my head around? Anyone care to elaborate and share some insight?
