Do database tables need to have IDs?

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?

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.

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.

In every database schema there could be many tables.

In some tables the natural key is a good key.
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.

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.

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:

can the table benefit from a clustered index?
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.

Filed under: softwareengineering - @ 20:05

Thẻ: database

Thiết kế website giá rẻ

Danh mục

Do database tables need to have IDs?

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