A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Separate PK in Jxn Tbl?



 
 
Thread Tools Display Modes
  #111  
Old January 28th, 2008, 12:50 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Brian Selzer
external usenet poster
 
Posts: 32
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.

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 don't think it's exactly the same situation. As long as the data resides
in the same table, it is possible to create several indexes over several
columns in order to improve query performance--including join performance.
It's a lot harder to do that when the data is spread out over several
tables, as can be seen from the artificial key example. (You could use
indexed views, perhaps, if you know ahead of time which queries will be
run.)

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







  #112  
Old January 28th, 2008, 01:33 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
-CELKO-
external usenet poster
 
Posts: 26
Default Separate PK in Jxn Tbl?

Much of the early (Bowdlerized) description of relational databases was written with reference back to the (at the time) more familiar CODASYL databases.

I was on X3H2 when we were still working on NDL and I have a copy
somewhere next to my COBOL manual autographed by Grace Hopper.

  #113  
Old January 28th, 2008, 03:31 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
JOG
external usenet poster
 
Posts: 30
Default Separate PK in Jxn Tbl?

On Jan 27, 8:33 pm, "David W. Fenton"
wrote:
JOG wrote
m:

I certainly don't think developers should excuse sloppy RDBMS
design just because they are using access (and of course I'm sure
many of the professionals here wouldn't dream of doing so, despite
others laxness).


What *are* you talking about?

Any mistakes in schema design that you can make in Access, you can
make in any other RDBMS.


*Sigh*. Yes, but as bob has pointed out, you've misconstrued my point.
Because it is marketed at different business problems (ones with few
concurrent users, simple domains, comparatively smaller schema), a lot
of Access users can get away with mistakes that someone using, say,
Oracle 11g to keep track of millions of facts would in the end get
called up on. So that's nothing to do with the technology, just the
market, which makes your empassioned defence of the super-duper
jetomatic engine a bit misplaced.

I'll also ignore the diatribe that followed in light of your
misunderstanding. (And the fact that you share my mother's maiden
name, so may well be long distant family...).


I would agree that there are many places that the wizards in Access
and the sample databases encourage sub-optimal practices. But most
professional developers aren't using either wizards or the sample
databases as their models for developing their own applications.

If your impression of Access comes from futzing with it for 10
minutes and from encountering kludged-together apps created by your
company receptionist, then you just haven't a clue what Access
offers, either as an application development platform or as a data
store (using its native Jet engine).

This is a schema question, and that is orthogonal to Access, because
Access is an application development platform. If you use Jet for
your data store, then Jet is relevant to the discussion. But you can
use any data store that offers an ISAM or ADO or ODBC drivers, and
that means all your schema issues are completely divorced from
Access itself.

That you can't seem to keep this distinction clear in your mentions
of Access demonstrates pretty clearly that you are completely
clueless about Access


Well, apart from this one. Up yer bum. That's a term of endearment in
the UK (from whence your name originates), of course


and really aren't in any position to be making
disparaging comments about it.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

  #114  
Old January 28th, 2008, 03:39 AM posted to comp.databases.ms-access, comp.databases.theory,microsoft.public.access, microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
JOG
external usenet poster
 
Posts: 30
Default Separate PK in Jxn Tbl?

On Jan 27, 6:16 pm, Marshall wrote:
On Jan 26, 6:09 pm, "James A. Fortune"
wrote:



Marshall wrote:
On Jan 26, 4:26 am, "David Cressey" wrote:


When you want to delete an entry form a junction table, you almost always
know the two FKs that uniquely determine the entry to be deleted. You
almost never know the value of the superflous surrogate key. So it's simple
to use the two FK's as the criterion for deletion than it is to look up the
ID field, and then use that as the basis for deletion.


Yes, exactly.


One of the greatest benefits, and one of the fundamental
differences between how SQL treats data and how
(most) conventional programming languages treat data
is that in SQL we specify data by its value, instead of by
location. I often observe that superfluous keys in the field
are an attempt to make SQL data have an address, to
make it behave the way the programmer's mental model
(perhaps influenced by years of using pointers) does.


Personally, I don't take the natural keys out either, so they
can still be used for the deletion.


Since natural keys are data that needs to be managed, this
is an unsurprising claim. I don't throw out the data I'm supposed
to be managing either. Also consider this thread is about junction
tables. Imagine what you would have left if you threw out the
natural key of a junction table: nothing! Just the capriciously
introduced surrogate key in a table by its lonesome self.

