data structure - Where and how to store players collection in a computer card game?

I am developing a trading card game ( something like Hearthstone but not as complex) and I am faced with the following problem: I don't know what is the optimal way to store players collections and decks (the cards that they have available).

I thought of storing their collections on a local file on their device, but that seems bad, as they could probably modify that file and get themselves cards that they shouldn't have.

The second idea is that i could save all their decks and collection in a database. But having one table for each player isn't possible. Having one table with all that data something like PlayerCards(id, cardId, playerName, deckName) seems like it might work, but the table would be huge, and containg quite a bit of redundant data (cards that are in the same collection but are in multiple decks).

What would be the proper way to do it?

Answer

The relational-database-by-the-book solution would have a table players, a table cards_owned_by_players, a table decks a table cards_in_deck and a table cards.

Here is an entity-relationship diagram of the whole schema. If you are wondering where cards_in_deck and cards_owned_by_players went: note that an N:M relationship needs to be represented with a separate relation-table.

• A player owns m cards


• A card is owned by n players



• A player has n decks


• A deck is always owned by one player


• A deck has n cards


• A card is in m decks

The table players would have the primary key playerId. It contains all the information about the player themselves (like the player name).

The table cards_owned_by_players manages the ownership relation of individual cards. Its primary key would be cardId and playerId. If a player can own more than one copy of a card, it would have a value-field count. To get all cards owned by a player, you can do SELECT cardId FROM cards_owned_by_players WHERE playerId = [id]. If you also need additional information about these cards, like their artwork or name, you would add a JOIN with cards to this query. More about the table cards later.

The table decks would have the primary key deckId. It includes all the information about the deck itself (name of the deck and the Id of the player who owns it).

The table cards would have the primary key cardId and include the information about the cards themselves (name, description, artwork, functionality). You have one entry per type of card. I.e. if you have a card "Goblin Warrior" which is owned by 1752125 players and is in 2357689 decks, you would still only have one row for it in cards.

The table cards_in_deck would have a compound primary key of deckId and cardId. If your game allows multiple copies of a card in one deck, the value field would be count. When it doesn't, that table might not actually need any fields at all except the primary key.

As an example query, let's say you want the deck names and card names of all cards in all decks by a specific player which you only know by name "Bob". You would then do the query:

SELECT decks.name, cards.name, cards_in_deck.count
       FROM players
       JOIN decks ON players.playerId = decks.playerId
       JOIN cards_in_deck ON decks.deckId = cards_in_deck.deckId
       JOIN cards ON cards_in_deck.cardId = cards.cardId
       WHERE players.name = "Bob"

A JOIN over 4 tables looks like it could be a lot of work for the database, but notice that they are all JOINs on primary keys. Most database management systems optimize heavily for primary key access. The slowest parts of this query will likely be WHERE players.name = "Bob", because that will require a full table scan of the players table, unless you have an index on the name field.

Also, don't be afraid of your cards_in_deck table growing too large. We are living in the age of big data. Many database management systems are capable of handling tables with billions of rows and terabytes of data... as long as all queries on them are using primary key or index access.

---

An optimized solution:

If you ever meet the ghost of Edgar F. Codd, please don't tell him I wrote this.

If you are sure you will always query only for the complete content of a deck and never query for individual cards in a deck, you can remove the table cards_in_deck and instead serialize the deck content into a binary representation and put it into one BLOB field of the table deck. You will no longer be able to do queries like "all players which have card X in their deck". Also, making a change to a deck will now require to get the whole BLOB, deserialize it, change it, serialize it, and write it back. But it will be a lot faster to get the whole deck of a specific player.

You can now of course no longer do a JOIN with cards. But you might not have to do that. You won't have that many different cards (even Hearthstone only has about 2000), your game mechanics will constantly need them, and your card information will only change when you make a major update to your game. So it might be better to keep the card information constantly in the game server's memory instead of re-reading it from the database all the time.