Skip site navigation (1) Skip section navigation (2)

Re: pervasiveness of surrogate (also called synthetic) keys

From: Merlin Moncure <mmoncure(at)gmail(dot)com>
To: Misa Simic <misa(dot)simic(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Date: 2011-05-04 12:34:31
Message-ID: BANLkTimmmh_5_+vSjGaNwwiaNbymB2AJUA@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-general
On Wed, May 4, 2011 at 7:14 AM, Misa Simic <misa(dot)simic(at)gmail(dot)com> wrote:
>
>
> 2011/4/28 Merlin Moncure <mmoncure(at)gmail(dot)com>
>>
>> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer <irrer(at)umich(dot)edu> wrote:
>> *) most tables don't have unique natural keys (let's see em)
>> etc
>>
>
> i.e for an Invoice, we have at least 2 tables (more in practice...):
> Invoice Header
>     -Invoice Number
>     -Date
>     -CustomerID
>     -Currency
> Invoice Line
>     -ItemID
>     -qty
>     -Price
>
> This is not real design for tables, just basic real info stored to represent
> 1 Invoice Document.
> Now, let say for Invoice Header, "Natural" PK is Invoice Number, so we will
> add that column to Invoice Line table as FK....
> What should be "Natural" PK for Invoice Line table?
> Also, if we now add additional tables i.e. Serial Numbers, related to
> Invoice Line, what should be "Natural" key in it?

Most of the old school accounting systems maintained an invoice line number.

> Invoice Line
>     -Invoice Number
>     -LineNo
>     -ItemID
>     -qty
>     -Price

The line number started from 1 (the first line on the invoice) on
every unique invoice.  This has the added benefit of allowing the
primary key index (invoice_no, line_no) allowing you to pull up the
invoice line records in correct order without requiring an extra sort
and, if you don't need any fields from the invoice, a join.

Compare the two queries pulling up invoice lines over a range of
invoice numbers.

merlin

In response to

Responses

pgsql-general by date

Next:From: Misa SimicDate: 2011-05-04 12:50:54
Subject: Re: pervasiveness of surrogate (also called synthetic) keys
Previous:From: Dickson S. GuedesDate: 2011-05-04 12:28:14
Subject: Re: postgresql not updating the sequence

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group