Sign in to follow this  
Alejandr0

[web] Primary key choice

Recommended Posts

Alejandr0    100
I'm a start to developing an online RPG game, like “Mafia Wars”. I'm using : Flash + PHP + MySQL I have read some larges debates about natural keys vs surrogate keys in forums. I would like to read opinions from game developers. What will be a good choice for a primary key : an e-mail, an user-name, or a surrogate key (e.g. database sequence number) Thanks.

Share this post


Link to post
Share on other sites
Anntor    254
Quote:
Original post by Alejandr0...What will be a good choice for a primary key : an e-mail, an user-name, or a surrogate key ...


It depends on many factors. But for your examples: Ask yourself what happens, when someone wants to change his e-mail address!

All tables with references to that key have to be updated too. So an update in one table causes cascading updates in all adjacent tables.


But read Wikipedia surrogate keys for further advantages and disadvantages.

Share this post


Link to post
Share on other sites
krez    443
Heh heh, this one starts flame wars on database forums as badly as OpenGL vs DirectX do over here.

In theory, a natural key is the right way to do do it. You just have to pick one or more attributes that are unique and don't change. In practice, there is no piece of data that is guaranteed to never change, forcing you to cascade updates to any other tables relying on that natural key. This is possible but a real pain.

I've done time as a database developer and find natural keys to not be worth the effort; I'm not getting graded on proper relational theory, I'm getting paid to make it work and make it maintainable.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this