[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-sql
Subject: Re: [SQL] Advice on key design
From: Bèrto ëd Sèra <berto.d.sera () gmail ! com>
Date: 2013-07-24 10:12:35
Message-ID: CAKwGa_9GGKs+YgK8=jAfCHWZoGuGsMXYpG5TaYBSaVoLrHmm0g () mail ! gmail ! com
[Download RAW message or body]
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.
[Attachment #3 (text/html)]
<div dir="ltr">Hi,<div><br></div><div>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.</div> <div><br></div><div>Oh well, \
we are discussing principles as applied to a practical situation that we actually \
ignore, so... <br><br>It's been a pleasure, but unless we get more detail... not \
much we can do apart from putting out personal preferences :)</div> \
<div><br></div><div>Bèrto</div></div><div class="gmail_extra"><br><br><div \
class="gmail_quote">On 24 July 2013 11:05, Luca Ferrari <span dir="ltr"><<a \
href="mailto:fluca1978@infinito.it" \
target="_blank">fluca1978@infinito.it</a>></span> wrote:<br> <blockquote \
class="gmail_quote" style="margin:0 0 0 .8ex;border-left:1px #ccc \
solid;padding-left:1ex"><div class="im">On Wed, Jul 24, 2013 at 11:47 AM, Bèrto ëd \
Sèra <<a href="mailto:berto.d.sera@gmail.com">berto.d.sera@gmail.com</a>> \
wrote:<br>
> Hi,<br>
><br>
> It looks heavy, performance-wise. If this is not OLTP intensive you can<br>
> probably survive, but I'd still really be interested to know ow you can \
end<br> > up having non unique records on a Cartesian product, where the PK is \
defined<br> > by crossing the two defining tables. Unless you take your PK down \
there is<br> > no way that can happen, and even if it does, a cartesian product \
defining<br> > how many languages a user speaks does not look like needing more \
than<br> > killing doubles. So what would be the rationale for investing process \
into<br> > this?<br>
<br>
<br>
</div>You are probably right: you are like to never refactor this kind of<br>
design, and this situation using a surrogate key is useless. But what<br>
happens if your language is no more uniquely identified by<br>
lpp_language_id? Suppose you need to track also the language version<br>
and therefore a language is identified by the couple (id, version). In<br>
this case you have to refactor two tables: the language one and the<br>
person-language join table.<br>
Having a surrogate key on both sides allows you to smoothly add such<br>
constraint without having to refactor the latter table and ensuring<br>
all previous joins still work.<br>
Ok, not a really smart example, but the only one that comes into my<br>
mind at the moment.<br>
<span class="HOEnZb"><font color="#888888"><br>
Luca<br>
</font></span></blockquote></div><br><br clear="all"><div><br></div>-- \
<br>==============================<br><span \
style="font-family:Verdana,Arial,Helvetica,sans-serif;font-size:12px;line-height:17px">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.</span> </div>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic