Take the 2-minute tour ×
Programmers Stack Exchange is a question and answer site for professional programmers interested in conceptual questions about software development. It's 100% free, no registration required.

Is an ID field is always needed in database tables?

In my case I have a user with firstName, lastName and email fields. email is unique and not null, so it could be used as an ID, right? So in that case, could/should I try to remove the ID?

Also I want to have another table which extends this one. Let's say its called patient and it has it's own field additionalData and I would like to link the relationship through the email of user I mentioned. So the relationship should be 1 to 1, right? and I wouldn't need the IDs? Somehow MySQL Workbench wants me to use the IDs.

What do you guys think. Any suggestions on this topic?

share|improve this question
1  
Related: stackoverflow.com/questions/840162/… and also stackoverflow.com/questions/439507/… Also, might this have been better asked at dba.stackexchange.com/questions ? –  FrustratedWithFormsDesigner Nov 6 '12 at 20:18
2  
dba.stackexchange.com/questions/6108/…, dba.stackexchange.com/questions/24048/… - already there, @FrustratedWithFormsDesigner. –  Mat Nov 6 '12 at 20:52
16  
I think the first time someone needs to change their email address, you're going to realize why using that as a key all over your database is a bad idea. –  Blrfl Nov 6 '12 at 20:55
    
It is a bit uncommon to get a patient's name from a user table specially when you have 1-1 relationship. Think carefully about your model. –  Emmad Kareem Nov 6 '12 at 22:14
    
What happens when a user wants to change their E-mail address? –  kevin cline Nov 6 '12 at 23:27
show 1 more comment

5 Answers

up vote 15 down vote accepted

Virtually every table needs a primary key. I would strongly argue that every table needs a primary key but I'm willing to make the occasional exception.

Whether the primary key of a table should be a "natural" primary key-- some column or columns that are part of the business data that are naturally unique-- or whether a "synthetic" primary key should be used-- some additional data that has no business meaning and is used solely as an identifier, generally either an incrementing integer or a GUID-- is a bit of a religious debate. Personally, I tend to prefer synthetic keys over natural keys but other data modelers who I have a great deal of respect for prefer natural keys.

In your case, one of the primary issues with using a email address as a natural key is dealing with what happens when someone wants to change their email address. If email address is the primary key, they you'll have to change the data in the USER table but you'll also have to ripple the change through every child table that has a foreign key relationship to the USER table. Depending on your database, that can range from annoying to a major undertaking, depending on whether your database happens to support cascading updates. Oracle, for example, believes that primary keys ought to be immutable so it does not support cascading updates-- you'd have to write that code yourself (or leverage one of the packages floating around to do it). I believe MySQL does support cascading updates so you merely have to ensure that each and every foreign key constraint in the system is set to cascade on update and test that you haven't broken the ability to change an email address every time you add a new table to the database or modify a constraint. On the other hand, if you use a nice, simple synthetic key, i.e. a user_id column, you can simply update the email address column like you would update any other piece of business information.

Whatever you define as the primary key of your parent table should be used as the foreign key in your child table.

share|improve this answer
5  
Your example of the key changing rippling through the system is why I've personally come to prefer synthetic keys. The problem with natural keys is that others know of it, and at some point you'll have to deal with changes to the actual value, or some new format, etc. This then results in a whole bunch of work that can be avoided if a synthetic key is used. –  Andy Nov 7 '12 at 1:09
1  
Worth noting that many natural keys that we expect to be constant, can actually change: E-mail addresses, social security numbers, ISBNs, tax IDs, FEINs, etc. - all of those CAN and DO change in certain scenarios, and thus are not good natural key candidates. –  Keith Palmer Nov 7 '12 at 2:45
    
Natural PK's should always be used over Synthetic where available, they make the data far more readable, and reduce storage space and associated resources when processing. Email address as a Natural key however is a bad example, as Email addresses can change. A field should only be considered a suitable natural key if it will never change. –  Gavin Coates Nov 7 '12 at 9:39
    
MySQL does support cascading updates, but foreign key relationships are not supported by all storage engines, which means the foreign key creation (and by extension, the cascading update) will silently fail if one of the tables is a MyISAM table. –  tdammers Nov 7 '12 at 22:46
add comment

email is unique and not null, so it could be used as an index, right?

Right. It would be the natural key of the table. You don't need to a surrogate key, but having to join on this field is wasteful and could be slower than using an int surrogate key.

So the relationship should be 1 to 1, right? and I wouldn't need the IDs?

Lets say it is 1 to 1. You still need to link the two tables together.


Using a surrogate key can help with performance. If the natural key is also of a data type that leads to efficient storage and execution, all the better.

share|improve this answer
add comment

well as my title says I have a qeustion whether an index is always needed in database tables?

Pretty much nothing is always needed--but you will basically always want an index.

Natural keys are a wonderful thing when they are efficient and exist. In the real world, both rare. In your example, I would be very hesitant to regard email as unique. Email addresses can be reissued. Domain names are bought and sold. This is especially possible if you have soft deletes on your tables (e.g. toggle an IsDeleted flag to 1 instead of issuing a hard DELETE statement).

