aspose file tools*
The moose likes Object Relational Mapping and the fly likes Surrogate Vs Natural Keys Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » Object Relational Mapping
Bookmark "Surrogate Vs Natural Keys" Watch "Surrogate Vs Natural Keys" New topic
Author

Surrogate Vs Natural Keys

Varun Chopra
Ranch Hand

Joined: Jul 10, 2008
Posts: 211
I want to ask this question again from ORM perspective (Hibernate/JPA with Oracle behind).
We are changing our DB design and one very important change we can make right now is to use surrogate keys in place of natural keys.
My questions in this regard are:

1. I understand in general people believe that if a single column natural key is available then use that instead of surrogate key. But this means some of the keys will be numeric type while others will be string (or date in some cases). From ORM perspective is it worthwhile to keep all keys of same type (say number)? Does it help increase code re-usability and less maintenance in future?

2. For relation tables, is there any benefit of adding a surrogate key to avoid composite key? Mostly we have 2 columns in relation tables (both FKs). It is rare that we will search a relation based on its PK.




-Varun -
(My Blog) - Online Certifications - Webner Solutions
Paul Sturrock
Bartender

Joined: Apr 14, 2004
Posts: 10336


1. I understand in general people believe that if a single column natural key is available then use that instead of surrogate key. But this means some of the keys will be numeric type while others will be string (or date in some cases). From ORM perspective is it worthwhile to keep all keys of same type (say number)? Does it help increase code re-usability and less maintenance in future?

I'd be wary of natural keys, because they have the irritating habit of changing as business requirements change. The only purpose of a primary key is so a relational database can identify rows of data, they don't really have any meaning outside a database. So I favour using a database-only key that's nothing to do with the data it identifies. From the ORM's point of view it doesn't really matter if you have mixed key types just so long as you have a unique identifier.


2. For relation tables, is there any benefit of adding a surrogate key to avoid composite key? Mostly we have 2 columns in relation tables (both FKs). It is rare that we will search a relation based on its PK.

Yes. Composite keys add extra work. Surrogate keys remove this work. OK, so you never search based on surrogate key, but your ORM will rely on it to track object identity.


JavaRanch FAQ HowToAskQuestionsOnJavaRanch
Varun Chopra
Ranch Hand

Joined: Jul 10, 2008
Posts: 211
OK, so you never search based on surrogate key, but your ORM will rely on it to track object identity.


Thanks for replying. Regarding above point, can you refer me to a link on web for more details?
 
With a little knowledge, a cast iron skillet is non-stick and lasts a lifetime.
 
subject: Surrogate Vs Natural Keys