On Wed, May 4, 2011 at 7:14 AM, Misa Simic <misa.simic@gmail.com> wrote:
>
>
> 2011/4/28 Merlin Moncure <mmoncure@gmail.com>
>>
>> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer <irrer@umich.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
