Logfile parser for Team Fortress Classic video game

Question

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<47><STEAM_0:1:111><>" 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, player_id_fk, name) VALUES
    (NULL, (SELECT id FROM players WHERE steam_id = '0:1:111'), 'Marcus The Great'),
    (NULL, (SELECT id FROM players WHERE steam_id = '0:1:111'), 'sucraM'),
    (NULL, (SELECT id FROM players WHERE steam_id = '0:1:222'), 'Benjamin The Weirdo');

Finding a player by steam_id and return his primary nick and known aliases:

SELECT players.steam_id, players.name, group_concat(aliases.name separator ', ') AS aliases
FROM players
INNER JOIN aliases
ON players.id = aliases.player_id_fk
WHERE players.steam_id = '0:1:111'

Hence I'm a novice to relational databases I have a few questions:

1. Am I using one-to-many the right way?
2. Can the layout be improved in any form?

3. If I'm going to print all known player names and their aliases to a website I might have to split aliases (group_concat() part in SELECT query) by separator ", " - now if a player is using the alias/name "Hello, its me!" this will be a problem as the alias/primary nickname will be split into "Hello" and "its me!".

   Of course I could use a separator that is very unlikely to used by any player in their nicknames but I consider that method pretty unclean. I'm sure there has to be a query which would print steam_id, primary nick (players.name) and all the players' aliases so I can just iterate through them, I just can't figure out how to achieve this.

   Any suggestions?

Answer 1

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.