Jump to content
  • Advertisement
Sign in to follow this  
korbul

Database Primary Key

This topic is 2342 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hello guys, I'm new to gamedev.net and i came here with a question. I'm building an online game with c++, but that's not what my question is about. In my database, I have a table named Players. The structure of Players is something like [color=#800000]playerID(primary key), playerName, etc. Now here is what I need to know:

1. If playerName is unique, can I remove playerID and make playerName primary key?
2. Why would the above be ok/not ok?

The entries in Players table could go as high as thousands.
I am using MySql with InnoDB

Thank you.

Share this post


Link to post
Share on other sites
Advertisement
Yes indeed, that answers my question. Thank you.

However, for knowledge's sake, if the user would not be able to change his name, would it be ok for playerName to be primary key?

Share this post


Link to post
Share on other sites

Yes indeed, that answers my question. Thank you.

However, for knowledge's sake, if the user would not be able to change his name, would it be ok for playerName to be primary key?


I don't see a problem in that. Even if you declare the player name as primary key you won't be able to create duplicates for it because your hurting the "no duplicate key" constraints.

For my personal use I still prefer a unique ID instead of the name.

Share this post


Link to post
Share on other sites
It's always a better practice to make your primary key an int value.

The querying is way more faster and optimal when doing complex queries.

Have a nice day

Share this post


Link to post
Share on other sites
A couple extra details I hope help you bring it all home. Just wanted to give you a few terms and tell you to follow up online with a few searches on databases and "third normal form".

1. Using the playerName's suitability to being a key (uniqueness mainly but a few other things as well) are what make it both a "candidate key" and a "natural key".
2. The playerID field is also called a "surrogate key" if it has no purpose in your system except to be the primary key.
3. Ideally surrogate keys are not used as inputs or outputs of your system, only internally ... because that allows you to change them as the needs change (for instance picking a 32 bit number for the first 4 years of your product and then re-factoring the system to use 64 bit numbers when it threatens to outgrow its boundaries). If you think of your surrogates as a reference and nothing but a reference, whose only valid operation is a test for equality you will be on a solid foundation (ie dont make assumptions like lower ID means created first ... because what happens then when you have systems which create IDs in batches and merge later - like perhaps a distributed MegaMillions gambling systems).
4. Do also add indexes and/or constraints on all of your candidate and natural keys where appropriate. (ie do add a unique index on playerName). This can allow for enormously more efficient search operations. You'll know when to violate this rule long before you ever need to (I've violated this idea for due to space related performance impacts 2 times in 7 years and over 600 tables worth of database development).
5. When doing data models, focus on making the models LOGIC and CONSISTENT before worrying about performance. It is far easier to refactor your whole program which uses 30 tables build on simple standard principles, then to deal with a system of 30 tables where 4 are optimized 1 way, 5 another, 20 not at all, and the last 1 is some bastard unique crazy idea that came to you in a dream. For instance if you don't ever put surrogate keys on your map tables (many-to-many join tables) ... that's fine ... if you do ... that's fine ... but BE CONSISTENT. Consistency should even trump "cool", or "optimized", or even "right" most of the time (all rules are made to be broken though).
6. Consistency in naming is important too, and easier if you decide on a strategy early. And one of the worst is how to name tables that are the join / map table for 2 tables, each of which is more than 1 word long.

Good Luck.

Share this post


Link to post
Share on other sites
Sign in to follow this  

  • Advertisement
×

Important Information

By using GameDev.net, you agree to our community Guidelines, Terms of Use, and Privacy Policy.

GameDev.net is your game development community. Create an account for your GameDev Portfolio and participate in the largest developer community in the games industry.

Sign me up!