Database schema for holding game save files

Question

I have a database schema for a game to hold save files where some tables have a one-to-one relationship to classes. I was wondering if I should split up some tables.

Example: troop_class table. It could have a PK of

{ GameID,PostionX,PostionY,PostionZ,Team }

but then team don't really rely on the positions and could be split to two new tables

game_units Keys{GameID, troop_id} PK{GameID}

and

troop_team{troop_id, Team} PK{troop_id}.

I am wondering if I should break up the one-to-one mapping for table-class and start to normalize and create proper PK instead of using id.

CREATE TABLE accounts(
 AccountName VARCHAR(50) UNIQUE NOT NULL,
 Password BLOB NOT NULL,
 PRIMARY KEY(AccountName)
) Engine=InnoDB;
CREATE TABLE maps(
 maps_id INT NOT NULL UNIQUE,
 MapName VARCHAR(30) NOT NULL,
 PRIMARY KEY (maps_id),
 INDEX(maps_id)
) ENGINE=InnoDB;
CREATE TABLE game_searching_player(
 AccountName VARCHAR(50) NOT NULL,
 MapID INT NOT NULL,
 PRIMARY KEY (AccountName, MapID),
 FOREIGN KEY (AccountName) REFERENCES accounts(AccountName)
 ON DELETE CASCADE ,
 FOREIGN KEY (MapID) REFERENCES maps(maps_id)
 ON DELETE CASCADE ,
 INDEX(AccountName)
) ENGINE=InnoDB;
CREATE TABLE games(
 games_id INT NOT NULL AUTO_INCREMENT UNIQUE,
 Player1 VARCHAR(50) NOT NULL,
 Player2 VARCHAR(50) NOT NULL,
 WhosTurn INT NOT NULL,
 TurnHasEnded BOOL NOT NULL,
 HaveWon BOOL NOT NULL,
 TurnNumber INT NOT NULL,
 MapID INT NOT NULL,
 Type VARCHAR(30) NOT NULL,
 PRIMARY KEY (games_id),
 FOREIGN KEY (Player1) REFERENCES accounts(AccountName)
 ON DELETE CASCADE ,
 FOREIGN KEY (Player2) REFERENCES accounts(AccountName)
 ON DELETE CASCADE ,
 FOREIGN KEY (MapID) REFERENCES maps(maps_id)
 ON DELETE CASCADE ,
 INDEX(games_id,Player1, Player2 )
) ENGINE=InnoDB; 
CREATE TABLE player(
 GameID INT NOT NULL,
 Player VARCHAR(30) NOT NULL, 
 VictoryResources INT NOT NULL,
 UniversalResources INT NOT NULL,
 Type VARCHAR(30) NOT NULL,
 PRIMARY KEY (GameID, Player),
 FOREIGN KEY (GameID) REFERENCES games(games_id)
 ON DELETE CASCADE ,
 INDEX(GameID)
) ENGINE=InnoDB;
CREATE TABLE troop_class (
 troop_class_id INT NOT NULL AUTO_INCREMENT UNIQUE,
 GameID INT NOT NULL,
 Health INT NOT NULL,
 CombatInitiative INT NOT NULL,
 Spotted BOOL NOT NULL,
 Entrenched INT NOT NULL, 
 Initiative INT NOT NULL,
 Ammo INT NOT NULL,
 Petrol INT NOT NULL,
 ActionPoints INT NOT NULL,
 IsMoving BOOL NOT NULL,
 PositionX INT NOT NULL,
 PositionY INT NOT NULL,
 PositionZ INT NOT NULL,
 Team VARCHAR(30) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Type VARCHAR(30) NOT NULL,
 PRIMARY KEY (troop_class_id),
 FOREIGN KEY (GameID) REFERENCES games(games_id)
 ON DELETE CASCADE ,
 INDEX(GameID)
) ENGINE=InnoDB;
CREATE TABLE supply_unit(
 supply_unit_id INT NOT NULL UNIQUE,
 SupplyPetrol INT NOT NULL,
 SupplyAmmo INT NOT NULL,
 VictoryResources INT NOT NULL,
 LoadWeight INT NOT NULL,
 MaxWeight INT NOT NULL,
 PRIMARY KEY (supply_unit_id),
 FOREIGN KEY (supply_unit_id) REFERENCES troop_class(troop_class_id)
 ON DELETE CASCADE
) ENGINE=InnoDB;
CREATE TABLE central_warehouse (
 central_warehouse_id INT NOT NULL AUTO_INCREMENT UNIQUE,
 GameID INT NOT NULL,
 SupplyPetrol INT NOT NULL,
 SupplyAmmo INT NOT NULL,
 VictoryResources INT NOT NULL,
 LoadWeight INT NOT NULL,
 MaxWeight INT NOT NULL,
 PositionX INT NOT NULL,
 PositionY INT NOT NULL,
 PositionZ INT NOT NULL,
 Team VARCHAR(30) NOT NULL,
 Name VARCHAR(30) NOT NULL,
 Type VARCHAR(30) NOT NULL,
 PRIMARY KEY (central_warehouse_id),
 FOREIGN KEY (GameID) REFERENCES games(games_id)
 ON DELETE CASCADE ,
 INDEX(GameID)
) ENGINE=InnoDB;