Moreover, as @Oded pointed out, using a combination of strings is seldom as efficient a key as just allocating one usable integer.

share|improve this answer
1  
Also, users change email address, or even have several. Changing email address doesn't make them different people though, nor does it change your obligations towards them. –  Donal Fellows Nov 7 '12 at 14:48
add comment

In every database schema there could be many tables.

  1. In some tables the natural key is a good key.
  2. In some tables no natural key exists or it is not a good key.

In case 1 you should used the natural key.

In case 2 you should use a autogenerated surrogate key


"Every PK must be natural" is a wrong approach and impossible to achieve. This is an extreme position, but nobody proposes it.

"Every PK must be surrogate" is a wrong approach also but sadly possible to achieve. This is also an extreme position.


Use natural when natural fits and use surrogate when it doesn't.

The "every PK must be surrogate" approach leads to many headaches:

  • Migrating data from a database to another is a nightmare since surrogates are sequences that are not syncroniced between databases.
  • Data is only meaningful if visualized through "the" application, assuming an "one application -> one database" paradigm.
  • Queries are more complex because you have to join every table from the top-most table ( only place where the business key exists, down to the last table ).
  • Business people speaks business, they know that a car plaque is ADFG 237, they don't know the car has 155201 ID in the car table. Meetings with the users/business people get awkward because you continue talking about keys they don't know and they continue talking about business keys they know.
  • Users will continue to use the natural key for searches, meaning you will have to keep the natural key indexed anyway.
  • Interoperation with any external system has the overhead of translating the business key, like the ISO/universaly accepted LHR code for London Heatrow Airport, into the surrogate meaningless key.

Those who defend the "every PK must be surrogate" approach usually argue than character keys are inefficient for joins. Well maybe that was a decade ago, and they fail to produce numbers to support that.

They also argue the update issue when the natural key changes. If the natural key changes frequently, then it's not a good key. If it changes every ten years, that's what cascade update is built for into modern RDBMS.

Also, any hipotethical performance problem of using character keys can be solved by throwing money at it.

But the problems of using the "every PK must be surrogate" approach, cannot be solved by throwing money at them.

Please someone produce any canonical text or bibliography that supports the "every FK must be surrogate" point. On the other hand, the "mixed", "surrogates when necessary" approach is well documented. And Codd's normal forms still exist.

My suggestion:

  • Use natural keys where a good enough one exists.
  • Use surrogates when a good enough natural key doesn't exist.

EDIT: Some people say that "every PK must be surrogate" is an implementation decision, and thus it doesn't violate 3NF and 3NFBC, but they include them first off from the beginning of the design, in the conceptual phase, violating 3NF and 3NFBC, which is an error that is paid by means of writing much much more code than necessary.

share|improve this answer
2  
Every table I create has an auto-generated, unique primary key for one simple reason: I don't have to think about it. It is guaranteed to work every time, and it doesn't suffer any of the vagaries of natural keys. –  Robert Harvey Nov 6 '12 at 21:56
2  
Nice, but no. Auto-generated keys allow me to more freely think about the business concerns without having to worry about some goofy business rule (now or in the future) fucking up my relationships between entities. I get to completely control these keys, in other words. –  Robert Harvey Nov 6 '12 at 22:27
1  
Having synthetic keys that are divorced from the natural keys is the most egoless way of doing it. It guarantees that the business can change anything about any of the natural keys they use in any way they wish without breaking my database model. I appreciate the fact that you have a different preference, but... meh. –  Robert Harvey Nov 6 '12 at 22:35
1  
Yeah, I get that. But the business owner doesn't know about my surrogate keys, and frankly, he doesn't give a shit, so long as the system works. He might, however, take umbrage when I tell him that his little change to the invoice ID is going to cause two weeks of extra development time. –  Robert Harvey Nov 6 '12 at 22:42
2  
To put it another way, me and the business owner have an understanding; I don't tell him how to run the company, and he doesn't tell me how to design my database. –  Robert Harvey Nov 6 '12 at 22:48
show 12 more comments

It's good to use IDs because of clustered indexes. IDs are integers (small) and sequential so they are the ideal for clustered indexes.

Also it's good to use IDs because they are surrogates. Surrogates never change. An email address can change causing you headaches to update all the tables that reference the email as a key.

But all tables should not have ID's. The question boils down to 2 things:

  1. can the table benefit from a clustered index?
  2. can the table benefit from a surrogate key?

If the answer to both of those questions is "no" then there is no point of having an ID.

A key/value hash that does not grow at run-time gains no benefit from an ID. The table is a stand alone island so an ID would never be used in a join. All filters will be on the natural key. The ID would just waste space. No benefit from clustering on the ID, no benefit from the surrogate.

Mapping or junction tables should never have their own ID. These are used to implement many-to-many relationships.

There is never a situation where you would join or filter on the ID of a mapping table. All joins and filters would be on the IDs which the mapping table maps. The PK would be the combo of the 2 mapped IDs. The combo of 2 surrogate ID's is a surrogate in it's own right.

Usually you will want 2 separate covering indexes on a mapping table. So each mapped ID can be the dominate field in a covering index. A dedicated ID would just waste space for junctions.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.