• 1

• 59.7k
• 9
• 157
• 311

#Games and Players

I imagine that one of the most common statistics you would want to display is: "What are my win statistics against player X?". For such a query, the table game_master is really not optimized for it.

Let's say that you want to make a query for "Find all game_ids that the user id 6354 has played in"

SELECT game_id FROM game_master WHERE player1 = 6354 OR player2 = 6354

UGH! Considering that usually you don't have the user id and only a user name, you would have to make two joins here to join with the player table.

Better would be to have a many to many relationship between game and players. One player can play in many games, and one game can contain many players. The fact that one game only contains two players (at least for now) is irrelevant. Either it is one, or it is many.

So create a games_players table for this relationship. It can contain: game_id, player_id, result. result can be an integer to support up to any number of players in the future. result = 1 for the winner, result = 2 for second place, result = 8 for the "biggest loser" (no offense if you would end up here) in an 8-player game, etc.

Now let's make this query again:

SELECT game_id FROM games_players WHERE player_id = 6354

Ah, much better! It can even be extended to find the number of wins and losses in the same query without too much effort. Can also easily join with the player table to find out the player id for a specific player name.

#Game Actions

turn INT NOT NULL,

If you would consider "End Turn" itself an action, then this "turn" integer isn't really needed.

action_attack INT NULL,

Umm... I'm sure there are other possible actions except attack, right? And what is the exact purpose of this column? How much attack damage that was dealt? It has a very confusing name and I'm not sure you know yourself what it should be used for.

I don't see an "action_id" or "action_name" anywhere here. If one card at any point have multiple possible actions, then an action_id column is required.

#Action Targets

target_player INT NULL,
 FOREIGN KEY (action_player) REFERENCES player(id),
target_card INT NULL,
 FOREIGN KEY (action_player) REFERENCES player(id)

What about actions that can target more than one card? What about actions that can target more than one card in multiple different "targetSets"? Consider for example the Magic: The Gathering card Glimpse the Sun God:

Tap X target creatures. Scry 1. (Look at the top card of your library. You may put that card on the bottom of your library.)

What is needed to store about this action? We know that the card played was a "Glimpse the Sun God", we have the card id, player id, action id. Now, we need to store the value for "X", and we need to store the actual creature targets, and we need to store whether or not the card on the top of the library was put at the bottom of the library or not.

Other cards, such as Gods Willing needs to store information about what color was chosen. Then there's cards such as Cryptic Annelid that has multiple instances of Scry. Which cards was put on the bottom of the library in each Scry? In which order were cards put on the top?

Now you might not want to support Magic: The Gathering. But if you do, or want to support games like it, or simply want to support actions which can have more than one target (A kind of mulligan at the start of the game where players can choose individual cards that should be switched perhaps?) then having simply a target_player and target_card is not enough.

Speaking of target_player and target_card. Don't separate them. Inside the game, give players an id and give cards an id, make sure there are no conflicts for the ids of course, then simply store the target id.

#Mod Cards

You have this 'mod' table in the database, without much explaining about what it means. I assume this means that the database should be able to be used for more than one kind of game. If that is the case, let's take a look at the card table.

scrap_cost INT NULL,
scrap_value INT NULL,

Does all mods use those values? What about a Magic: The Gathering-style mod which uses more complicated mana costs? How to store a mana cost of 3 uncolored mana, one blue, one white and one green mana, and an additional mana cost of X? Or to make things worse, Reaper King where you can pay either uncolored mana, or colored mana?

#Conclusion

Targeting

Trading Card Games is tricky business. As I sincerely hope that no actual game logic will performed by the database (No stored procedures for "Card X attacks Y and deals 3 damage which causes Card X to die"), you might want to restrict the level of detail that the database will store. Given the complexity of the targeting for advanced actions, perhaps store the information about all targets in a serializable format. Let's say for example that Glimpse the Sun God was played:

Tap X target creatures. Scry 1. (Look at the top card of your library. You may put that card on the bottom of your library.)

You store the value of X in a column extra_data (this can also contain the chosen color for actions that want that, so it needs to be text). Then let's say that the target creatures were 41, 42, and 43. And that the result of the scry was to put on bottom of the library. To store this information we can serialize it to something like this: 41,42,43;BOTTOM or 41,42,43;63; (indicating that 63 was the chosen card to put on the bottom of the library, and that no card was put on top - indicated by an empty string after the last semicolon).

Now, you won't be able to SELECT * FROM game_actions WHERE target_card = 42; anymore, but you even do that before? What would that give you if you don't know the context of it all and have no game logic nearby? To me it seems better to put that on the Java side of things, as you say JDBC is involved. If JDBC fetches the information about all performed actions from the database and is able to replay the actual game events, then Java can filter out and scan for actions where target 42 was included.

Mod Cards

Again, you might want to not store a high level of detail in the database. Probably not all cards have scrap_value. You might want to store these 'values' in a serializable format and letting the application code retrieve that, deserialize it, and filter out some specific cards, instead of doing a SELECT * FROM card WHERE scrap_value = 5. I am aware that it will affect performance, but it will improve flexibility. Another alternative would be to use a card_values many-to-many relationship for these values. In this particular case, I can see some reasons for using the many-to-many relationship, but I am not entirely convinced that is the best option.