Answer 1

Here are a few suggestions, many of which are just "best practices" I've picked up along the way (or simplifications that would make things a bit more concise and easier to read). There are also a few ideas that will likely prevent common errors from happening to the code as it's maintained in the future (that I've run across in many code reviews).

1. I'd create an id column on the accounts table:

   CREATE TABLE accounts(
    account_id INT UNIQUE NOT NULL,
    AccountName VARCHAR(50) UNIQUE NOT NULL,
    Password BLOB NOT NULL,
    PRIMARY KEY(account_id)
   ) Engine=InnoDB;
   

   Then the tables that reference accounts like game_searching_player will reference an INT instead of VARCHAR(50) - (I think I see at least 4 references to that column, which would be much simpler if they were ints.) You may also likely get a small amount of performance improvement by using INT for these keys instead of VARCHARs.

2. MySQL automatically indexes PRIMARY KEYs, so no need for those explicit INDEX lines.

   For example, this:

   PRIMARY KEY (maps_id),
   INDEX(maps_id)
   

   would become just

    PRIMARY KEY (maps_id)
   

3. MySQL's InnoDB engine automatically indexes FOREIGN KEYs, so no need for those explicit INDEX lines either (player and troop_class and game_searching_player).

   For example, this:

   CREATE TABLE player(
    ...
    PRIMARY KEY (GameID, Player),
    FOREIGN KEY (GameID) REFERENCES games(games_id)
    ON DELETE CASCADE ,
    INDEX(GameID)
   ) ENGINE=InnoDB;
   

   becomes this:

   CREATE TABLE player(
    ...
    PRIMARY KEY (GameID, Player),
    FOREIGN KEY (GameID) REFERENCES games(games_id)
    ON DELETE CASCADE
   ) ENGINE=InnoDB;
   

4. You don't have AUTO_INCREMENT on all your table IDs, but I suspect that you actually want it on them all, since it will simplify your insertion code.

5. In MySQL all PRIMARY KEYs are also guaranteed to be UNIQUE and NOT NULL, so those attributes aren't really needed on those columns, though my personal preference is that it adds to the readability to leave those attributes on the columns, but thought I'd mention it for reference.

6. Your player table should probably reference the (new) id in the accounts table (with a ON DELETE CASCADE setting too). It also looks like players can have names up to 30 characters long, but accounts can have names up to 50 characters. (The difference in lengths may also of course indicate that I completely mis-understand the purpose of the player and account tables, and that they really shouldn't be related in any way.)

7. While it will work perfectly fine the way you have it shown here, in most of the environments and organizations I've worked with/in, it has been standard best practice to make the name of every table's "id" column be id, rather than including the table name in the column name (like maps_id). This "coding style" choice allows the code to be more concise and since this is a very common practice, it doesn't add any "cognitive load" as maintainers read your queries/schema/code.

• David Harkness: I agree with all, though on my most recent project I switched to using the table's name in the ID column (#7), e.g., player_id. The reason is that this allows the use of the terse join form account join player using (account_id) as opposed to the longer where clause account, player where account.id = player.account_id. This comes up more when building reports and using the command-line client, but I definitely like it so far.
• David Harkness: I'll also add that I've found using singular noun forms for table names to be more readable. Thus map instead of maps. Why? What's the name of the table that holds the foot items? foots or feet?

Answer 2

I've found over the years that it's best to start with the normalized form and denormalize as-needed to solve performance and usability problems. Without a full description of what troop_class stores, we're left to guess about a lot of details.

1. Is Team unique for each troop_class? If not--and there's a team table--add a unique int surrogate key to it and use that throughout as Eric S suggested.

2. Avoid using data attributes--especially those that may change over time--in primary keys. Adding the x, y, and z positions to the PK will severely complicate working with these rows since you'll need to update the foreign keys every time the unit moves.

3. Similarly for Type. Is this what one would normally call the "class" of the unit, defining its characteristics that are the same across all units belonging to that class? This information should be extracted to its own class table.

   team <---- unit ----> type
    1 * * 1
   

   Here each unit holds the specifics for a single unit belonging to a team: its position, health, action points, etc. The type table lists the things that don't change about a unit: its ammo and fuel capacity, strength, etc. This minimizes the data duplication across tables and allows you to load up all unit types at the start of the game or in tools for easy reuse throughout.

Answer 3

In addition to the others review which are totally great, I would like to add a little thing!

You name your tables and primary keys using_the_following_notation (underscores), but then you name your other fields usingTheFollowingNotation (camelCase).

Both are valid and great notations. But try to be consistent and use one OR the other, not both at the same time.