Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vfj-0007Oj-AS for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 09:47:51 +0000 Received: from localhost ([127.0.0.1] helo=postgresql.org) by malur.postgresql.org with smtp (Exim 4.80) (envelope-from ) id 1V1vfi-0008UQ-J6 for pgsql-sql@arkaria.postgresql.org; Wed, 24 Jul 2013 09:47:50 +0000 Received: from makus.postgresql.org ([2001:4800:7903:4::125]) by malur.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vfh-0008UI-Bh for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 09:47:49 +0000 Received: from mail-pa0-x230.google.com ([2607:f8b0:400e:c03::230]) by makus.postgresql.org with esmtp (Exim 4.80) (envelope-from ) id 1V1vfe-00076Y-2v for pgsql-sql@postgresql.org; Wed, 24 Jul 2013 09:47:48 +0000 Received: by mail-pa0-f48.google.com with SMTP id kp1so455204pab.7 for ; Wed, 24 Jul 2013 02:47:44 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20120113; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; bh=Zip7CSx+Mx9pk2b4gHx9iZ137oHFWrTU96oXZPBSFg0=; b=aUrPBzGx8UlhEnvU79oCXek2/Z6uxMZDUnjGur+NvwJorZaf7IKVWFhUaSlbkX9/kF J/yvv40GZNEZMNGk0V5qe1sQo4br+nVNQenCwQkQm7Lf2g3XNtLRYBxC60NWALviX4Yd k+16OIlsQrdLbxu2cVynH0mHRmvN5YhXv5B3jROlYO0hdszA4MHXCle8Cpot5HKGN1Hm KUiftVitQvoIP1gDv35D+4JWzz9aeomvS+hqiiQ2CM1mtOnrf1XAEt1cCSr8wsZMXhB9 1pu+XJqyp82qhp+XSMiFNoh3+Q1WFOZBwcBFS6FdTJwBAV/RLYDiK/kZhS0cR7AffY44 K13Q== MIME-Version: 1.0 X-Received: by 10.68.200.135 with SMTP id js7mr2122462pbc.173.1374659264889; Wed, 24 Jul 2013 02:47:44 -0700 (PDT) Received: by 10.70.69.7 with HTTP; Wed, 24 Jul 2013 02:47:44 -0700 (PDT) In-Reply-To: References: Date: Wed, 24 Jul 2013 10:47:44 +0100 Message-ID: Subject: Re: Advice on key design From: =?UTF-8?B?QsOocnRvIMOrZCBTw6hyYQ==?= To: Luca Ferrari Cc: JORGE MALDONADO , "pgsql-sql@postgresql.org" Content-Type: multipart/alternative; boundary=047d7b15b0712bc8c104e23ecd45 X-Pg-Spam-Score: 0.7 (/) List-Archive: List-Help: List-ID: List-Owner: List-Post: List-Subscribe: List-Unsubscribe: X-Mailing-List: pgsql-sql Precedence: bulk Sender: pgsql-sql-owner@postgresql.org --047d7b15b0712bc8c104e23ecd45 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable 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=C3=A8rto On 24 July 2013 10:39, Luca Ferrari wrote: > On Wed, Jul 24, 2013 at 10:38 AM, B=C3=A8rto =C3=ABd S=C3=A8ra > 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 > --=20 =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D 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. --047d7b15b0712bc8c104e23ecd45 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable
Hi,

It looks heavy, performance-w= ise. If this is not OLTP intensive you can probably survive, but I'd st= ill really be interested to know ow you can end up having non unique record= s on a Cartesian product, where the PK is defined by crossing the two defin= ing tables. Unless you take your PK down there is no way that can happen, a= nd 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 do= ing.

B=C3=A8rto


On 24 July 2013 10:39, Luca Ferrari = <fluca1978@infinito.it> wrote:
On Wed, Jul 24, 2013 at 10= :38 AM, B=C3=A8rto =C3=ABd S=C3=A8ra <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 man= agement
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



-- =3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D= =3D=3D=3D=3D=3D=3D
If Pac-Man had affected us as kid= s, we'd all be running around in a darkened room munching pills and lis= tening to repetitive music.
--047d7b15b0712bc8c104e23ecd45--