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.
Database Primary Key
Author
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?
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?
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.
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?
Usually doing lookups based on integer ids is faster than doing it on strings so once you start having relationships things will perform far better with integer primary keys.
text primary keys are allowed by most SQL implementations though so it is possible.
Requirements change. When they change, you'll wish you had chosen to have an artificial key.
Generally, if you have other columns which are logically keys, you can also constrain them to be unique too.
Generally, if you have other columns which are logically keys, you can also constrain them to be unique too.
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
The querying is way more faster and optimal when doing complex queries.
Have a nice day
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.
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.
This topic is closed to new replies.
Advertisement
Popular Topics
Advertisement
