Surrogate Vs Natural Keys

[Varun Chopra]

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.

[Paul Sturrock]

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.

[Varun Chopra]

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?