Although I'm using MySQL, the idea is that this can go to SQL or another DB server if necessary... so as generic as possible.
I'm writing a stat package for tracking video game stats from an RSS feed. I made a mistake and wrote this as a personal venture and only intended to use my own stats. Naturally, I used the 8-digit gameid as my primary key because, it would only appear once in my stats.
The problem crept up when I decided to make it a multi-user system. If two users played in the same game, the gameid would be identical and thus a duplicate in the db (and thus unable to be a PK).
My thought was to use a multi-field PK consisting of the username and gameid.
Problem is I have never used a multi-field PK and I'm concerned how to do this without losing my db. (Of course, I did back it up in case of such an eventuality).
But I'm looking for the best way to alter this table without losing the relevance of the data already used (follow me?).
The table schema is as follows:
CREATE TABLE halo2 (
gameid int(13) NOT NULL default '0',
gamertag varchar(20) NOT NULL default '',
gamestyle varchar(25) NOT NULL default '',
gamemode varchar(25) NOT NULL default '',
gamemap varchar(15) NOT NULL default '',
link varchar(100) NOT NULL default '',
playdate varchar(30) NOT NULL default '',
score int(5) NOT NULL default '0',
kills int(5) NOT NULL default '0',
deaths int(5) NOT NULL default '0',
assists int(5) NOT NULL default '0',
PRIMARY KEY (gameid)