Re: Separate PK in Jxn Tbl?

Brian Selzer wrote:

> A typical schema with artificial keys:
>
> Customer {CustomerKey, CustomerNo, ...}
> Key {CustomerKey}, Key {CustomerNo}
>
> Item {ItemKey, ItemNo, ...}
> Key {ItemKey}, Key {ItemNo}
>
> CI {CustomerItemKey, CustomerKey, ItemKey, CustomerItemNo}
> Key {CustomerItemKey}, Key {CustomerKey, ItemKey}
> CI[ItemKey] IN Item[ItemKey]
> CI[CustomerKey] IN Customer[CustomerKey]
>
> SOLine {SOLineKey, SOKey, SOLineNo, CustomerItemKey, Quantity, Price}
> Key {SOLineKey}, Key {SOKey, SOLineNo}
> SOLine[CustomerItemKey] IN CI[CustomerItemKey]
>
>
> A typical schema with natural keys
>
> Customer {CustomerNo, ...}
> Key {CustomerNo}
>
> Item {ItemNo, ...}
> Key {ItemNo}
>
> CI {CustomerNo, ItemNo, CustomerItemNo}
> KEY {CustomerNo, ItemNo}
> CI[CustomerNo] IN Customer[CustomerNo]
> CI[ItemNo] IN Item[ItemNo]
>
> SOLine {SO#, SOLineNo, CustomerNo, ItemNo, Quantity, Price}
> SOLine[CustomerNo, ItemNo] IN CI[CustomerNo, ItemNo]
>
>
> 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.

Brian,

Thanks for attempting a concrete example to show your point. Although the first schema doesn't look like something I would have created I will go over your example and post back later, perhaps when the dust has settled a bit in this thread. In spite of my sometimes unorthodox ways I am a reasonable person who is always looking for better ways to do things.

James A. Fortune
MPAPoster_at_FortuneJames.com Received on Mon Jan 28 2008 - 22:26:43 CET