Re: Separate PK in Jxn Tbl?

<CDMAPoster_at_fortunejames.com> wrote in message news:db4e7666-f9ef-4c72-a483-f951b80c6183_at_k39g2000hsf.googlegroups.com... On Jan 27, 12:39 am, "Brian Selzer" <br..._at_selzer-software.com> wrote:
> "James A. Fortune" <MPAPos..._at_FortuneJames.com> wrote in messagenews:%

> > Access programmers use forms to interact with the data. If I follow
> > Jamie's advice and constrain the data at both the table level and in
> > code,
> > then your points make more sense. Right now, they're just arguments for
> > me not to constrain the data at the table level because the reasons you
> > gave might make natural keys preferable in that situation :-).
>
> Well, that's just dumb. Checks in code can reduce database round-trips,
> and
> therefore can improve performance, but are not and cannot be a substitute
> for constraints on the tables. It is the constraints on the tables that
> keeps garbage out of the database.

>>>>>

If the users only access the tables through forms, conforming to best practices in Access, how are they going to get garbage into the tables? Now if you're trying to keep Jamie and his Excel SQL out of your database, that's another story :-). <<<<<

There can be several forms that access the same table, so you would have to duplicate the code behind each form that accesses a table, or you can get garbage into the database.

> >> * Referencing an artificial key in a child table can complicates
> >> queries - and not just with a longer restrict clause, but with a whole
> >> extra join that may well have been unrequired if a natural key had
> >> been used.
>
> > I don't agree with that point. The child table can contain the
> > AutoNumber
> > primary key from the main table as a foreign key if desired. I don't see
> > how using the natural key fields requires less joins than that. Maybe an
> > example would help me understand what you mean.
>
> An extra join may be needed if the natural key from the parent table is
> used
> in a restrict clause. If all you have is the artificial key from the
> parent
> table, then you have to join in order to access the natural key columns.
> With natural keys, the natural key values from the parent table also
> appear
> in the child table, so there isn't any need to join. Bottom line: joins of
> artificial keys are typically faster than joins of natural keys due to the
> size of the comparands, but with natural keys, fewer joins may be needed..

If you're planning on using a natural key column in the child table as part of a join then doesn't it make sense to include that field in the child table?

Still waiting...

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. Received on Sun Jan 27 2008 - 08:13:11 CET