Is it good practice to use email as id in a mysql DB?

Question

I have read may questions and answers as to simplicity of a mysql DB and good practice.

I have a mysql DB, with a 'clients" table. Each client added has an email, which is unique as to emails. Using C++ Builder there are problems adding records caused by the id field auto incremented - forcing to manually increment the id during adding new rows using DBEXPRESS.

Why not skip the auto incremented id ? Will it be good practice to have a table without (auto incremented) id, and have the email as unique key ? That would solve the DBEXPRESS problem.

Answer 1

No: Don't use email as the primary key. There a few reasons why:

• You will never be able to store people who don't have an email address, which you may want to do for various reasons
• It makes the key very "wide", so foreign keys are wide too, leading to much wasted disk space and slower queries because there are less index entries per I/O page
• People may change their email address - how will you handle that if it's the primary key?
• Your queries will be less intuitive to maintain, because most database coders expect there to be an auto increment key called id. Conforming to industry standards is good practice.

Answer 2

Conceptually, yes, it is a good idea to not generate artificial unique keys (or at least, limit those instances). And e-mail serves that natural unique key purpose.

However, one thing I would be worried about is performance. Having an integer as an id is pretty convenient when doing queries and it is a lot faster to search in those than long strings... It does not matter if you simply retrieve a user from the database using the e-mail address. But it matters if you have complex queries with multiple joins.

Also, if you store information about clients in multiple tables, the foreign keys to the other table will be the e-mail address. That means that you store the e-mail address multiple times, which will make it a lot harder to keep everything updated if a client decides to change his e-mail someday.