Database schema for holding game save files

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(
Game

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

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

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

-
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;

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

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

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

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

StackExchange Code Review Q#15082, answer score: 8