For a consumer website, should I have a user ID in my DB in addition to the user-selected unique username?

Question

Hello everyone,

This is a "web dev best practice" question. I am envisioning a website where users would sign up with a username they would select (revolutionary, I know). They would also enter a valid email address. I would test for the uniqueness of those in the db before allowing the sign-up.

The site is non-commercial in nature, and I don't need a GUID.

Given this, should I have in my db a separate user ID? I understand vaguely that MySQL might index more efficiently a number-based ID?

Alternatively, should I just use the email address as a unique identifier?

Thanks.

JDelage

Answer 1

User names are typically not unique, and even e-mail addresses can be reused. That's one reason to have a numeric unique user id.

Another is that when you create a database relation, MySQL has to make a copy of the primary key of the user table. For example, if you have a list of orders per customer. The order would contain the username if that was the primary key. Usernames are longer than integers, which hurts index performance.

If you combine the two, a user that changes his username or email becomes a big problem: you have to rename a primary key, which isn't easy, and you'd have to update all references to that user from other tables.

But overall, eh, I would hope you have bigger things to worry about, like adding functionality to attract users :)

Answer 2

It is good practice to use some type of dumb key (int, GUID) to identify a record in case the need to update the unique key ever arises. I previously worked in the health insurance industry where we identified members by their SSN (prior to my joining the team) and carried that throughout the db as foreign keys. Once HIPAA was implemented and SSNs were no longer able to be used as Member IDs the need for a dumb key was evident. I cannot speak for the technical advantages but from a flexibility standpoint the choice is obvious.