Trading Card Game database schema for statistics

Question

This database schema will be used in conjunction with various systems as related to an online Trading Card Game, mostly for permanent storage and statistics. Some example cases would include:

• JDBC calling the DB to get information about player decks, or returning after-game stats;

• Website displaying stats relating to the game via JavaScript, PHP, etc.

Any advice on naming, table relations and such are appreciated.

START TRANSACTION;
DROP SCHEMA IF EXISTS cardshifter_stats CASCADE;
CREATE SCHEMA cardshifter_stats;
SET SEARCH_PATH TO cardshifter_stats;

CREATE TABLE player 
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    password TEXT NOT NULL, -- this will be made secure once implemented
    email TEXT,
    website TEXT NULL,
    about TEXT NULL,
    create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    delete_date TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NULL,
    last_seen_date TIMESTAMP NULL
);
CREATE TABLE mod 
(
    id SERIAL PRIMARY KEY,
    name TEXT NOT NULL,
    description TEXT NULL,
    owner_player_id INT NOT NULL,
        FOREIGN KEY (owner_player_id) REFERENCES player(id),
    create_date TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NOW(),
    delete_date TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NULL
);
CREATE TABLE player_mod 
(
    player_id INT NOT NULL,
        FOREIGN KEY (player_id) REFERENCES player(id),
    mod_id INT NOT NULL,
        FOREIGN KEY (mod_id) REFERENCES mod(id)
);
CREATE TABLE card 
(
    id SERIAL PRIMARY KEY,
    version INT NOT NULL,
    name TEXT NULL,
    description TEXT NULL,
    effect_description TEXT NULL,
    type TEXT NULL,
    attack INT NULL,
    health INT NULL,
    mana_cost INT NULL,
    scrap_cost INT NULL,
    scrap_value INT NULL,
    sickness INT NULL,
    attack_available INT NULL,
    create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    delete_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NULL
);
CREATE TABLE deck
(
    id SERIAL,
    version INT NOT NULL,
    player_id INT NOT NULL,
        FOREIGN KEY (player_id) REFERENCES player(id),
    mod_id INT NOT NULL,
        FOREIGN KEY (mod_id) REFERENCES mod(id),
    card_id INT NOT NULL,
        FOREIGN KEY (card_id) REFERENCES card(id),
    card_quantity INT NOT NULL
);
CREATE TABLE game_master
(
    game_id SERIAL PRIMARY KEY,
    player1 INT,
        FOREIGN KEY (player1) REFERENCES player(id),
    player2 INT,
        FOREIGN KEY (player2) REFERENCES player(id),
    start_time TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
    end_time TIMESTAMP WITHOUT TIME ZONE
);
CREATE TABLE game_actions
(
    game_id INT NOT NULL,
        FOREIGN KEY (game_id) REFERENCES game_master(game_id),
    turn INT NOT NULL,
    action_player INT NOT NULL,
        FOREIGN KEY (action_player) REFERENCES player(id),
    action_card INT NULL,
        FOREIGN KEY (action_card) REFERENCES card(id),
    action_attack INT NULL,
    target_player INT NULL,
        FOREIGN KEY (action_player) REFERENCES player(id),
    target_card INT NULL,
        FOREIGN KEY (action_player) REFERENCES player(id)
);

COMMIT;

SELECT
    tables.table_schema,
    tables.table_name,
    columns.column_name,
    columns.ordinal_position,
    columns.is_nullable,
    columns.data_type
FROM information_schema.tables AS tables
    INNER JOIN information_schema.columns AS columns
        ON tables.table_name = columns.table_name
WHERE 
    tables.table_schema = 'cardshifter_stats'
ORDER BY 
    tables.table_name ASC,
    columns.ordinal_position ASC;

This is what the schema looks like:

Answer 1

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.

Answer 2

I really don't see much to comment on here. The code was easy to read, well structured, and the code style was, for SQL relatively easy on the eyes, and consistent, which is good.

One inconsistency I see is:

create_date TIMESTAMP WITHOUT TIME ZONE DEFAULT NOW(),
delete_date TIMESTAMP WITHOUT TIME ZONE NULL DEFAULT NULL,
last_seen_date TIMESTAMP NULL

yet the image of your schema clearly shows that last_seen_date is also created without the time zone. When I investigated, I found:

Note: The SQL standard requires that writing just timestamp be equivalent to timestamp without time zone, and PostgreSQL honors that behavior.

So, there is no need to specify WITHOUT TIME ZONE at all, or, if you do, you should do it consistently.

The create_date (or start_date) on all of your tables are defaulted to NOW(), but the columns are still nullable. CreateDate will never be null, and should be created as NOT NULL.

