Database design ==> primary key????!!!!?

Question

Hi i'm designing a database for a membership system.
Th question is, "is it bad to have the username as the primary key for my table? and use the username to create relationships to my other tables?
and if it's not why?

Answer 1

It's not terrible, but you can probably do better by just assigning an integer user_id column to that table.



Why, you ask?



1) If the user decides they want to change their username at some point in the future, you can do so more easily than if you're using their username as a foreign key in a bunch of your tables



2) integer comparisons are much faster than string comparisons, so ou'll get a little bit of performance improvement in your DB and in any applications that use your DB.

Answer 2

There is nothing wrong with that approach. Any unique value will do fine and usernames are usually unique.



One approach that is commonly used though, is that people will use an index of integers, longs, etc. as the primary key for all of their tables. They will call that column, simply "ID" since it identifies the row uniquely. If that ID is referenced by numerous other tables, it could possibly save some space because those other tables would store 1, 2, 3, etc. as a reference rather than "superCoolUser"



I won't go so far as to call that a "best practice" though because any unique column will do just fine.

Answer 3

I would use an auto-number as the primary key, if the database supports it, to make it independent of the user's whims. If you allow username changes, using the name as the key could cause problems if the database isn't designed properly.



On the other hand, I would certainly index usernames to enforce uniqueness.



Hope that helps.

Answer 4

always us a autonumber and not the value



as you add new users.. it will be faster to add and look up if it is an int as it will be a primary key/clustered index.



if you use the username as the primary key

and you have "abe" and "jan" as users.. and you add "doug"

it will need to reorganize the table.. slowing it down...



if you need to use these in a web app and need to pass them on a querry string... then you should use guids