• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

Surrogate Vs Natural Keys

 
Ranch Hand
Posts: 213
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator
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.



 
Bartender
Posts: 10336
Hibernate Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator


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
Ranch Hand
Posts: 213
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Quote
  • Report post to moderator

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?
 
reply
    Bookmark Topic Watch Topic
  • New Topic