Hey whats up young squires. LOL. I put together a database and for a users table, I made the primary key the login name. WHY? Because I was sick of skimming through the foreign keys and reading the assigned user as a digit.
But Im thinking, since the user should probably be able to change their name, it’d be a waste to have to update every foreign key with their new login name, LOL.
AM I RIGHT?
Should I just go back to the auto_increment for the Primary Key?
Whether you have an auto-increment PK in addition to the username or not is irrelevent because if the user changes their username, and assuming you check if the new name already exists or not, you will still have to change the username from the old to the new in all the tables where the username column exists.
All you have to decide is whether to allow users to change their usernames or not in your application.
Allowing changes to username is not very common. For example Sitepoint will only allow us to change our username once. As such the number of times that they have to deal with updating all the references to the username is extremely rare. Most other sites are likely to be the same. The change is requested infrequently enough to be done manually even though the field is the primary key on one table and a foreign key on many of the others.
I prefer to plan for the worst case scenario making most foreign key references unassociated with meaningful user data, such as a title or name. Less issues that way with changing most fields that way. Also, regardless of updates using a surrogate key assures the URL stays the same to the entity regardless of changes to information for it by the user. However, if something such as the title is used to locate the row in the db, than when the title changes any bookmarks, etc need to be accounted for, which can quickly become out of hand. That isn’t to say the information such as a user name shouldn’t be placed in the URL for SEO purposes, but it can merely be placed there as dumb, filler data using the actual primary key to locate the item ie. /oddz/567849/profile or /My-Really-Awesome-Blog-Post/78945637 - Best of both worlds in regards to SEO and Data Retrieval.
vB does not use the username as the PK for the users table. They use an auto_increment surrogate PK. See here: http://www.vbulletin.com/forum/archive/index.php/t-3112.html
That was a lot of replies fast, LOL. I am thinking along the lines of what oddz just wrote while I rethink a good way to go about this.
I know people will want to change user names. Some applications allow you to login via email, and then change your Display name, and that kind of works – unless they have to change their email and it’s referenced through many tables 
depends what you mean by “waste”
do you understand the concept of ON UPDATE CASCADE?
it is not irrelevant
if the auto_increment is the PK, and the username is just carried along as data, then the user name should not exist in any other tables besides the users table, so if you find that it does, then it’s a very poorly normalized database
all I am saying is that
if the user changes their username, and assuming you check if the new name already exists or not, you will still have to change the username from the old to the new in all the tables where the username column exists.
Whether the username needs to exist in more than one table or not is another issue.
So for me whether I have an additional PK column to the username would be irrelevent.
Whoa I just took a gander at that, that is looking cool to do from what I read… But I’m already switching everything over to # ID’s. I am afraid of getting confused during times of motivation and losing productive time LOL.
When I read about MySQL for some reason I have to read it about 50 times, draw a picture, and sleep before I seem to understand it. 
totally understandable
but that’s no reason to switch to id numbers, in fact, that’d be a good reason ~not~ to
