Re: Separate PK in Jxn Tbl?

From: Ed Murphy <>
Date: Sun, 27 Jan 2008 23:22:27 -0800
Message-ID: <479d834b$0$6143$>

Brian Selzer wrote:

> Now write a query that returns how many of item '12345' were sold to
> customer '4321'
> It should be obvious that with the natural keys, no joins are
> necessary--it's just a simple select from SOLine since all of the
> information is actually /in/ SOLine; whereas, with the artifical keys,
> several joins are required because in order to query by item number and
> customer number, SOLine must be joined to CI which must then be joined to
> Customer and Item.

I'm sure you already know this, but:

  1. Assuming proper indexes, the extra time required to process the extra joins should be negligible.
  2. If the natural keys are changeable, then synthetic keys are a Big Win. (Example: One of my higher-end clients has lots of items that are essentially the same except for color, and routinely discontinues slow-moving options - including, for various reasons, prepending 'Z' to the item number.)
Received on Mon Jan 28 2008 - 08:22:27 CET

Original text of this message