5

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.

Jan Andersen
  • 51
  • 2

2 Answers2

3

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.
Bohemian
  • 412,405
  • 93
  • 575
  • 722
1

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.

mbinette
  • 5,094
  • 3
  • 24
  • 32
  • Perhaps another solution, related to the DBEXPRESS problem adding new records to a table. Suppose we use a time stamp down to two digit milisecs, and top years off, or first digit could be used as a "source" id, 1-9 different applications can add to the table. That would/ could however give problems on change from winter to summer - but not at the scale of hazard at every add to table. – Jan Andersen Sep 23 '12 at 14:28
  • I don't know... I really feel like generating a simple sequence/auto-generated id is the way to go for it's simplicity. However, if you want to generate that integer from other information, that can be a good thing (conceptually). Just make sure you're not making it too complicated for you (for updates, etc). The thing is there is sometimes that gap between what is a good concept, and what is a good practice. Like Bohemian said, one of the dangers of going against common practice (which would be having a sequence id) is that it might be harder for later SQL programmers to work with your DB. – mbinette Sep 23 '12 at 16:12
  • "In theory, there is no difference between theory and practice. But in practice, there is." - Jan L.A. van de Snepscheut – mbinette Sep 23 '12 at 16:14