Re: Advice on key design - Mailing list pgsql-sql

From Bèrto ëd Sèra
Subject Re: Advice on key design
Date
Msg-id CAKwGa_9GGKs+YgK8=jAfCHWZoGuGsMXYpG5TaYBSaVoLrHmm0g@mail.gmail.com
Whole thread Raw
In response to Re: Advice on key design  (Luca Ferrari <fluca1978@infinito.it>)
List pgsql-sql
Hi,

yeah, I am okay with design prudence, just used to be so paranoid about performance that just any possible "one more thing to do" gets me nervous :) Language versions do exist, say Dutch has different orthography depending on what convention is used, so you may well need to suddenly add a further level of definition. But you can pretty much do that by a sequence of alter tables, especially in a situation like this.

Oh well, we are discussing principles as applied to a practical situation that we actually ignore, so... 

It's been a pleasure, but unless we get more detail... not much we can do apart from putting out personal preferences :)

Bèrto


On 24 July 2013 11:05, Luca Ferrari <fluca1978@infinito.it> wrote:
On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com> wrote:
> Hi,
>
> It looks heavy, performance-wise. If this is not OLTP intensive you can
> probably survive, but I'd still really be interested to know ow you can end
> up having non unique records on a Cartesian product, where the PK is defined
> by crossing the two defining tables. Unless you take your PK down there is
> no way that can happen, and even if it does, a cartesian product defining
> how many languages a user speaks does not look like needing more than
> killing doubles. So what would be the rationale for investing process into
> this?


You are probably right: you are like to never refactor this kind of
design, and this situation using a surrogate key is useless. But what
happens if your language is no more uniquely identified by
lpp_language_id? Suppose you need to track also the language version
and therefore a language is identified by the couple (id, version). In
this case you have to refactor two tables: the language one and the
person-language join table.
Having a surrogate key on both sides allows you to smoothly add such
constraint without having to refactor the latter table and ensuring
all previous joins still work.
Ok, not a really smart example, but the only one that comes into my
mind at the moment.

Luca



--
==============================
If Pac-Man had affected us as kids, we'd all be running around in a darkened room munching pills and listening to repetitive music.

pgsql-sql by date:

Previous
From: Luca Ferrari
Date:
Subject: Re: Advice on key design
Next
From: Luca Ferrari
Date:
Subject: Re: monthly statistics