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
Here is an entity-relationship diagram of the whole schema. If you are wondering where
cards_owned_by_players went: note that an N:M relationship needs to be represented with a separate relation-table.
player owns m
card is owned by n
player has n
deck is always owned by one
deck has n
card is in m
players would have the primary key
playerId. It contains all the information about the player themselves (like the player name).
cards_owned_by_players manages the ownership relation of individual cards. Its primary key would be
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
cards to this query. More about 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).
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_in_deck would have a compound primary key of
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
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
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.