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

Question

I read questions and answers regarding the simplicity of the mysql database and good practice.

I have a mysql database with a clients table. Each added customer has an email that is unique to emails. With C ++ Builder, problems arise with adding records caused by the automatically increasing id field - to force the manual increase of id when adding new rows using DBEXPRESS.

Why not skip the auto-increment identifier? Would it be good practice to have a table without an (automatically incrementing) id and have email as a unique key? This would solve the DBEXPRESS problem.

Answer 1

No. Do not use email as the primary key. There are several reasons:

• You can never store people who don’t have an email address, which you might want to do for various reasons.
• It makes the key very "wide", so foreign keys are also wide, which leads to a significant reduction in the amount of free disk space and slower queries, since there are fewer index entries on the input / output page.
• People can change their email address - how do you handle it if it's a primary key?
• Your queries will be less intuitive to support, since most database encoders expect that there will be an automatic id key for this. Compliance with industry standards is good practice.

Answer 2

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

However , one thing I'll worry about is performance. Having an integer as an identifier is quite convenient when executing queries, and it’s much faster to search in those long strings ... It doesn’t matter if you simply retrieve the user from the database using the email address. But it matters if you have complex queries with multiple joins.

In addition, if you store customer information in multiple tables, the foreign keys to another table will be the email address. This means that you store the email address several times, which will make it much more difficult if everything is updated, if the client decides to change his email sometime.