Database structure for a trading cards video game (TCG)

Question

Just started and after pondering with registered users' table everything came to a halt.

So far I've got:

+----------------------+
| tblUsers             |
+----------------------+
| userID(primaryKey)   |
| username             |
| password *           |
| accountStatus **     |
+----------------------+

* hashed
** 0=not activated, 1=activated, 2=suspended

Still haven't decided on each of the field's data type. But the question is: should I have one big massive table for all the cards or split them into say tblCreatures, tblSpells, tblTraps, tblQuests tables?

Plus, for example, some of the creatures have special abilities besides the usual strength and health stats. So I assume if there was tblCreatures, it would have cardTitle, cardStrength, cardHealth, cardDescription and then it would have cardAbility0...4, because I doubt that a single creature card can have more than 2 abilities?

Also how would I contain each user's decks? A single user for example can have up to 10 decks created by themselves, which will be saved on the server.

Any suggestions/remarks?

The project will either be purely written in C# (both client and server) and XNA4, or maybe in the future the client will be ported to Flash+AS3 while server will be done in C#.

Answer 1

I'm not familiar with TGC. I assume you trade cards and each card can be categorized into one of four types: tblCreatures, tblSpells, tblTraps, tblQuests

If the four types are very unrelated, then I would split them into four tables, so you can manage each type independently, much like you outlined your tblCreatues. You would need to add a cardID to each of these four tables, and this ID should be unique amongst the four tables (don't use the same ID in more than one table).

You would need a tblDeck, such as:

• deckID
• cardType
• cardID

'cardType' would tell you which of the four card type tables to search in. 'cardID' would be the index into that table. ('cardID' doesn't NEED to be unique like I mentioned above, but it would make things cleaner IMO)

I'd keep your tblUsers much like you outlined, with a unique userID.

Lastly, each user can have multiple decks, so you need a new table to join the two: tblUserDeck:

• userID
• deckID

Answer 2

From a DB programmer passing by...

It depends how different are your items. I think for your case, that you should have a main table, for all main shared fields, and additional specific tables for each specific field.

The main table, must have a special field that indicates what kind of specific table relates that item.

When you read info from the database you read the main table, and later, if applies, bring additional specific info for that card.

Answer 3

I'm working on a TCG myself, so I have a little bit of experience with it.

I would have a base "Card" table which would have the columns common to all cards, such as Name, Description, CastingCost or whatever.

Then for each type you'd have the type specific columns which would foreign key to the Card table. The CardId can be both PK and FK in the Creature/Spell/Trap/Quest table.

You can either have an explicit "CardType" column, or you can simply say that if the CardId is in the Creature table, then it's a creature.

As far as decks go, I have a CardInstance table which represents a specific card instance that a user owns, then I have a table called joining table between that and Deck (For lack of a better name, it's Deck_CardInstance) that represents a card in a specific deck. This allows a specific card instance to be in multiple decks.

You may or may not want this, but if someone wants to share a rare card they own between decks, they're going to do so by swapping which deck the card's in anyway. Might as well make it less annoying for them. The other benefit of this is that a CardInstance may be in no deck. The user hasn't organized it into a deck yet.

In this setup it's assumed that a deck and a card instance belongs to a single user, which seems like a safe assumption.

Below is a database diagram similar to what I'm using but simplified to your example. (For instance I don't know if you just have ability names that match to abilities in code or if you have data-driven abilities stored in the database.)

Answer 4

There are a bunch of questions there, but I'll just deal with one. I'd recommend making Abilities a different table and making a many-to-many table that connects cards with Abilities. It's common in TCGs to have multiple creatures with the same ability, and Ability0, 1, 2, and so on is sort of clumsy and limits your future options.