I am suspicious of the type column on card. Columns like that are typically a small domain (only a few values, with many repetitions). That should be normalized out in to a cardtype table, and the type column replaced with a type_id. This will potentially improve the performance and maintainability of the system. Of course, if your type has many different values with few repetitions, the normalization won't make sense.

Similarly, I am sispicious of the version column on deck. This is a value related to the application that does not make clear sense based on what you have presented. I can only guess what it will be used for, but, since the rest of the schema is clear, it stands out as being ... 'obtuse'.

Fnally, I expect that you are missing some indexes.

Postgres will index the primary and foreign key columns, but I suspect indices on player.name, mod.name, and card.name would be good (and consider making things like mod and card names unique....).

Answer 3

I applied pretty much everything that was suggested by @rolfl and @SimonAndréForsberg and very much simplified the schema. I had originally planned on storing actions in game_action but since the Java server is already storing all that, there was not much of a point.

Here is the simplified code:

START TRANSACTION;

DROP SCHEMA IF EXISTS cardshifter_stats CASCADE;
CREATE SCHEMA cardshifter_stats;
SET SEARCH_PATH TO cardshifter_stats;

CREATE TABLE player 
(
    id SERIAL PRIMARY KEY UNIQUE,
    name TEXT NOT NULL,
    password TEXT NOT NULL, -- this will be made secure once implemented
    email TEXT,
    website TEXT NULL,
    about TEXT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT NOW(),
    delete_time TIMESTAMP NULL DEFAULT NULL,
    last_seen_time TIMESTAMP NULL DEFAULT NULL
);
CREATE UNIQUE INDEX player_name_idx ON player(name);
CREATE TABLE mod 
(
    id SERIAL PRIMARY KEY UNIQUE,
    name TEXT NOT NULL,
    version INT NOT NULL, -- incremented when modified
    description TEXT NULL,
    owner_player_id INT NOT NULL,
        FOREIGN KEY (owner_player_id) REFERENCES player(id),
    create_time TIMESTAMP NOT NULL DEFAULT NOW(),
    delete_time TIMESTAMP NULL DEFAULT NULL
);
CREATE UNIQUE INDEX mod_name_idx ON mod(name);
CREATE TABLE player_mod 
(
    player_id INT NOT NULL,
        FOREIGN KEY (player_id) REFERENCES player(id),
    mod_id INT NOT NULL,
        FOREIGN KEY (mod_id) REFERENCES mod(id)
);
CREATE TABLE card 
(
    id SERIAL PRIMARY KEY UNIQUE,
    version INT NOT NULL, -- incremented when modified
    name TEXT NULL,
    flavor_text TEXT NULL,
    effect_description TEXT NULL,
    type TEXT NULL,
    attack INT NULL,
    health INT NULL,
    sickness INT NULL,
    attack_available INT NULL,
    create_time TIMESTAMP NOT NULL DEFAULT NOW(),
    delete_time TIMESTAMP NULL DEFAULT NULL
);
CREATE INDEX card_name_idx ON card(name);
CREATE TABLE deck
(
    id SERIAL PRIMARY KEY UNIQUE,
    player_id INT NOT NULL,
        FOREIGN KEY (player_id) REFERENCES player(id),
    mod_id INT NOT NULL,
        FOREIGN KEY (mod_id) REFERENCES mod(id),
    name TEXT NOT NULL
);
CREATE TABLE deck_card
(
    deck_id INT,
        FOREIGN KEY (deck_id) REFERENCES deck(id),
    version INT NOT NULL,   -- incremented when modified
    card_id INT NOT NULL,
        FOREIGN KEY (card_id) REFERENCES card(id),
    card_quantity INT NOT NULL
);
CREATE TABLE game
(
    id SERIAL UNIQUE,
    mod_id INT NOT NULL,
        FOREIGN KEY (mod_id) REFERENCES mod(id),
    start_time TIMESTAMP NOT NULL
);
CREATE TABLE game_player
(
    game_id INT NOT NULL,
        FOREIGN KEY (game_id) REFERENCES game(id),
    player_id INT NOT NULL,
        FOREIGN KEY (player_id) REFERENCES player(id),
    player_deck INT NOT NULL,
        FOREIGN KEY (player_deck) REFERENCES deck(id),
    game_rank INT NOT NULL
);

COMMIT;

SELECT
    tables.table_schema,
    tables.table_name,
    columns.column_name,
    columns.ordinal_position,
    columns.is_nullable,
    columns.data_type
FROM information_schema.tables AS tables
    INNER JOIN information_schema.columns AS columns
        ON tables.table_name = columns.table_name
WHERE 
    tables.table_schema = 'cardshifter_stats'
ORDER BY 
    tables.table_name ASC,
    columns.ordinal_position ASC;

Here is the schema in table rendition: