Re: Separate PK in Jxn Tbl?
Date: Sun, 27 Jan 2008 23:22:27 -0800
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:
- Assuming proper indexes, the extra time required to process the extra joins should be negligible.
- 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.)