I'm trying to understand the performance implications of using an identifier (basically surrogate key) that has no business meaning, instead of using a natural primary key (composite key in my case). The Hibernate best practices says "there are all sorts of reasons why you should use identifiers" - i think i understand at least some of the benefits of using them. However, I'm concerned about performance - database performance, not Hibernate's in-memory caching or object graph navigation performance. Simplistically, if I use natural primary keys, I have one constraint in the database for that table; if I use a surrogate PK, and "natural-id" to ensure uniqueness of the "business keys", I have 2 constraints.
Any thoughts on this?

Thanks

Vijay

I use natural keys

Surrogate primary keys mean that some poor maintenance programmer is less likely to tear their eyeballs out in a fit of rage in ten years time when they have to make some change to your system. Your natural primary key might seem fixed in stone and unchanging but this is not guraranteed.

Also never think your system won't be used in ten years time, I work for a massive comapny still using NT4 and a 15 year old mainframe app.

I use natural keys for 20 years - relational database theory don't force usage surrogate keys - I don't know for advantage, it is natural design for me and I don't know different

When I make sql query I know natural keys and I make query easier

Surrogate keys have own advantage, but and natural keys too
I disagree that natural keys are bad design
I think that you use what you like

Hibernate support both

http://www.amazon.com/exec/obidos/tg/de ... 4?v=glance

but it is only opinion

This is link for compare keys http://www.bcarter.com/intsurr1.htm (very good)
and one http://builder.com.com/5100-6388_14-1045050.html (favorite surrogate)

Here are a few rules:

1. Never post anything from Celko. Most of what he does is wrong. Yes, he is popular, but he definitely lacks fundamentals and is regularly debunked.

2. Never confuse "logical" and "physical". The question for primary keys is a very common source of confusion. You chose primary keys based on their logical integrity with the rest of the logical data model you are creating. This has nothing to do with their physical implementation, or the performance characteristics of such an implementation.

3. There is no "always use surrogate keys" or "always use natural keys" theory. Both have their place and use, depending on the logical data model.

Get the Fabian Pascal book, he summarizes these important points nicely.

What I can say ?



I agree.

There is different (for me) between an application for external users (web application, mostly) and an application for internal users (classical information systems, order entry, accounting etc)
I don't know for first type, but I know for last type of application.
For example, my employees or partners have surrogate keys (ID), but internal users use it like natural key - users know partner ID before name or address and search with id easy - what is it : natural or surrogate ? I think that all columns visible to users are natural and I show all columns to users - I don't force that you do it, this is my opinion only.

I disagree with all Natural-Key-Lovers.

Yes, RDBMS theory recommends to have First Normal Form, Second Normal Form, and even Third Normal Form. And, we almost always have composite primary keys (instead of natural candidates + surrogate) in existing production databases. And, we almost never have true Second Normal Form: for performance optimizations, we sometimes have attributes depending on another attributes, additionally to primary key dependency.

Theory, and practice.

Now we have another demand: how to submit Composite PK via GET/POST, via SOAP, etc? How to use it in <OPTION> tag of HTML? What about security? Database identity should not have any business meaning...

We lose performace during inserts, because we should fire two constraints. Who cares!!! Compare 10ms of SQL INSERT execution via Network and JDBC with 0.1ms for firing of additional constraint.

yes,

Natural-Key is perfect for database tools vendors like hibernate (or database vendor ever, but database vendor have resolved this a long time ago), but no for designer of database applications

Best