Having looked at the code for a number of web forums, most of them use a numeric ID as the primary key for their users table. They keep the usernames unique by doing a search through the table when creating a new user or changing the username and not allowing it if the search comes up with any existing rows.

Quote:Original post by graveyard filla
Yeah, you're right.... Like I said, every situation has different variables.....in a web app I did for work I used the username as the PK - because no one ever changed their username (In fact, this wasn't even possible by the software.) It doesn't even seem like something that would be too common in any software, anyway...... have you ever changed your username for anything?

It would've been more efficient storage wise to use a surrogate key even if the user name never changed. An int would give you 2.1B unique numbers in only 4 bytes of space. All of your foreign keys would also only be 4 bytes. A varchar would vary, but anyone with a user name >4 chars long would be less efficient spacewise than the int.

As far as performance, joining using the int datatype is more efficient performance wise than either char or varchar (at least on mssql). Also, b/c of the reason above, more data can possibly fit in your data pages that will again make a join more efficient timewise. An argument could be made that since you have the username in the other tables you won't have to join the user table to them at all, but then what about the other tables that you may need to join together such as roles, prefs, posts, whatever.

Very rarely in practice have I seen a natural PK workout better than a surrogate one in storage efficiency, speed, and maintainability.

Quote:Original post by graveyard filla
Unfortunately my boss was a cheap bastard so these are the kind of things I had to do [grin].

Understood. I tell my current boss either we can do it right now or do it right later. No matter what we do it will eventually have to get done right. Only takes a couple examples before managers start to realize a little extra time up front can save so much time in the back end. This is even more true on the database side of things b/c once you have a structure built with live data and code built on top of it, it's so hard to change in a graceful manner.