On Wed, May 4, 2011 at 7:14 AM, Misa Simic <misa.simic@xxxxxxxxx> wrote: > > > 2011/4/28 Merlin Moncure <mmoncure@xxxxxxxxx> >> >> On Thu, Apr 28, 2011 at 12:29 PM, Jim Irrer <irrer@xxxxxxxxx> 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
