Which of the following SQLite database methods more efficient in storage usage and lookup speed?

Question

I am creating an android app to store data of a card game, and I want to save each match data (Player profile IDs and player scores). I am using SQLite databases for that. I want to sue this data to look back older matches or just load up an unfinished one.

Currently, I store each match data in a separate table, with table name of current date. I store the 4 player ids as int and 4 player scores as byte array (scores for each round).

I was thinking that I could store these data in a single table like this: A column for match date, 4 column for player id-s as int, 4 column for player scores as byte array.

Or like this: A column for match date, column for player ids combined as string, column for player scores combines as string.

Or like this: A column for match date, column for player ids and scores combined as string

Answer 1

I'll try to actually answer your question with this one.

I was thinking that I could store these data in a single table like this: A column for match date, 4 column for player id-s as int, 4 column for player scores as byte array.

I think this is the closest one. But I think it could be expanded a little. I don't actually know what data you're working with so I have no idea how player_scores should be represented, but I'll roll with a byte array.

• match table: matchId (int), matchDate (timestamp or date type) (and any other columns for metadata like a nickname for the match, a type if there are different types of matches etc )
• score table: matchId, scoreId, playerId, playerPosition, score

And here, score is the byte array.

Then to query:

SELECT score.* FROM score  
JOIN match ON score.matchId = match.matchId
WHERE match.matchDate > /* start_date_for search */
AND match.matchDate < /* end_date_for_search */

Then you would get back four rows per match if you had four players in a match & you could loop over those for rows for the player-specific data.

Answer 2

Conclusion

It's going to be as complicated as you want it to be. Think about what kind of questions you want to be able to ask your database. I set out to answer a very hard question, which would have involved another significant change to the proposed schema or a much more complicated query involving sub-queries, or maybe just SQL functions that I'm not very familiar with. I needed to do more research.

I can usually figure this stuff out if I have too, but I'm not a database expert, so it's far from simple for me. I thought I was pretty good with databases until I got into the nitty gritty on this one lol.

Hope I didn't confuse you too much. I seriously thought about erasing my answer, but I put all this time into it. I gave you a bad answer. But I hope it's helpful anyway.

Woah

So, I set out to help & answer your question, then I started having fun with it, then I realized I was in over my head. So please take my lengthy answer lightly & don't overcompmlicate it for your current knowledge & skill level.

As far as databasing goes, what you're trying to do is pretty simple, but I understand how overwhelming & difficult databases can be before you get the hang of them.. Keep reading & you'll see why I struck this. If you stuck to the final scores, then it wouldn't be too complicated. If you want to re-load a game where it left off, it might get pretty complex.

In code, there are so many ways you can do the same thing, and there's rarely one "right" answer. Anyway, I'll comment on the approaches you've suggested.

the problem

You have a card game, which you're representing in a database as a match_date, player_profile_id, and player_score. There are four players per game. And you want to look at or load up old matches to continue them.

Options you gave

I store each match data in a separate table, with table name of current date. I store the 4 player ids as int and 4 player scores as byte array (scores for each round).

Having a separate table for each game is... clunky, to say the least.

I was thinking that I could store these data in a single table like this: A column for match date, 4 column for player id-s as int, 4 column for player scores as byte array.

Good. Switching to a single table is a huge improvement, but the data is still compacted in formats that are not query-able, and having different columns for each player is not necessarily ideal, but it would serve the task at hand.

A column for match date, column for player ids combined as string, column for player scores combines as string.

I like that you're trying to condense the table and not have things so wide. But condensing the data in this way pretty much defeats the purpose of a database. You wouldn't be able to query for specific information, and you'd probably lose performance because your Android code (Kotlin, right?) would have to unpack the data. However I don't have benchmarks to prove that.

A column for match date, column for player ids and scores combined as string

This, as with the previous one, again tightly couples your database to your Android code. The database should be doing the data-base job & the Android code should be doing the Android job. What I mean is that your database should be portable without complicated logic to extract the information you need. The query should extract the information you need.

My suggestion

It's good that you're considering different ways to do things, and it sounds like you have a solution that is currently doing the job. What you will likely have, though, is growing pains. I'm gonna add a whole lot of database stuff now that you don't need to implement at this time. Sometimes it's better to do what you have the skills for now so that you can get the current task done. Sometimes it's better to do the complicated thing you don't know how to do so you can learn. That depends on your situation.

So. We're going to have round and player and match.

The schema

round table:

id | match_id | player_id | round_number | hand /*at the start of the round*/ 
87 |   3      |   96      |      0       | ["ace of spades", "kind of hearts", "7 of clubs"]
99 |   3      |   85      |      3       | [...]

and so on
Then your player table would have columns for name, id, etc. Your match table would contain the date of the match, a match id, and any other match data

Critiques of my solution

• If you want to query like "How many times were games on round 7 while a player had an ace in their hand and no kings were left in the deck", then you'll need to unpack hand into it's own table as well.
• I personally prefer camelCase column names over under_score names. But I think under_score is more commonly used for databases. I have nothing to back that up.
• UUIDs are a thing. I haven't personally started using them and it doesn't seem necessary for your use case.

Let's get confused

I want to answer the question: "How many times were games on round 7 while any player had an ace of hearts in their hand, no player had a 2 of clubs, and no kings were left in the deck?"

• deck_start table has the columns: matchId, cardName, cardFace, 'index'
• match table has the columns: id, name, date, type (if there are different types of matches)
• card_in_hand table has the columns: id, matchId, playerId, roundNumber, cardName, cardFace

So to answer the question

How many times were games on round 7 while any player had an ace of hearts in their hand? no player had a 2 of clubs, and no kings were left in the deck?

SELECT COUNT(DISTINCT match.id) FROM match
JOIN card_in_hand ON match.id = card_in_hand.matchId
WHERE card_in_hand.roundNumber = 7
AND card_in_hand.cardName LIKE 'ace'
AND card_in_hand.cardFace LIKE 'heart'

Or something like that. I struck the second part of the question... The more I thought about it, the more complicated it got. You'd have to either keep track of the full deck on each round, or use count of distinct cards in hand to figure out your deck index. Or maybe an approach where you keep track of the starting deck, then keep track of every single play. That's probably the one, really. If you track every single play.

play table might contain id, matchId, playerId, roundNumber, sequenceNumber, action

And then you might have an action table or otherwise further expand action.

You also might want to keep the id column in the match table as matchId and not just id. If you start doing joins & you want to get the correct id (match v round v play v whatever), then you'd be better off having it as table.tableId instead of table.id

Answer 3

Typically, you would have one table which stores "the match," and a second related table which stores each one of the scores. They would not be "combined as a string."

That being said, however, it's really up to you: if your application is always going to just want the scores, all at once, and you don't intend to query against them, you could do it that way.

As you've correctly guessed, however, you do not want to have "a table per match."