Trading Card Game database schema for statistics

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,

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....).

StackExchange Code Review Q#67963, answer score: 10