Logfile parser for Team Fortress Classic video game

I'm currently working on a logfile parser for a pretty old videogame called Team Fortress Classic, which can be compared to Counter-Strike 1.6 and is available on Valve's Steam platform.

I started out trying to make a collection of nicknames for each player. Every player joining the server has a unique ID, the STEAM_ID.
Once a player joins the server I can parse the ID off the logfile:

L 07/08/2013 - 19:25:40: "Marcus<>" entered the game

So the STEAM_ID for the joining player would be STEAM_0:1:111, for the database I strip off the static prefix STEAM_ and only add 0:1:111 to the steam_id field in the database to keep the size smaller.

Now a player can change his in-game nickname from let's say Marcus to sucraM. I thought about making a one-to-many relationship between the tables players and aliases, adding the nickname the player was first seen with to the players table as a primary nickname to easier keep track of the player (later that would be useful i.e. when a known player joins the server with a new nickname I could print a line "new_nickname is also known as primary_nickname" to tell everyone who they are actually dealing with) and using a foreign key field player_id_fk in the aliases-table to be able to add several aliases for one players.id.

The database structure I created looks like the following;

players table:

CREATE TABLE players (
    id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    steam_id VARCHAR(15) UNIQUE,
    name VARCHAR(15)
) ENGINE = 'InnoDB';

aliases table:

CREATE TABLE aliases (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    player_id_fk SMALLINT UNSIGNED,
    name VARCHAR(31),
    FOREIGN KEY (player_id_fk) REFERENCES players(id) ON UPDATE CASCADE ON DELETE CASCADE
) ENGINE = 'InnoDB';

Populating the tables with dummy data:

```
INSERT INTO players (id, steam_id, name) VALUES
(NULL, '0:1:111', 'Marcus'),
(NULL, '0:1:222', 'Benjamin');
INSERT INTO aliases (id,

Your 3rd question is off-topic for the site, but I do believe that you are using the one-to-many relationships correctly.

When you are finding a player by steam_id you don't actually need to select the steam_id unless you are going to use it for something in an application. This will speed up the query (probably not enough to be visible though).

Another thing, and this is just a thought because I am not positive on how this works:

In your players table you could change the primary key to be the Steam ID since that is always going to be unique and you are always going to give that as a value on the insertion of a record.

On the same note, you can change the foreign key of the aliases table to be the Steam ID as well.

Still add the Constraint of NOT NULL, Primary Key And Foreign Key as appropriate.

StackExchange Code Review Q#28329, answer score: 5