pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedFrom: Bèrto ëd Sèra <berto.d.sera@gmail.com>
To: Luca Ferrari <fluca1978@infinito.it>
Cc: JORGE MALDONADO <jorgemal1960@gmail.com>
Cc: pgsql-sql@postgresql.org <pgsql-sql@postgresql.org>
Subject: Re: Advice on key design
Date: Wed, 24 Jul 2013 10:47:44 +0100
Message-ID: <CAKwGa_8T0H4Mp7=cO2VGiaPdpXJGo33CmUZSe_bVEVJ2Yf7dhA@mail.gmail.com> (raw)
In-Reply-To: <CAKoxK+57gzt1zQsVXRPmswqV=r5UQPxCBoHbnEh2zzJKe=r9vQ@mail.gmail.com>
References: <CAAY=A7_ZKoCRjP19oLRYfQeV1=Q=c-3cX+qDmPfZC8_o_B8msQ@mail.gmail.com>
<CAKwGa_87zf_A2e7Canqru1Btuuy+U+t_MBpDRAQyv_tvQsAo1A@mail.gmail.com>
<CAKoxK+57gzt1zQsVXRPmswqV=r5UQPxCBoHbnEh2zzJKe=r9vQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
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?
Get me right, just trying to understand what you guys are doing.
Bèrto
On 24 July 2013 10:39, Luca Ferrari <fluca1978@infinito.it> wrote:
> On Wed, Jul 24, 2013 at 10:38 AM, Bèrto ëd Sèra <berto.d.sera@gmail.com>
> wrote:
>
> > What would be the rationale behind the serial number?
> >
>
> The serial key, also named "surrogate key" is there for management
> purposes. Imagine one day you find out your database design is wrong
> and what was unique the day before is no more so, how can you find
> your records?
> The idea is to have a surrogate key to save you from real world
> troubles, and then constraints to implement the database design.
>
> I usually use this convention:
> - primary surrogate keys named pk and defined as primary keys
> - database design keys named _key and defined with a unique constraint.
>
> 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.
view thread (10+ messages) latest in thread
Message-ID: <CAKwGa_8T0H4Mp7=cO2VGiaPdpXJGo33CmUZSe_bVEVJ2Yf7dhA@mail.gmail.com>
Permalink: ../CAKwGa_8T0H4Mp7=cO2VGiaPdpXJGo33CmUZSe_bVEVJ2Yf7dhA@mail.gmail.com/
Also on: postgresql.org/message-id/CAKwGa_8T0H4Mp7=cO2VGiaPdpXJGo33CmUZSe_bVEVJ2Yf7dhA@mail.gmail.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: berto.d.sera@gmail.com, fluca1978@infinito.it, jorgemal1960@gmail.com
Subject: Re: Advice on key design
In-Reply-To: <CAKwGa_8T0H4Mp7=cO2VGiaPdpXJGo33CmUZSe_bVEVJ2Yf7dhA@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox