View Single Post
  #108  
Old January 27th, 2008, 10:51 PM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
David Cressey[_2_]
external usenet poster
 
Posts: 50
Default Separate PK in Jxn Tbl?


"Sylvain Lafontaine" sylvain aei ca (fill the blanks, no spam please)
wrote in message ...
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" wrote in message
...

wrote in message

...
On Jan 27, 12:39 am, "Brian Selzer" wrote:
"James A. Fortune" 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.