Re: pervasiveness of surrogate (also called synthetic) keys

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