What should the Primary Key be?

Question

I've ran into an issue that I can't seem to solve.

Say for instance that I have a table with upcoming videogame releases:

GAME
game_ID | title             
-----------------------------
1       | Super Mario
2       | Final Fantasy XIII

And then I've got a table with releasedates (different dates for ps3 and xbox360 just for the sake of the argument):

RELEASES
game_ID | releasedate | platform
---------------------------------
1       | 20-04-2010  | Wii
2       | 23-03-2010  | PS3
3       | 20-03-2010  | Xbox360

Now, I have put game_ID as the primary key in the table "GAME". And game_ID is also a foreign key in the table "RELEASES". What should I have as the primary key in the latter? It seems rather unnecessary to create yet another ID-key in the "RELEASES"-table?

Can I somehow use game_ID and platform together to create the primary-key? If so, how would the SQL look like?

Answer 1

You can create a composite key that consists of game_id and platform just like you create a primary key that consists of only one column:

PRIMARY KEY(game_id, platform)

Answer 2

You don't want game_ID to be the primary and foreign key in the Releases table. That would prevent the table from having more than one record for each game, since the primary key must be unique. I would recommend a structure like this.

RELEASES
release_ID    | game_ID     | releasedate | platform
---------------------------------------------
    1         |     1       | 20-04-2010  | Wii
    2         |     1       | 23-03-2010  | PS3
    3         |     1       | 20-03-2010  | Xbox360

release_ID would be auto-generated. You could use a composite key by including platform in the primary key, but you may run into a problem if a single game/platform has multiple releases. You may not think that's possible now, but things change.

I also consider it good practice to never use a column that has any meaning as a key, since things change and you can't predict how they will change. If you keys are meaningless to end users, then they can't mess with the structure of your database.