What should the Primary Key be?

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开发者_Go百科 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?

---

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)

---

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.

---

First, consider realising Platforms as a separate lookup table. Not only does this reduce the chance of data corruption, but it'll also make your key definition easier.

A primary key doesn't have to be restricted to one field. You can define a composite primary key using multiple fields.

However, in this instance, I'd recommend against going the composite primary key, and would advocate the creation of a new primary key on the Releases table.

Why? Well, for starters, I don't think you've captured enough info in Releases. For example, video games are often released at different times in different regions, so it could be perfectly valid to have two releases for the same game and platform. In that circumstance, you'd need a composite primary key comprising of three fields. Where does it end? :)

By defining ReleaseID as a surrogate primary key, you give yourself a lot more scope for change in the future.