# 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?

• Did you run the numbers on this? You are very likely overestimating how much data you need. – ratchet freak Mar 17 '17 at 15:07
• Let's see, playerId -> 64 bit, cardId -> 32 bit, playername -> 8 bit * characters, deckName -> 8 bit * characters. If we give a player an average name length of 15 and an average deckname length to 20 and 100 cards, then we get approx. 443 bytes per player. For worst case scenario let's round that to 512 bytes (half KB), this way you can store 2,097,152 players in a gigabyte. – Bálint Mar 17 '17 at 15:18

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.

• Oh no, an answer battle between Philipp and Josh Petrie by 20 seconds :) – lozzajp Mar 17 '17 at 16:33
• Thanks a lot for the detailed answer. Do you think it's a bad idea to store some of the cards information (such as description and artwork) on the client to reduce the amount of data i send from the server to the client? – Andy Mar 17 '17 at 18:48
• Also, what about the cards that are part of a players collection, but aren't part of any deck? Should i have a table collections(collection, userId) for all the cards? – Andy Mar 17 '17 at 19:29
• @Andy Description and artwork are very likely irrelevant for the server, so you should store these only on the client. – Philipp Mar 18 '17 at 0:17
• @Andy I updated the answer to account for the player's card collection. – Philipp Mar 23 '17 at 13:13

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.

Correct; if you care about the player being able to tamper with their collection of cards, you don't want the authoritative collection of cards stored on their device. If their devices are computers, it's pretty easy to access that data for modification. If their devices are phones or tablets, it's a little harder, but still possible.

The second idea is that i could save all their decks and collection in a database.

This seems like the ideal solution, especially if your game already has a multiplayer component where you might want to stand up a central database for match scores or tournaments.

Assuming you have a Cards table (describing every card in the game) indexed by CardId and a Players (describing every player in the game) table indexed by PlayerId, you only need a two- or three- column additional table to represent who owns what.

This Inventory table describes which cards every player has by using a player ID column and a card ID column, such that one row of this table is indicating that (for example) player 90 has card 43. If a player can possess multiple cards you can add a third column for Count.

You will have a large number of rows in this table if you have a large number of players and a large number of cards. It won't be redundant data though; it's the minimum amount of data you need. The "larger" chunks of data, stuff like the player's name, the card's name or stats, all of that lives in the Players or Cards tables where it isn't duplicated.

• Thanks a lot for the answer. What about the decks? Should i go for a table decks(deckId, playerId, deckName) as mentioned in Philpp's answer? – Andy Mar 17 '17 at 19:33
• Sure, that kind of layout can work fine. – user1430 Mar 17 '17 at 19:59