Perhaps I've just stumbled into a sanity check for table
design: every projection of a table should have a meaningful
predicate. (Do I hear JOG's ears pricking up?)


I have no idea what you mean.

Well not since my semantic-2000 buzzword detector imploded when
someone described "web 3.0" to me.

In a junction
table in which a surrogate key has been introduced, what's
the meaning of the projection of the table over the s.k. column?
Oops!

The thought of giving the SQL data an address and
following a programmer's mental model did not enter
into my thinking at all.


Your not being consciously aware of it doesn't mean
it didn't happen. The mind doesn't work like that.

You've been listening to Celko too much.


Who? ;-)

Marshall


  #115  
Old January 28th, 2008, 04:10 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

Jamie Collins wrote:

I'm saddened, although not surprised, at the comments from a few regulars from the
c.d.theory newsgroup using words such as idiot


"Clearly the person is an idiot and is better off employed at a 7-11."
http://groups.google.com/group/micro...3c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/micro...5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/micro...c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/micro...edf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/micro...2d3ed992e9a2ec


chuckle You almost got me there. However I was ridiculuing other people who
weren't part of the conversation. Not much of an excuse.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #116  
Old January 28th, 2008, 04:24 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

"Brian Selzer" wrote:

Only an idiot would have a rule for no particularly good reason. Only an
imbecile would follow such a rule. A strong argument can be made for
using
autonumber primary keys--especially if the target DBMS doesn't support FOR
EACH ROW triggers--but to just blythely add them for no particularly good
reason is a recipe for disaster.


My reasons are, in my opinion, good reasons. Not great but good. You
don't like
them? Tough.


So now they're good reasons? In your earlier post, you said they weren't
good reasons. Can't you make up your mind? You also haven't stated your
reasons. How can I like them or not like them? I don't know them!


They are good reasons for me and, in my opinion, for many or all other users of
Access. I flippantly stated "no particular good reason" as I was thinking about
some of the regulars in the comp.databases.theor newsgroup and what I assumed would
be there viewpoint.

I was not speaking of corruption due to disk failures; I was instead
referring to permitting garbage into the database due to the misuse of
auto-number primary keys.


Ok, not quite sure what you mean by this. Presumably because a unique index wasn't
declared on other fields such as, for a parts table, a part number. Which can be
duplicated by multiple manufacturers so even there that's not quite a good example.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #117  
Old January 28th, 2008, 04:28 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

-CELKO- wrote:

I always use an autonumber PK and a unique index set on the two FK fields [sic]. Why? No particular good reason. One of my database rules is that all tables have an autonumber primary key [sic].


You add redundancy to a schema and never thought about it?


I have and I'm quite comfortable with it.

If you had
gone thru the Normalization process, this would stick out as a
fundamental design error immediately. You have not been writing SQL;
you are faking a sequential file system in SQL and even say "field"
instead of "column" -- huge conceptual and implementation differences!


Ok.

Now if I was to have a child table [sic: that terms comes from Network DBs; did you used to program in IMS?] from the junction table [sic: did you mean a table that models a relationship among many entities? Or a multi-way pointer structure as in an Network DB?] then I would absolutely use a autonumber primary key [sic: it cannot be a key by definition] for ease of use when designing queries, forms and reports.


Never woked in IMS or Network DBs. These are common terminology in the Access world
so that's what I use.

I hope that you are not designing forms and reports in the database.


You've never used Access then?

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #118  
Old January 28th, 2008, 04:33 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

rkc wrote:

Yes. Because Access developers have no need to separate data access
from user interface from business rules. Just bind a form to a
recordsource, sprinkle some VBA code in a few events and run with it.


I sure would like to have that middle tier with business rules and also have the
power of Access and RAD.

Tony

--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #119  
Old January 28th, 2008, 04:38 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Tony Toews [MVP]
external usenet poster
 
Posts: 3,776
Default Separate PK in Jxn Tbl?

"James A. Fortune" wrote:

I am only speaking for myself. I may be the only Access programmer on
the planet who validates input the way I do in code.


Not sure exactly what mean mean by that statement but I do a lot of validating on
input as well. And I very much try to keep one form updating one table so as to
not have to duplicate such data.

I'd very much like to see a business rules layer implemented that worked well within
Access. However I rather much doubt we'll ever see it given that Microsoft is
focusing their attentions on the office information worker rather than the developer.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/
  #120  
Old January 28th, 2008, 08:07 AM posted to comp.databases.ms-access,comp.databases.theory,microsoft.public.access,microsoft.public.access.tablesdbdesign,microsoft.public.sqlserver
Jon Heggland
external usenet poster
 
Posts: 6
Default Separate PK in Jxn Tbl?

Quoth David W. Fenton:
Any database engine can have the schema defined in a way that will
allow duplicates.


Nonsense. Any /SQL-based/ database engine, perhaps.
--
Jon
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 04:49 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.