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
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
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
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;
CREATE TABLE players ( id SMALLINT UNSIGNED AUTO_INCREMENT PRIMARY KEY, steam_id VARCHAR(15) UNIQUE, name VARCHAR(15) ) ENGINE = 'InnoDB';
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
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:
- Am I using one-to-many the right way?
- Can the layout be improved in any form?
If I'm going to print all known player names and their aliases to a website I might have to split
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'
aliasesso I can just iterate through them, I just can't figure out how to achieve this.