Re: are primary keys always 'needed'

"Michael Glaesemann" <grzm(at)seespotcode(dot)net> wrote in message
news:E93B93F4-8616-4A23-8AC5-84749581E067(at)seespotcode(dot)net(dot)(dot)(dot)
>
> On Feb 28, 2010, at 11:38 , Serge Fonville wrote:

snip

>> I did a lot of googling and found very varying opinions.
>> Mainly:
>> - Always use a primary key, no reason why, it's just 'better'
>> - Use primary keys when it makes sense.
>>
>> How do I determine what 'sense' or 'better' mean?
>
> This depends on your application. Here are a few things to think about.
>
> * Surrogate keys require joins or lookups to retrieve the actual data
> values they represent.
> * Surrogate keys require additional space in the table that holds the
> actual data values and an extra index to enforce the uniqueness of these
> values.
> * Surrogate keys are often smaller in terms of size on disk than the data
> they refer to. This counts for both tables including the surrogate key
> and the indexes that include them.
> * Integer surrogate keys used to represent text data are often faster to
> look up, as integer comparisons are faster than string comparisons.
>
> This is not an exhaustive list. Some people are vehemently opposed to
> surrogate keys; others use them blindly. I think there are cases where
> using surrogate keys does make sense, taking into account the trade- offs
> when using them.
>

If you are planning on using an ORM framework such as "Hibernate" on top of
this schema you will want to pay more attention to this PK issue (surrogate
vs. natural) as it was a pain for us to setup primary keys on multi-columns
initially. As well most ORM frameworks will want/require a PK on a table to
operate properly.

There was a post/link on tips to follow if using Hibernate/PostgreSQL which
I saved somewhere but can't easily find right now.

Regards, Eric