Re: Advice on key design

On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd Sèra <berto(dot)d(dot)sera(at)gmail(dot)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