Re: Surrogate primary key plus unique constraint vs. natural primary key: data integrity?

On Friday, March 15, 2013 2:03:43 AM UTC+2, Jan Hidders wrote:
>
> > - Performance (?)
>
>
> Doubtful in my experience. Programmers tend to think that since the
> join condition becomes simpeler the joins become faster, but that is
> usually not the bottle neck. There can be a observable gain if the size
> > of many records decreases, which might typically happen for
> cross-reference tables representing relationships. But significant
> gains are rare in my experience.
>

That’s why I put a "?".

> > - You need more joins (performance?)
>
> Really? Why you think that? One typically would have the same number of
> tables in the schema, so why would we need more joins?
>

Let’s consider the relation reviews{reviewer, review-language, book, rating} and tuple {34, 14, 66734, 4}. Can you tell anything about it? Now replace with {34, 'EN', 'ISBN-XXXXX’, 'verry good'}. Do we need any join if we want to search for a specific book, review language or rating?

Also if you have something like D -> C -> B -> A, each relation having it’s own surrogate, then you cannot join A and D directly (pretty stupid design, but found in practice).

>
> > - You lose the simplicity and power of overlapping composite foreign
> > keys constraints (discussed in this thread).
>
> Using surrogate keys does not imply that you cannot use composite foreign keys.
>

The problem may appear only if you replace a natural composite key by a surrogate. Received on Fri Mar 15 2013 - 13:01:45 CET