Re: Separate PK in Jxn Tbl?

This remind me of the discussion twenty five years ago between relational databases (like SQL-Server) and non-relational databases (like ISAM databases, dBase, etc.). It has always been right to say that for simple queries, non-relational databases are faster than relational databases. However, nowadays, non-relational databases have (practically) vanished because of their inherent slowness when the queries become more and more complicated.

This is exactly the same situation with the possibility of accelerating a query by using a natural key: you are accelerating simple queries that are already light and fast but on the opposite side, you are slowing down complexe queries that are already big and slow. Not sure if going this way is really advantageous.

-- 
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"Brian Selzer" <brian_at_selzer-software.com> wrote in message 
news:baWmj.3398$nK5.1213_at_nlpi069.nbdc.sbc.com...


>

> <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.

>

>

>