Re: Separate PK in Jxn Tbl?

From: David Cressey <cressey73_at_verizon.net>
Date: Sun, 27 Jan 2008 22:51:52 GMT
Message-ID: <cW7nj.228$Tb6.54_at_trndny07>


"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)> wrote in message news:OowKsDTYIHA.5416_at_TK2MSFTNGP05.phx.gbl...
> 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.
>

I don't believe the above is correct. It's still the case that a graph based DBMS
like IMS can otperform an SQL based DBMS for queries that were anticipated at the time the graph was designed. This is not a function of the
complexity of the query. It's a function of the relationship between the path the query must take through the data and the paths that are made blindingly fast by the graph set up at stata store time.

Essentially, an index gives and SQL engine exactly what it needs to locate table rows: pointers to table rows.

Where the SQL DBMS (and the databases built using it) are superior to a graph based DBMS (and the databases built using it) is flexibility. When an unanticipated query occurs in a graph based system, the DBMS is reduced to doing a brute force search (if it can even do that much). Either that or the database designer must redesign the database, unload and reload all the data, and revise any application programs that were dependent on the old structure. That's usually prohibitively expensive.

By contrast the SQL DBMS can generally build an in memory index when needed, or a new index can be built by the database designer without unloading any data or requiring any revisions to programs.

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

I disagree.

> --
> 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.
> >
> >
> >
>
>
Received on Sun Jan 27 2008 - 23:51:52 CET

Original text of this message