MySQL: Should my user ID be INT auto-increment primary key starting at 1?

Question

I'm a beginning programmer, building a non-commercial web-site.

I need a user ID, and I thought it would be logical to use for that a simple INTEGER field with an auto-increment. Does that make sense? The user ID will be not be directly used by users (they'll have to select a user-name); should I care about where they start at (presumably 1)?

Any other best practices I should incorporate in building my 'Users' table?

Thanks!

JDelage

Answer 1

Your design is correct. Your internal PK should be a meaningless number, not seen by users of the system and maintained automatically. It doesn't matter if it starts at 1 and it doesn't matter if it's sequential or not, or if you have "holes" in the sequence. (For cases in which you do expose the number to end users, it is sometimes important that the numbers be neither sequential nor fully-populated so that they are not guessable).

Users should identify themselves to the system with another, meaningful piece of the information (such as an email address). That piece of information should either be guaranteed unique (using a UNIQUE index) or else your front end must provide an interface for disambiguation.

Among the benefits of this design are:

1. The meaningful identifier for the account can be changed by updating one value in one record of one table, rather than requiring updates all around the database.

2. Your PK value, which will appear many, many times in the database, is a small and efficiently indexed integer while your user-facing identifier can be of any type you want including a longish text string.

3. You can use a non-unique identifier with disambiguation if the application calls for it.