Separate PK in Jxn Tbl?

Whenever I've created junction tables in the past, I always made the PK of
the junction table the combined pks from the two other tables. Ex: Table1
PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
together comprise the PK for the junction table.

However, I just came across some code in which the person created a junction
table with a separate PK consisting of an autonumber field, and then the two
fields.

So I was wondering how others did junction tables -- with a standalone
autonumber PK, or with a PK consisting of the PKs of the tables being
joined? And, if a standalone PK, then why?

Thanks!

Neil

Re: Separate PK in Jxn Tbl?

I always use just the 2 primary keys but....
If I were a library lending 2 undiffentiatable copies of 1 book to the same
person, I suppose I would need an Autonumber PK plus A BookID and
BorrowerID.

Phil

"Neil" wrote in message
news:6kslj.128$J41.113@newssvr14.news.prodigy.net...
> Whenever I've created junction tables in the past, I always made the PK of
> the junction table the combined pks from the two other tables. Ex: Table1
> PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
> together comprise the PK for the junction table.
>
> However, I just came across some code in which the person created a
> junction table with a separate PK consisting of an autonumber field, and
> then the two fields.
>
> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?
>
> Thanks!
>
> Neil
>

Re: Separate PK in Jxn Tbl?

"Phil Stanton" wrote in message
news:13pclc9bkkveq4e@corp.supernews.com...
>I always use just the 2 primary keys but....
> If I were a library lending 2 undiffentiatable copies of 1 book to the
> same person, I suppose I would need an Autonumber PK plus A BookID and
> BorrowerID.

Hmm. Not persuaded. At best you have concealed a problem with the business
process, at worst you have allowed the very same copy to be lent multiple
times without detection.

I say this with some passion, having come home to find my cash register
receipt shows a single bottle of gin got scanned twice.

Roy

Re: Separate PK in Jxn Tbl?

"Neil" wrote in message
news:6kslj.128$J41.113@newssvr14.news.prodigy.net...

> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?

With a PK consisting of the FKs that reference the tables being joined.

Is that what you meant?

Re: Separate PK in Jxn Tbl?

On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" wrote:

>So I was wondering how others did junction tables -- with a standalone
>autonumber PK, or with a PK consisting of the PKs of the tables being
>joined? And, if a standalone PK, then why?

I've done both; the separate primary key is (strictly speaking) never
necessary, but it can be handy if the junction table is itself related
one-to-many to an additional table or tables. Access doesn't make multifield
foreign keys all that easy to use (e.g. you can't use them in a combobox
without some messy code).

If I do so, I will always specify a unique (non-primary) Index on the two
parent foreign key fields.

John W. Vinson [MVP]

Re: Separate PK in Jxn Tbl?

Roy Hann wrote:

> "Phil Stanton" wrote in message
> news:13pclc9bkkveq4e@corp.supernews.com...
>
>>I always use just the 2 primary keys but....
>>If I were a library lending 2 undiffentiatable copies of 1 book to the
>>same person, I suppose I would need an Autonumber PK plus A BookID and
>>BorrowerID.
>
> Hmm. Not persuaded. At best you have concealed a problem with the business
> process, at worst you have allowed the very same copy to be lent multiple
> times without detection.
>
> I say this with some passion, having come home to find my cash register
> receipt shows a single bottle of gin got scanned twice.
>
> Roy

From that last anecdote, one can conclude one of two things: 1) gin was
only a small part of the overal liquor purchase or 2) you were too drunk
to notice.

Either way, you are a lush! ;)

Re: Separate PK in Jxn Tbl?

Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time to
see if there is a different number Ah!

Phil

"Roy Hann" wrote in message
news:obednVDy8opSxAvanZ2dneKdnZydnZ2d@pipex.net...
> "Phil Stanton" wrote in message
> news:13pclc9bkkveq4e@corp.supernews.com...
>>I always use just the 2 primary keys but....
>> If I were a library lending 2 undiffentiatable copies of 1 book to the
>> same person, I suppose I would need an Autonumber PK plus A BookID and
>> BorrowerID.
>
> Hmm. Not persuaded. At best you have concealed a problem with the
> business process, at worst you have allowed the very same copy to be lent
> multiple times without detection.
>
> I say this with some passion, having come home to find my cash register
> receipt shows a single bottle of gin got scanned twice.
>
> Roy
>

Re: Separate PK in Jxn Tbl?

On Jan 22, 8:43 pm, "Phil Stanton" wrote:
> I always use just the 2 primary keys but....
> If I were a library lending 2 undiffentiatable copies of 1 book to the same
> person, I suppose I would need an Autonumber PK plus A BookID and
> BorrowerID.

So, say that book was "war and peace" and it was john who took it out
on loan, then you would be recording the propositions:

John has on loan the book "War and Peace" and
John has on loan the book "War and peace"

Genius ;) This is actually a relatively serious design flaw, as you
are trying to state the exact same fact twice. If the two books that
John took out really are indiscernible, then the proposition we have
actually recognized is:

John has on loan 2 copies of the book "War and Peace"


>
> Phil
>
> "Neil" wrote in message
>
> news:6kslj.128$J41.113@newssvr14.news.prodigy.net...
>
> > Whenever I've created junction tables in the past, I always made the PK of
> > the junction table the combined pks from the two other tables. Ex: Table1
> > PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
> > together comprise the PK for the junction table.
>
> > However, I just came across some code in which the person created a
> > junction table with a separate PK consisting of an autonumber field, and
> > then the two fields.
>
> > So I was wondering how others did junction tables -- with a standalone
> > autonumber PK, or with a PK consisting of the PKs of the tables being
> > joined? And, if a standalone PK, then why?
>
> > Thanks!
>
> > Neil

Re: Separate PK in Jxn Tbl?

"Bob Badour" wrote in message
news:479667ab$0$4030$9a566e8b@news.aliant.net...

> From that last anecdote, one can conclude one of two things: 1) gin was
> only a small part of the overal liquor purchase or 2) you were too drunk
> to notice.

Actually I got the kids to buy it on the way home from school. :-)

Roy

Re: Separate PK in Jxn Tbl?

"John W. Vinson" wrote in message
news:78ocp31i3n533hh069svjlsci7bp9uc68b@4ax.com...
> On Tue, 22 Jan 2008 20:26:42 GMT, "Neil" wrote:
>
>>So I was wondering how others did junction tables -- with a standalone
>>autonumber PK, or with a PK consisting of the PKs of the tables being
>>joined? And, if a standalone PK, then why?
>
> I've done both; the separate primary key is (strictly speaking) never
> necessary, but it can be handy if the junction table is itself related
> one-to-many to an additional table or tables. Access doesn't make
> multifield
> foreign keys all that easy to use (e.g. you can't use them in a combobox
> without some messy code).
>
> If I do so, I will always specify a unique (non-primary) Index on the two
> parent foreign key fields.
>
> John W. Vinson [MVP]


Good to know. That makes sense, about needing the PK to refer separately to
the junction table, if that situation exists.

Re: Separate PK in Jxn Tbl?

"Neil" wrote:

>However, I just came across some code in which the person created a junction
>table with a separate PK consisting of an autonumber field, and then the two
>fields.
>
>So I was wondering how others did junction tables -- with a standalone
>autonumber PK, or with a PK consisting of the PKs of the tables being
>joined? And, if a standalone PK, then why?

I always use an autonumber PK and a uniqui index set on the two FK fields.

Why? No particular good reason. One of my database rules is that all tables have
an autonumber primary key. It's also slightly easier to delete the record in code.

Now if I was to have a child table from the junction table then I would absolutely
use a autonumber primary key for ease of use when designing queries, forms and
reports.

The theorists will argue. I don't care.

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/

Re: Separate PK in Jxn Tbl?

"Roy Hann" wrote

> Actually I got the kids to buy it on the way home from school. :-)

That could have happened where I grew up -- it was a dry county, but liquor
was available from bootleggers and a few people who ran their own stills.

Re: Separate PK in Jxn Tbl?

On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:
> I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> Why? No particular good reason. One of my database rules is that all tables have
> an autonumber primary key.
>
> The theorists will argue. I don't care.

Would you care for a pragmatist to offer a counter argument?

I never seek to add columns where an attribute does not exist in the
reality being modelled; sometimes I do end up adding something
'artificial' but only when there is a "good data modelling" reason for
doing so.

In the scenario described, there is a two-column "all key" table which
means it is in 5NF. From a "data modelling" point of view I can't see
a case for adding an 'artifical key'.

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 23, 9:05 am, Jamie Collins wrote:
> On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:
>
> > I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> > Why? No particular good reason. One of my database rules is that all tables have
> > an autonumber primary key.
>
> > The theorists will argue. I don't care.
>
> Would you care for a pragmatist to offer a counter argument?
>
> I never seek to add columns where an attribute does not exist in the
> reality being modelled; sometimes I do end up adding something
> 'artificial' but only when there is a "good data modelling" reason for
> doing so.
>
> In the scenario described, there is a two-column "all key" table which
> means it is in 5NF. From a "data modelling" point of view I can't see
> a case for adding an 'artifical key'.

To the OP, I agree with this point of view. Again from a totally
practical standpoint, it is nonsense adding an artificial key would
not "make it slightly easier to delete records", because when
something changes in the real world, you need that data that made up
the 'original' superkey to know which row to delete anyhow.

I'd ignore anyone who claims to be telling you based on a purely
"practical" /or/ purely theoretical standpoints. Its always important
to be aware of both extremes, and balance them in order to work out
how to best get the job done.

>
> Jamie.
>
> --

Re: Separate PK in Jxn Tbl?

On Jan 23, 2:20 pm, JOG wrote:
> On Jan 23, 9:05 am, Jamie Collins wrote:
>
>
>
> > On Jan 23, 1:41 am, "Tony Toews [MVP]" wrote:
>
> > > I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> > > Why? No particular good reason. One of my database rules is that all tables have
> > > an autonumber primary key.
>
> > > The theorists will argue. I don't care.
>
> > Would you care for a pragmatist to offer a counter argument?
>
> > I never seek to add columns where an attribute does not exist in the
> > reality being modelled; sometimes I do end up adding something
> > 'artificial' but only when there is a "good data modelling" reason for
> > doing so.
>
> > In the scenario described, there is a two-column "all key" table which
> > means it is in 5NF. From a "data modelling" point of view I can't see
> > a case for adding an 'artifical key'.
>
> To the OP, I agree with this point of view. Again from a totally
> practical standpoint, it is nonsense adding an artificial key would
> not "make it slightly easier to delete records", because when
> something changes in the real world, you need that data that made up
> the 'original' superkey to know which row to delete anyhow.

That should have read "from a totally practical standpoint, adding an
artificial key would not 'make it slightly easier to delete
records'".

>
> I'd ignore anyone who claims to be telling you based on a purely
> "practical" /or/ purely theoretical standpoints. Its always important
> to be aware of both extremes, and balance them in order to work out
> how to best get the job done.
>
>
>
> > Jamie.
>
> > --

Re: Separate PK in Jxn Tbl?

On Jan 22, 3:26 pm, "Neil" wrote:
> Whenever I've created junction tables in the past, I always made the PK of
> the junction table the combined pks from the two other tables. Ex: Table1
> PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
> together comprise the PK for the junction table.
>
> However, I just came across some code in which the person created a junction
> table with a separate PK consisting of an autonumber field, and then the two
> fields.
>
> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?
>
> Thanks!
>
> Neil

I've done both. I try to use natural keys where applicable, usually
both PK's as the primary for the junction table.

Re: Separate PK in Jxn Tbl?

Jamie Collins wrote:

>> The theorists will argue. I don't care.
>
>Would you care for a pragmatist to offer a counter argument?

You're no pragmatist.

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/

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in message
news:ii6dp3tc3l2u8m4nv6c8ipg8ejgjggu5oe@4ax.com...
> "Neil" wrote:
>
> >However, I just came across some code in which the person created a
junction
> >table with a separate PK consisting of an autonumber field, and then the
two
> >fields.
> >
> >So I was wondering how others did junction tables -- with a standalone
> >autonumber PK, or with a PK consisting of the PKs of the tables being
> >joined? And, if a standalone PK, then why?
>
> I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> Why? No particular good reason. One of my database rules is that all
tables have
> an autonumber primary key. It's also slightly easier to delete the
record in code.
>
> Now if I was to have a child table from the junction table then I would
absolutely
> use a autonumber primary key for ease of use when designing queries, forms
and
> reports.
>
> The theorists will argue. I don't care.
>

Sometimes, theory IS practical. (Some would say always). A pragmatic person
would at least listen to the arguments of theorists before dismissing them.

Re: Separate PK in Jxn Tbl?

David Cressey wrote:

> "Tony Toews [MVP]" wrote in message
> news:ii6dp3tc3l2u8m4nv6c8ipg8ejgjggu5oe@4ax.com...
>
>>"Neil" wrote:
>>
>>
>>>However, I just came across some code in which the person created a
>
> junction
>
>>>table with a separate PK consisting of an autonumber field, and then the
>
> two
>
>>>fields.
>>>
>>>So I was wondering how others did junction tables -- with a standalone
>>>autonumber PK, or with a PK consisting of the PKs of the tables being
>>>joined? And, if a standalone PK, then why?
>>
>>I always use an autonumber PK and a uniqui index set on the two FK fields.
>>
>>Why? No particular good reason. One of my database rules is that all
>
> tables have
>
>>an autonumber primary key. It's also slightly easier to delete the
>
> record in code.
>
>>Now if I was to have a child table from the junction table then I would
>
> absolutely
>
>>use a autonumber primary key for ease of use when designing queries, forms
>
> and
>
>>reports.
>>
>>The theorists will argue. I don't care.
>
> Sometimes, theory IS practical. (Some would say always). A pragmatic person
> would at least listen to the arguments of theorists before dismissing them.

The theorists won't argue. The theorists will simply point out the
inherent stupidity of abdicating thought for simplistic recipes. The
invincibly ignorant won't care. They never do.

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in message
news:ii6dp3tc3l2u8m4nv6c8ipg8ejgjggu5oe@4ax.com...
> "Neil" wrote:
>
>>However, I just came across some code in which the person created a
>>junction
>>table with a separate PK consisting of an autonumber field, and then the
>>two
>>fields.
>>
>>So I was wondering how others did junction tables -- with a standalone
>>autonumber PK, or with a PK consisting of the PKs of the tables being
>>joined? And, if a standalone PK, then why?
>
> I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> Why? No particular good reason. One of my database rules is that all
> tables have
> an autonumber primary key. It's also slightly easier to delete the
> record in code.
>

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. A clear understanding of how and when they
can be used and why is critical or you run the risk of a corrupt database.

> Now if I was to have a child table from the junction table then I would
> absolutely
> use a autonumber primary key for ease of use when designing queries, forms
> and
> reports.
>
> The theorists will argue. I don't care.
>
> 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/

Re: Separate PK in Jxn Tbl?

"Brian Selzer" wrote in
news:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:

> 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. A clear
> understanding of how and when they can be used and why is critical or
> you run the risk of a corrupt database.

We often follow certain practices which we may describe as rules. These
customs may simplify our work, or contribute to its success.
Tony is a very experienced Access developer; readers of Comp Databases Ms-
Access have benefited from his sharing that experience, sometimes in
encapsulated form, as when he describes a usual, customary, or generalized
course of action or behaviour as a rule.

I follow the same rule. Having an auto-number primary key in each table
simplifies the establishment of relationships among tables. In Access, the
primary key auto-number ensures that forms bound to the table will be
editable, although any unique non-null index will serve that purpose. In
scripts using ADO, the primary key auto-number provides an identifier for
update and delete actions.

Could you cite instances of databases typically used with Access that have
been corrupted by auto-number primary keys?

Re: Separate PK in Jxn Tbl?

"David Cressey" wrote

> Sometimes, theory IS practical. (Some would say always).
> A pragmatic person would at least listen to the arguments
> of theorists before dismissing them.

In this case, of course, it is immaterial, because Tony is correct that
Jamie is not a pragmatist, at least not demonstrably so in his posts that I
have observed. Is there a category of "argumentist"?

Larry

Re: Separate PK in Jxn Tbl?

On Jan 24, 2:01 am, lyle fairfield wrote:
> "Brian Selzer" wrote innews:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>
> > 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. A clear
> > understanding of how and when they can be used and why is critical or
> > you run the risk of a corrupt database.
>
> We often follow certain practices which we may describe as rules. These
> customs may simplify our work, or contribute to its success.
> Tony is a very experienced Access developer; readers of Comp Databases Ms-
> Access have benefited from his sharing that experience, sometimes in
> encapsulated form, as when he describes a usual, customary, or generalized
> course of action or behaviour as a rule.

Ah, the joys of cross-posting. Different expectations as to precision,
definitions, decorum, etc... well they're all just going to lead to
immensely fruitful conversations!

>
> I follow the same rule. Having an auto-number primary key in each table
> simplifies the establishment of relationships among tables. In Access, the
> primary key auto-number ensures that forms bound to the table will be
> editable, although any unique non-null index will serve that purpose. In
> scripts using ADO, the primary key auto-number provides an identifier for
> update and delete actions.

And there you have it. There is no way there is going to be a sensible
discussion between RM and MS Access experts. Let the tirade of
misunderstanding and ego begin.

>
> Could you cite instances of databases typically used with Access that have
> been corrupted by auto-number primary keys?

Let me start things off, and see how everyone politely rectifies any
ensuing confusion! Let me see...how about a database with Marriages:
{id, husband, wife, church} - the PK being the autonumbered id - where
a church clerk accidentally enters a marriage twice, because the
schema isn't using the natural {husband, wife} key. Hey, for all I
know duplicates are allowed in Access anyhow...

Re: Separate PK in Jxn Tbl?

"lyle fairfield" wrote in message
news:Xns9A2ED5CCBFC7B6666646261@216.221.81.119...
> I follow the same rule. Having an auto-number primary key in each table
> simplifies the establishment of relationships among tables. In Access, the
> primary key auto-number ensures that forms bound to the table will be
> editable, although any unique non-null index will serve that purpose. In
> scripts using ADO, the primary key auto-number provides an identifier for
> update and delete actions.

I too follow the rule. There are a few more reasons to do this from my POV:

1. One of the main reasons for using a relational db is to avoid data
duplication. Yet some duplication must be present, ie the foreign keys. When
autonumbers are used as PKs, at least all that is duplicated is a simple
number.

2. The name 'id' is instantly recognisable for what it is (a rare thing for
a 2-char name).

3. Re-usable code can be written which may assume the name 'id' for a
table's PK, instead of having to walk the index tree to discover it. Keeps
things simple.

4. When 'id' is used for all PK names, building criteria strings in code can
take up a lot less room.

I'm sure there are more reasons, but that's all I can think of for the
moment.

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

>A clear understanding of how and when they
>can be used and why is critical or you run the risk of a corrupt database.

Umm, not that you care I'm sure but my web pages on Microsoft Access corruptions
http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive resource on the
web. And there have never been any Access corruptions during to autonumber primary
keys that I can recall. And I've likely read just about every posting on that topic
in the last eight or ten years in the comp.databases.ms-access and the
microsoft.public.access.* newsgroups.

However my knowledge is practical not theoretical.

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/

Re: Separate PK in Jxn Tbl?

Bob Badour wrote:

>>>The theorists will argue. I don't care.
>>
>> Sometimes, theory IS practical. (Some would say always). A pragmatic person
>> would at least listen to the arguments of theorists before dismissing them.
>
>The theorists won't argue. The theorists will simply point out the
>inherent stupidity of abdicating thought for simplistic recipes. The
>invincibly ignorant won't care. They never do.

Hey, I thought you had plonked me.

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/

Re: Separate PK in Jxn Tbl?

"lyle fairfield" wrote in message
news:Xns9A2ED5CCBFC7B6666646261@216.221.81.119...
> "Brian Selzer" wrote in
> news:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>
>> 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. A clear
>> understanding of how and when they can be used and why is critical or
>> you run the risk of a corrupt database.
>
> We often follow certain practices which we may describe as rules. These
> customs may simplify our work, or contribute to its success.
> Tony is a very experienced Access developer; readers of Comp Databases Ms-
> Access have benefited from his sharing that experience, sometimes in
> encapsulated form, as when he describes a usual, customary, or generalized
> course of action or behaviour as a rule.
>
> I follow the same rule. Having an auto-number primary key in each table
> simplifies the establishment of relationships among tables. In Access, the
> primary key auto-number ensures that forms bound to the table will be
> editable, although any unique non-null index will serve that purpose. In
> scripts using ADO, the primary key auto-number provides an identifier for
> update and delete actions.
>
> Could you cite instances of databases typically used with Access that have
> been corrupted by auto-number primary keys?

It is not the auto-number primary key that causes corruption: it is its
misuse. Many will fail to add the unique constraints on the other candidate
keys. This is how you can end up with more than one employee with the same
social security number.

Re: Separate PK in Jxn Tbl?

JOG wrote:

>Let me see...how about a database with Marriages:
>{id, husband, wife, church} - the PK being the autonumbered id - where
>a church clerk accidentally enters a marriage twice, because the
>schema isn't using the natural {husband, wife} key.

And of course two different males with the same name, John Smith have never, ever
married two females with the same name, Jane Doe.

My brother has actually met his "twin" Same first, middle and last name. And same
birth date. Causes no end of trouble as the other guy has been a guest of Her
Majesty involuntarily a number of times. (That is the other guy has been in
prison.)

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/

Re: Separate PK in Jxn Tbl?

"Stuart McCall" wrote:

>2. The name 'id' is instantly recognisable for what it is (a rare thing for
>a 2-char name).

Although I don't use exactly that as my primary key name. I use the initials of the
table in front if the ID.

Tony's Object Naming Conventions
http://www.granite.ab.ca/access/tonysobjectnamingconventions .htm

>4. When 'id' is used for all PK names, building criteria strings in code can
>take up a lot less room.

However duplicate field names can be more troublesome in queries and code as you must
disambiguate them.

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/

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in message
news:6l5gp3hle4cn2lin154h4ip0288b0pgl0v@4ax.com...
> "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!

>>A clear understanding of how and when they
>>can be used and why is critical or you run the risk of a corrupt database.
>
> Umm, not that you care I'm sure but my web pages on Microsoft Access
> corruptions
> http://www.granite.ab.ca/access/corruptmdbs.htm are the definitive
> resource on the
> web. And there have never been any Access corruptions during to
> autonumber primary
> keys that I can recall. And I've likely read just about every posting on
> that topic
> in the last eight or ten years in the comp.databases.ms-access and the
> microsoft.public.access.* newsgroups.
>

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.

> However my knowledge is practical not theoretical.
>

I gained most of my knowledge the hard way as well, but that doesn't mean
that one shouldn't seek to understand and apply the theory.

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

Re: Separate PK in Jxn Tbl?

On Jan 24, 7:03=A0am, "Brian Selzer" wrote:
> "Tony Toews [MVP]" wrote in messagenews:6l5gp3hle=
4cn2lin154h4ip0288b0pgl0v@4ax.com...
>
> > "Brian Selzer" wrote:
>
> >>Only an idiot would have a rule for no particularly good reason. =A0Only=
an
> >>imbecile would follow such a rule. =A0A strong argument can be made for
> >>using
> >>autonumber primary keys--especially if the target DBMS doesn't support F=
OR
> >>EACH ROW triggers--but to just blythely add them for no particularly goo=
d
> >>reason is a recipe for disaster.
>
> > My reasons are, in my opinion, good reasons. =A0Not great but good. =A0Y=
ou
> > don't like
> > them? =A0Tough.
>
> So now they're good reasons? =A0In your earlier post, you said they weren'=
t
> good reasons. =A0Can't you make up your mind? =A0You also haven't stated y=
our
> reasons. =A0How can I like them or not like them? =A0I don't know them!

No, it looks like Tony's reasons are secret, and may only be gleaned
from a romantic evening of fine wine and barry white.

>
> >>A clear understanding of how and when they
> >>can be used and why is critical or you run the risk of a corrupt databas=
e.
>
> > Umm, not that you care I'm sure but my web pages on Microsoft Access
> > corruptions
> >http://www.granite.ab.ca/access/corruptmdbs.htmare the definitive
> > resource on the
> > web. =A0 And there have never been any Access corruptions during to
> > autonumber primary
> > keys that I can recall. =A0And I've likely read just about every posting=
on
> > that topic
> > in the last eight or ten years in the comp.databases.ms-access and the
> > microsoft.public.access.* newsgroups.
>
> 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.

An actual example I experience springs to mind - I have witnessed a
database where student projects were recorded via a schema of Project
Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id).
None of the partnerships were aware of any "id" in the real world, and
simply submitted their partnership choices on paper to admin. A
clerical error resulted in 2/3 of the data being entered twice, which
left a lot of people flapping about the number of markers required
until the error was found. If the schema had used the natural {RoleA,
RoleB} key there would have been no issue.

But then for all I know, MS Access might allow duplicates anyhow....



>
> > However my knowledge is practical not theoretical.
>
> I gained most of my knowledge the hard way as well, but that doesn't mean
> that one shouldn't seek to understand and apply the theory.
>
>
>
> > Tony
> > --
> > Tony Toews, Microsoft Access MVP
> > =A0 Please respond only in the newsgroups so that others can
> > read the entire thread of messages.
> > =A0 Microsoft Access Links, Hints, Tips & Accounting Systems at
> >http://www.granite.ab.ca/accsmstr.htm
> > =A0 Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/- Hide =
quoted text -
>
> - Show quoted text -

Re: Separate PK in Jxn Tbl?

On Jan 23, 10:29 pm, JOG wrote:
> On Jan 24, 2:01 am, lyle fairfield wrote:
>
>
>
> > "Brian Selzer" wrote innews:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>
> > > 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. A clear
> > > understanding of how and when they can be used and why is critical or
> > > you run the risk of a corrupt database.
>
> > We often follow certain practices which we may describe as rules. These
> > customs may simplify our work, or contribute to its success.
> > Tony is a very experienced Access developer; readers of Comp Databases Ms-
> > Access have benefited from his sharing that experience, sometimes in
> > encapsulated form, as when he describes a usual, customary, or generalized
> > course of action or behaviour as a rule.
>
> Ah, the joys of cross-posting. Different expectations as to precision,
> definitions, decorum, etc... well they're all just going to lead to
> immensely fruitful conversations!
>
>
>
> > I follow the same rule. Having an auto-number primary key in each table
> > simplifies the establishment of relationships among tables. In Access, the
> > primary key auto-number ensures that forms bound to the table will be
> > editable, although any unique non-null index will serve that purpose. In
> > scripts using ADO, the primary key auto-number provides an identifier for
> > update and delete actions.
>
> And there you have it. There is no way there is going to be a sensible
> discussion between RM and MS Access experts. Let the tirade of
> misunderstanding and ego begin.

I thought we were being peaceful here in CDMA, despite the unnecessary
references to idiot and imbecile.

> > Could you cite instances of databases typically used with Access that have
> > been corrupted by auto-number primary keys?
>
> Let me start things off, and see how everyone politely rectifies any
> ensuing confusion! Let me see...how about a database with Marriages:
> {id, husband, wife, church} - the PK being the autonumbered id - where
> a church clerk accidentally enters a marriage twice, because the
> schema isn't using the natural {husband, wife} key. Hey, for all I
> know duplicates are allowed in Access anyhow...

Many indexes are allowed in JET/ACE and many other db engines to which
Access might connect.
In addition to the PK index on the ID field, I would create a unique
non-null index on husband and wife, if I wanted to restrict couples
from marrying each other more than once.

Re: Separate PK in Jxn Tbl?

On Jan 24, 2:01 am, lyle fairfield wrote:
> Could you cite instances of databases typically used with Access that have
> been corrupted by auto-number primary keys?

There was a notorious bug in Access2000 where an auto-number values
would get duplicated:

ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
and Repair a Database
http://support.microsoft.com/kb/257408

There was anecdotal evidence that this would happen even when a PK has
been used on the column Presumably the PK would get dropped in the
process; I can't quite believe that a Jet PK could ever contain
duplicates. That said, I was surprised to discover that NULL values
can exists in a Jet NOT NULL column e.g.

Sub NullInANotNullColumn()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String
Sql = _
"CREATE TABLE Test (" & vbCr & _
"ID INTEGER);"
..Execute Sql
Sql = _
"INSERT INTO Test (ID) VALUES (NULL);"
..Execute Sql
Sql = _
"SELECT ID FROM Test;"
Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString(, , , , "<>")
rs.Close
Sql = _
"ALTER TABLE Test ALTER" & _
" ID INTEGER NOT NULL;"
..Execute Sql
Set rs = .OpenSchema(4, _
Array(Empty, Empty, "Test", "ID"))
MsgBox rs.Fields("IS_NULLABLE").Value
Sql = _
"SELECT ID FROM Test;"
Set rs = .Execute(Sql)

' If Test.ID IS_NULLABLE = false, why
' does it still contain a NULL ?!
MsgBox rs.GetString(, , , , "<>")
Sql = _
"INSERT INTO Test (ID) VALUES (NULL);"
..Execute Sql
End With
Set .ActiveConnection = Nothing
End With
End Sub

> In Access, the
> primary key auto-number ensures that forms bound to the table will be
> editable, although any unique non-null index will serve that purpose. In
> scripts using ADO, the primary key auto-number provides an identifier for
> update and delete actions.

A primary key auto-number is not a prerequisite of update and delete
actions via ADO. In fact, updating a Jet table with no unique
constraint/index and no PK using an ADO recordset still works with a
cursor side location provided the _data being updated_ can be uniquely
identified in the table using the compound of its attributes; with a
server-side cursor (which is more usual for ADO in Access) the data
does not need to unique _at all_.

Here's some VBA code to demo this behaviour (to test with a server
side cursor, comment out the line .CursorLocation = 3):

Sub ADONaturalKey()
Kill Environ$("temp") & "\DropMe.mdb"
Dim cat
Set cat = CreateObject("ADOX.Catalog")
With cat
..Create _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & _
Environ$("temp") & "\DropMe.mdb"
With .ActiveConnection
Dim Sql As String

' Note this table has no constraints
' (no UNIQUE, no PK) and no indexes
Sql = _
"CREATE TABLE Test (ID INTEGER," & _
" data_col INTEGER NOT NULL)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (1, 1)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (2, 2)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (3, 3)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (NULL, 4)"
..Execute Sql
Sql = _
"INSERT INTO Test (ID, data_col)" & _
" VALUES (NULL, 5)"
..Execute Sql
Sql = _
"SELECT ID, data_col " & vbCr & "FROM Test"

Dim rs
Set rs = .Execute(Sql)
MsgBox rs.GetString(, , , , "<>")
rs.Close
End With
End With
With rs
..CursorLocation = 3
..CursorType = 2
..LockType = 3
..Open

' Update some data
..MoveLast
..Update Array("ID", "data_col"), Array(1, 1)
..MovePrevious
..Update "data_col", 55
..MoveFirst

' Note that update was successful and
' that the rows are no longer unique
..MoveFirst
MsgBox rs.GetString(, , , , "<>")

' Update an attribute on a
' non-duplicated row
..MoveFirst
..MoveNext
..Update "data_col", 99

' Note that update was successful and
' even though the rows are no longer unique
..MoveFirst
MsgBox rs.GetString(, , , , "<>")

' Update an attribute on a
' duplicated row
..MoveLast
On Error Resume Next
..Update "data_col", 666
MsgBox Err.Description
End With
End Sub

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 24, 1:27 am, "Brian Selzer" wrote:
> "lyle fairfield" wrote in message
>
> news:Xns9A2ED5CCBFC7B6666646261@216.221.81.119...
>
>
>
> > "Brian Selzer" wrote in
> >news:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>
> >> 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. A clear
> >> understanding of how and when they can be used and why is critical or
> >> you run the risk of a corrupt database.
>
> > We often follow certain practices which we may describe as rules. These
> > customs may simplify our work, or contribute to its success.
> > Tony is a very experienced Access developer; readers of Comp Databases Ms-
> > Access have benefited from his sharing that experience, sometimes in
> > encapsulated form, as when he describes a usual, customary, or generalized
> > course of action or behaviour as a rule.
>
> > I follow the same rule. Having an auto-number primary key in each table
> > simplifies the establishment of relationships among tables. In Access, the
> > primary key auto-number ensures that forms bound to the table will be
> > editable, although any unique non-null index will serve that purpose. In
> > scripts using ADO, the primary key auto-number provides an identifier for
> > update and delete actions.
>
> > Could you cite instances of databases typically used with Access that have
> > been corrupted by auto-number primary keys?
>
> It is not the auto-number primary key that causes corruption: it is its
> misuse. Many will fail to add the unique constraints on the other candidate
> keys. This is how you can end up with more than one employee with the same
> social security number.

The defense rests.

Re: Separate PK in Jxn Tbl?

On Jan 24, 10:24 am, JOG wrote:
> An actual example I experience springs to mind - I have witnessed a
> database where student projects were recorded via a schema of Project
> Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id).
> None of the partnerships were aware of any "id" in the real world, and
> simply submitted their partnership choices on paper to admin. A
> clerical error resulted in 2/3 of the data being entered twice, which
> left a lot of people flapping about the number of markers required
> until the error was found. If the schema had used the natural {RoleA,
> RoleB} key there would have been no issue.

My experience, also. That's not to say that everyone who uses
autonumber PKs neglects to put constraints on alternate/candidate keys
but I do encounter autonumber problems frequently i.e. tables where
the only unique constraint/index is a PK on the autonumber column,
which actually *facilitates* the inclusion of duplicates by giving the
developer a false sense of security: "I've added a PK, I've done my
bit." Being able to uniquely identify duplicates is of no comfort to
me.

Who do we have to blame for this state of affairs? The Access
interface, IMO. If you omit to define the PK when creating a table in
Design View, upon saving you get prompted to add a primary key (good)
but instead of providing a way to choose existing columns, the offer
is to add an autonumber. If you haven't got a key to begin with,
autonumber cannot provide you with one unless you expose it to end
users which everyone says you shouldn't do (everyone except Tony
Toews, naturally ). The message even asserts that you need a PK to
be able to create Relationships that reference the table which isn't
true of Access Relationships (which are so vague they can even be
based on columns of different respective data types) nor Jet foreign
keys (which can be based on a unique constraint/index). BTW in Access
2007 you now just get given an autonumber PK by default, which you
would then have to go to the trouble to remove.

> it looks like Tony's reasons are secret, and may only be gleaned
> from a romantic evening of fine wine and barry white.

Hmm, I once tried this with Tony [TIC]: I spent hours cooking him his
favourite dish, getting the seasoning just right. And what does he do
when we've sat down? He picks up the salt and, just as he was about to
apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't
you going to test it for seasoning first? I think you'll find it is
just fine." He replied, "I always add salt to my food. Why? No
particular good reason. One of my rules is that all food needs extra
salt." I was about to protest when he added, "You don't like my rules?
Tough. I don't care." Well, you can imagine how hurt I was, reader.
Not as much as Tony after I'd thrown him off the balcony, though.

Jamie.

--

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in
news:h1cgp3h9sla9thq3niidn6sa4osjdj7sng@4ax.com:

> JOG wrote:
>
>>Let me see...how about a database with Marriages:
>>{id, husband, wife, church} - the PK being the autonumbered id -
>>where a church clerk accidentally enters a marriage twice, because
>>the schema isn't using the natural {husband, wife} key.
>
> And of course two different males with the same name, John Smith
> have never, ever married two females with the same name, Jane Doe.
>
The marriages table should use a composite natural key, made up from
the surrogate (autonumber) keys from the persons table, and possibly
a marriage date. Some people marry the same spouse twice.

But surrogate keys should only be used after analysis reveals that
no true natural key exists.

> My brother has actually met his "twin" Same first, middle and
> last name. And same birth date. Causes no end of trouble as the
> other guy has been a guest of Her Majesty involuntarily a number
> of times. (That is the other guy has been in prison.)
>
> Tony



--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Re: Separate PK in Jxn Tbl?

On Jan 24, 2:14 am, "Larry Linson" wrote:
> > A pragmatic person would at least listen to the arguments
> > of theorists before dismissing them.
>
> In this case, of course, it is immaterial, because Tony is correct that
> Jamie is not a pragmatist, at least not demonstrably so in his posts that I
> have observed.

Care to back up your claim with some evidence?

No, you are mistaken. I *frequently* (to the point of boring people,
I'm sure) back up my assertions with Jet SQL code, VBA demos, links to
Microsoft articles and the Access Help, and stories from the field.
This thread is a good example of that.

> Is there a category of "argumentist"?

I think the word you are looking for is "critic".

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 24, 11:09 am, Jamie Collins wrote:
> > Tony's reasons are secret, and may only be gleaned
> > from a romantic evening of fine wine and barry white.
>
> Hmm, I once tried this with Tony [TIC]

I also tried it with Bob Badour [TIC] but he merely dismissed my
simplistic recipes and wanted more plonk.

Jamie.

--

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in message
news:2ccgp3t7j2ap0kec53msevv5htmts1olsm@4ax.com...
> "Stuart McCall" wrote:
>
>>2. The name 'id' is instantly recognisable for what it is (a rare thing
>>for
>>a 2-char name).
>
> Although I don't use exactly that as my primary key name. I use the
> initials of the
> table in front if the ID.
>
> Tony's Object Naming Conventions
> http://www.granite.ab.ca/access/tonysobjectnamingconventions .htm
>
>>4. When 'id' is used for all PK names, building criteria strings in code
>>can
>>take up a lot less room.
>
> However duplicate field names can be more troublesome in queries and code
> as you must
> disambiguate them.

Disambiguating is exactly what you're doing by prefixing id with your
table's initials. If you use short (read abbreviated) table names as I tend
to, then it's a non-issue. And you only need to disambiguate when there's a
potential clash. Apples and oranges AFAIC.

Re: Separate PK in Jxn Tbl?

"Brian Selzer" wrote in message
news:sdWlj.5222$Rg1.1730@nlpi068.nbdc.sbc.com...
> It is not the auto-number primary key that causes corruption: it is its
> misuse. Many will fail to add the unique constraints on the other candidate
> keys. This is how you can end up with more than one employee with the same
> social security number.

This statement can be made generic...

[SomeNoun] is a [Bad/Good] idea because stupid people [Will/WillNot] do
[SomeVerb].

Perhaps the *actual* problem is stupid people or more generously "people who do
stupid things".

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Separate PK in Jxn Tbl?

On Jan 24, 6:09 am, Jamie Collins wrote:

> He picks up the salt and, just as he was about to
> apply it to the food I'd lovingly prepared, I cried out, "Tony, aren't
> you going to test it for seasoning first? I think you'll find it is
> just fine." He replied, "I always add salt to my food. Why? No
> particular good reason. One of my rules is that all food needs extra
> salt."

Thank you. I've learned that Tony and I share two rules.

Re: Separate PK in Jxn Tbl?

On Jan 24, 11:18=A0am, Jamie Collins wrote:
> On Jan 24, 2:14 am, "Larry Linson" wrote:
>
> > =A0> A pragmatic person would at least listen to the arguments
> > =A0> of theorists before dismissing them.
>
> > In this case, of course, =A0it is immaterial, because Tony is correct th=
at
> > Jamie is not a pragmatist, at least not demonstrably so in his posts tha=
t I
> > have observed.
>
> Care to back up your claim with some evidence?
>
> No, you are mistaken. I *frequently* (to the point of boring people,
> I'm sure) back up my assertions with Jet SQL code, VBA demos, links to
> Microsoft articles and the Access Help, and stories from the field.
> This thread is a good example of that.
>
> > Is there a category of "argumentist"?
>
> I think the word you are looking for is "critic".

I imagine he meant "antagonist" to be honest. But then, only seeing
this thread from c.d.t and not knowing the ms-access glitterati, I
have no idea as to whether he would be correct in such an assertion.

I just think you're all damn brave for using Access in the first
place.

>
> Jamie.
>
> --

Re: Separate PK in Jxn Tbl?

On Jan 24, 1:26 pm, lyle wrote:
> Thank you. I've learned that Tony and I share two rules.

It was an analogy and meant to be taken with a pinch of salt ;-)

Jamie.

--

Re: Separate PK in Jxn Tbl?

"JOG" wrote in message
news:2b0cf2d3-8b8e-4dc5-9255-3db75df415c4@d4g2000prg.googleg roups.com...
On Jan 24, 7:03 am, "Brian Selzer" wrote:
(quote)
An actual example I experience springs to mind - I have witnessed a
database where student projects were recorded via a schema of Project
Partners:{id:autonumber, RoleA:person, RoleB:person}, with PK(id).
None of the partnerships were aware of any "id" in the real world, and
simply submitted their partnership choices on paper to admin. A
clerical error resulted in 2/3 of the data being entered twice, which
left a lot of people flapping about the number of markers required
until the error was found. If the schema had used the natural {RoleA,
RoleB} key there would have been no issue.

But then for all I know, MS Access might allow duplicates anyhow....
(/quote)

I have begun to mess around with MS Access, as a retirement hobby, after
working professionally with some SQL DBMS products.

AFAIK, MS Access enforces the no duplicates rule, and the no missing data
rule, for every PK that's declared. There are other ways to express these
constraints, but the easiest way to get them is to declare a PK.

For that reason, I prefer not to create a new ID with an autonumber for any
junction table. Tony have have his reasons for going the other way. Until
I know what they are, I remain unpersuaded.

The fact tables in a star schema have the same property as a junction table,
except that the number of FKs that are components of the PK may be larger
than 2, and generally is larger.


I've just started messing with star schemas in MS Access. It's too soon for
me to offer even a guess as to whether this is a smart idea or a stupid
idea. All I know is that it will provide some cheap amusement for my
retirement.

Re: Separate PK in Jxn Tbl?

On Jan 24, 6:43 pm, "David Cressey" wrote:
> I've just started messing with star schemas in MS Access. It's too soon for
> me to offer even a guess as to whether this is a smart idea or a stupid
> idea. All I know is that it will provide some cheap amusement for my
> retirement.

Please post you findings here ;)

Jamie.

--

Re: Separate PK in Jxn Tbl?

JOG wrote:

>I just think you're all damn brave for using Access in the first
>place.

Why? It works and works well. If you have too many users or remote users bolt on a
SQL Server backend. Now you can have thousands of users.

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/

Re: Separate PK in Jxn Tbl?

Neil wrote:
> Whenever I've created junction tables in the past, I always made the PK of
> the junction table the combined pks from the two other tables. Ex: Table1
> PK=A; Table2 PK=B; Junction table consists of two fields, A and B, which
> together comprise the PK for the junction table.
>
> However, I just came across some code in which the person created a junction
> table with a separate PK consisting of an autonumber field, and then the two
> fields.
>
> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?
>
> Thanks!
>
> Neil

Whenever I have multiple key fields, natural or not, I create an
AutoNumber PK for pragmatic reasons. The main reason is that it makes
it easier to create the joins. The theorists are champions at joining
tables and don't have to be concerned with the complexity of the SQL
they write. If I convert an Access table over to SQLServer I add even
another field as a primary key, usually prefixed with SS (Gasp!).

I keep Jamie's advice in the back of my mind, about how enforcing
constraints at the table level is better than enforcing them through
code, but where I work, no one is going to access the table data using
anything other than Access so I am able to take the high road at my
leisure. Maybe my coding practice just needs to catch up with my
philosophy. Theoretically, the idea of using natural keys is more
intellectually satisfying, but for now the lure of simpler joins is
winning out. Lately, I've increased the amount of normalization in one
of my databases and the joins got even more complicated, adding about a
line or so in the SQL view in Access for every new query using those
tables. Queries involving many to many relationships often add
additional tables later and highlight the need to keep joins as simple
as possible.

James A. Fortune
MPAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

James A. Fortune wrote:

> Neil wrote:
>
>> Whenever I've created junction tables in the past, I always made the
>> PK of the junction table the combined pks from the two other tables.
>> Ex: Table1 PK=A; Table2 PK=B; Junction table consists of two fields, A
>> and B, which together comprise the PK for the junction table.
>>
>> However, I just came across some code in which the person created a
>> junction table with a separate PK consisting of an autonumber field,
>> and then the two fields.
>>
>> So I was wondering how others did junction tables -- with a standalone
>> autonumber PK, or with a PK consisting of the PKs of the tables being
>> joined? And, if a standalone PK, then why?
>>
>> Thanks!
>>
>> Neil
>
> Whenever I have multiple key fields, natural or not, I create an
> AutoNumber PK for pragmatic reasons. The main reason is that it makes
> it easier to create the joins. The theorists are champions at joining
> tables and don't have to be concerned with the complexity of the SQL
> they write.

Anyone who writes SQL is a theorist regardless whether the person is
smart enough and educated enough to realise it.


If I convert an Access table over to SQLServer I add even
> another field as a primary key, usually prefixed with SS (Gasp!).

There is no stopping the invincibly ignorant. -- DT


> I keep Jamie's advice in the back of my mind, about how enforcing
> constraints at the table level is better than enforcing them through
> code, but where I work, no one is going to access the table data using
> anything other than Access so I am able to take the high road at my
> leisure.

Uh huh. ::rolls eyes::


Maybe my coding practice just needs to catch up with my
> philosophy. Theoretically, the idea of using natural keys is more
> intellectually satisfying, but for now the lure of simpler joins is
> winning out.

You sound like one of these programmers who writes shitty, buggy code to
avoid a few keystrokes.


Lately, I've increased the amount of normalization in one
> of my databases and the joins got even more complicated,

Define "amount of normalization". Do you even have a clue what it is?


adding about a
> line or so in the SQL view in Access for every new query using those
> tables. Queries involving many to many relationships often add
> additional tables later and highlight the need to keep joins as simple
> as possible.

An intelligent, informed person would always follow the design principle
of "As simple as possible and no simpler." I don't see what that has to
do with joining tables (or relations for that matter) other than perhaps
the ability to even recognize complexity when one sees it.

Following the above design principle, one would not needlessly increase
complexity with additional arbitrary columns and keys.

Re: Separate PK in Jxn Tbl?

JOG wrote in
news:2b0cf2d3-8b8e-4dc5-9255-3db75df415c4@d4g2000prg.googleg roups.com
:

> But then for all I know, MS Access might allow duplicates
> anyhow....

Any database engine can have the schema defined in a way that will
allow duplicates. This is not an issue of the db engine in use, but
of the skill of the operator in association with the requirements of
the application the database is used with.

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

Re: Separate PK in Jxn Tbl?

JOG wrote in
news:ffbc6130-a98c-4177-81dd-43976fdfd1ac@t1g2000pra.googleg roups.com
:

> I just think you're all damn brave for using Access in the first
> place.

That just goes to show you haven't got a clue about what MS Access
actually is.

Typical.

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

Re: Separate PK in Jxn Tbl?

This boil up is a variation of the "Autonumber vs. Natural Key"
religious wars that sweep the Access groups on even numbered(?) years.
In the meantime OP is probably trying to hide the matches with which
he started the fires....

Until this thread, I thought that I might be the only person in the
world who reflexively entered an Autonumber PK into *every* table I
design. My reasons for using Autonumber PKs is practical and based on
experience.

As for using them in junction tables, I once stopped and thought about
it. I couldn't see a risk in either using them or not using them.
Since Autonumber PKs had saved me countless hours of re-work in normal
tables, my bias was and is to use them. I don't revisit that decision
every time I create a junction table, I just do it. Apparently, Tony
Toews went through similar reasoning. I didn't ask anyone's advice or
permission. It is not necessary that anyone else be persuaded to use
any method I adopt.

It has never caused me the least problem. There has been no
revelation in this thread that would cause me to even revisit the
decision.

In the one reported instance of a "problem", the Autonumber PK along
with the two FKs wasn't the issue. That configuration simply spewed
different erroneous data from the erroneous data that would be spewed
by sticking with natural keys alone. As reported, the problem in that
case was erroneous data entry not a flawed schema.

My advice to OP and to everyone else is to use which ever mode seems
best to him or her.

HTH
--
-Larry-
--

"Phil Stanton" wrote in message
news:13pclc9bkkveq4e@corp.supernews.com...
> I always use just the 2 primary keys but....
> If I were a library lending 2 undiffentiatable copies of 1 book to
the same
> person, I suppose I would need an Autonumber PK plus A BookID and
> BorrowerID.
>
> Phil
>
> "Neil" wrote in message
> news:6kslj.128$J41.113@newssvr14.news.prodigy.net...
> > Whenever I've created junction tables in the past, I always made
the PK of
> > the junction table the combined pks from the two other tables. Ex:
Table1
> > PK=A; Table2 PK=B; Junction table consists of two fields, A and B,
which
> > together comprise the PK for the junction table.
> >
> > However, I just came across some code in which the person created
a
> > junction table with a separate PK consisting of an autonumber
field, and
> > then the two fields.
> >
> > So I was wondering how others did junction tables -- with a
standalone
> > autonumber PK, or with a PK consisting of the PKs of the tables
being
> > joined? And, if a standalone PK, then why?
> >
> > Thanks!
> >
> > Neil
> >
>
>

Re: Separate PK in Jxn Tbl?

Larry Daugherty wrote:
> This boil up is a variation of the "Autonumber vs. Natural Key"
> religious wars that sweep the Access groups on even numbered(?) years.
> In the meantime OP is probably trying to hide the matches with which
> he started the fires....
>
> Until this thread, I thought that I might be the only person in the
> world who reflexively entered an Autonumber PK into *every* table I
> design. My reasons for using Autonumber PKs is practical and based on
> experience.

Bullshit! Ignorance, stupidity and laziness are not practical.

[snip]

Re: Separate PK in Jxn Tbl?

"Larry Daugherty" wrote:

>This boil up is a variation of the "Autonumber vs. Natural Key"
>religious wars that sweep the Access groups on even numbered(?) years.
>In the meantime OP is probably trying to hide the matches with which
>he started the fires....

Yup, it's amazing how this all happens. To me the particularly amusing
part is that we're generally quite civil in our discussions that stay in the Access
groups.

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

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/

Re: Separate PK in Jxn Tbl?

Jamie Collins wrote:

>> Could you cite instances of databases typically used with Access that have
>> been corrupted by auto-number primary keys?
>
>There was a notorious bug in Access2000 where an auto-number values
>would get duplicated:
>
>ACC2000: AutoNumber Field Duplicates Previous Values After You Compact
>and Repair a Database
>http://support.microsoft.com/kb/257408

A bug in the first versions of Jet 4.0. Long since fixed.

And that didn't actually corrupt the database. It was unusable for inserting records
but not corrupted. Although that's splitting hairs.

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/

Re: Separate PK in Jxn Tbl?

On Jan 24, 8:53 pm, "Tony Toews [MVP]" wrote:
> JOG wrote:
> >I just think you're all damn brave for using Access in the first
> >place.
>
> Why?

I was tempted to simply reply:

"avoiding using Access is just a rule I have. Why? No good reason. The
access fanboys will argue. I don't care"

....but, hey that sort of sentiment would surely just be peurile, and
discourage useful discussion would it not?

> It works and works well. If you have too many users or remote users bolt on a
> SQL Server backend. Now you can have thousands of users.

Or I could just use a decent database architecture in the first place
(and I am lucky enough to be able to), that can cope with more that 10
users (I mean 255 *cough*). Thats why I salute your dedication in the
face of all that superior db technology. I mean, we all know that the
client really wants to use oracle, and yet its you that stoutly has to
deal with his corner cutting as best you can.

I certainly don't mean to upset you....Well okay, maybe there's just a
bit of friendly ribbing in there ;)

>
> 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 athttp://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Re: Separate PK in Jxn Tbl?

Every dog gets one bite. Then they go into the bs/Kill file.

Did you note the net contribution to the subject at hand? Their
commentary serves only to identify and characterize *themselves*

My hope is to make meaningful contributions to those who are actually
developing applications or learning to do so.

Keep up the good work!
--
-Larry-
--

"Tony Toews [MVP]" wrote in message
news:fmdip39pv9vtpetukd82s7vd1l9gi5jaev@4ax.com...
> "Larry Daugherty" wrote:
>
> >This boil up is a variation of the "Autonumber vs. Natural Key"
> >religious wars that sweep the Access groups on even numbered(?)
years.
> >In the meantime OP is probably trying to hide the matches with
which
> >he started the fires....
>
> Yup, it's amazing how this all happens. To me the
particularly amusing
> part is that we're generally quite civil in our discussions that
stay in the Access
> groups.
>
> I'm saddened, although not surprised, at the comments from a few
regulars from the
> c.d.theory newsgroup using words such as idiot, imbecile and
invincibly ignorant.
>
> 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/

Re: Separate PK in Jxn Tbl?

Bob Badour wrote in news:4799368c$0$4065
$9a566e8b@news.aliant.net:

> Bullshit! Ignorance, stupidity and laziness are not practical.

If you’re feeling angry because you can’t program or design databases well,
perhaps you could describe your problems here, instead of posting streams
of invective. I’m sure that Tony, James or Larry will be able to simplify
the concepts required and suggest solutions suitable even for someone who
seems so backward and confused.

Hope this helps!

Re: Separate PK in Jxn Tbl?

Larry Daugherty wrote:

> Every dog gets one bite. Then they go into the bs/Kill file.
>
> Did you note the net contribution to the subject at hand? Their
> commentary serves only to identify and characterize *themselves*
>
> My hope is to make meaningful contributions to those who are actually
> developing applications or learning to do so.

Your hope is futile in the face of your ignorance and laziness.


> Keep up the good work!

I'll try.


"Tony Toews [MVP]" wrote in message
news:fmdip39pv9vtpetukd82s7vd1l9gi5jaev@4ax.com...

>> "Larry Daugherty" wrote:
>>
>
>>> >This boil up is a variation of the "Autonumber vs. Natural Key"
>>> >religious wars that sweep the Access groups on even numbered(?) years.
>>> >In the meantime OP is probably trying to hide the matches with which
>>> >he started the fires....
>
>>
>> Yup, it's amazing how this all happens. To me the
particularly amusing
>> part is that we're generally quite civil in our discussions that
stay in the Access
>> groups.

Ignorance is bliss.


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

Precious few pleasant ways exist to express unpleasant truths. However,
intellectual honesty requires the expression of truths regardless of
appeal to oneself or to others.

Re: Separate PK in Jxn Tbl?

JOG wrote:

>> Why?
>
>I was tempted to simply reply:
>
>"avoiding using Access is just a rule I have. Why? No good reason. The
>access fanboys will argue. I don't care"
>
>...but, hey that sort of sentiment would surely just be peurile, and
>discourage useful discussion would it not?



>> It works and works well. If you have too many users or remote users bolt on a
>> SQL Server backend. Now you can have thousands of users.
>
>Or I could just use a decent database architecture in the first place
>(and I am lucky enough to be able to), that can cope with more that 10
>users (I mean 255 *cough*). Thats why I salute your dedication in the
>face of all that superior db technology. I mean, we all know that the
>client really wants to use oracle, and yet its you that stoutly has to
>deal with his corner cutting as best you can.

But in many situations why go to the extra effort of Oracle or SQL Server
required? I seldom create systems that will be used by more than ten people. Using
Access in such an environment is a simpler install and simple updating. No DBA
required and very little IT admin.

>I certainly don't mean to upset you....Well okay, maybe there's just a
>bit of friendly ribbing in there ;)



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/

Re: Separate PK in Jxn Tbl?

JOG wrote:

>I was tempted to simply reply:
>
>"avoiding using Access is just a rule I have. Why? No good reason. The
>access fanboys will argue. I don't care"
>
>...but, hey that sort of sentiment would surely just be peurile, and
>discourage useful discussion would it not?

BTW I didn't realize that there were people in the theory newsgroup who actually
wanted to discuss the issues in a reasonable fashion.

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/

Re: Separate PK in Jxn Tbl?

Bob Badour wrote:

> >> Yup, it's amazing how this all happens. To me the
>particularly amusing
> >> part is that we're generally quite civil in our discussions that
>stay in the Access
> >> groups.
>
>Ignorance is bliss.

Politeness goes a long way.

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/

Re: Separate PK in Jxn Tbl?

lyle fairfield wrote:

> Bob Badour wrote in news:4799368c$0$4065
> $9a566e8b@news.aliant.net:
>=20
>>Bullshit! Ignorance, stupidity and laziness are not practical.
>=20
> If youï¿=BDre feeling angry because you canï¿=BDt program or de=
sign databases well,=20

Your question suffers the fallacy of many questions. First, I don't feel =

angry at all. Second, I can do both very well. Are you feeling angry=20
because you can't program or design databases well?


> perhaps you could describe your problems here, instead of posting strea=
ms=20
> of invective. Iï¿=BDm sure that Tony, James or Larry will be able t=
o simplify=20
> the concepts required and suggest solutions suitable even for someone w=
ho=20
> seems so backward and confused.

Since they are simpletons, I have no doubt they would simplify the=20
concepts ... beyond all recognition.


> Hope this helps! =20

Re: Separate PK in Jxn Tbl?

Tony Toews [MVP] wrote:
> "Neil" wrote:
>
> >However, I just came across some code in which the person created a junction
> >table with a separate PK consisting of an autonumber field, and then the two
> >fields.
> >
> >So I was wondering how others did junction tables -- with a standalone
> >autonumber PK, or with a PK consisting of the PKs of the tables being
> >joined? And, if a standalone PK, then why?
>
> I always use an autonumber PK and a uniqui index set on the two FK fields.
>
> Why? No particular good reason. One of my database rules is that all tables have
> an autonumber primary key. It's also slightly easier to delete the record in code.
>
> Now if I was to have a child table from the junction table then I would absolutely
> use a autonumber primary key for ease of use when designing queries, forms and
> reports.
>
> The theorists will argue. I don't care.
>
> Tony

Why introduce an autonumber as a PK when you don't need to? This
design is broken as noted in the discussion on library book example.
Why make the application code work harder than it has to? Let the DBMS
do the work that it is good at.

Note I'm no theorist. I'm a stuck in the trenches Software Engineer.
There are times to use an autonumber PK. But in this case I would say
you are fooling yourself in thinking this is good design practice.

Ed

Re: Separate PK in Jxn Tbl?

lyle fairfield wrote:
> "Brian Selzer" wrote in
> news:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>
> > 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. A clear
> > understanding of how and when they can be used and why is critical or
> > you run the risk of a corrupt database.
>
> We often follow certain practices which we may describe as rules. These
> customs may simplify our work, or contribute to its success.
> Tony is a very experienced Access developer; readers of Comp Databases Ms-
> Access have benefited from his sharing that experience, sometimes in
> encapsulated form, as when he describes a usual, customary, or generalized
> course of action or behaviour as a rule.

But remember the audience. This thread is also crossposted to
comp.database.theory So if Tony is going to proclaim rules based on
the limitations of the tool he uses, it would be useful to mention it.
>
> I follow the same rule. Having an auto-number primary key in each table
> simplifies the establishment of relationships among tables. In Access, the
> primary key auto-number ensures that forms bound to the table will be
> editable, although any unique non-null index will serve that purpose. In
> scripts using ADO, the primary key auto-number provides an identifier for
> update and delete actions.
>
> Could you cite instances of databases typically used with Access that have
> been corrupted by auto-number primary keys?

I happen to work with real DBMS products. ACCESS has too many
limitations. A decent DBMS doesn't handicap the Data model designer
based on the limits of the application front end tool.

Bottom line is that now we know, Tony's "rule" applies to ACCESS. So
there is a little justification there, but this is not common practice
in large scale databases.

Ed

Re: Separate PK in Jxn Tbl?

Ed Prochak wrote in
news:1e8dad11-e0bd-4a35-a3de-b7dfb7c6db28@v4g2000hsf.googleg roups.com:

>
>
> lyle fairfield wrote:
>> "Brian Selzer" wrote in
>> news:l1Rlj.2866$nK5.2329@nlpi069.nbdc.sbc.com:
>>
>> > 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.
>> > A clear understanding of how and when they can be used and why is
>> > critical or you run the risk of a corrupt database.
>>
>> We often follow certain practices which we may describe as rules.
>> These customs may simplify our work, or contribute to its success.
>> Tony is a very experienced Access developer; readers of Comp
>> Databases Ms- Access have benefited from his sharing that experience,
>> sometimes in encapsulated form, as when he describes a usual,
>> customary, or generalized course of action or behaviour as a rule.
>
> But remember the audience. This thread is also crossposted to
> comp.database.theory So if Tony is going to proclaim rules based on
> the limitations of the tool he uses, it would be useful to mention it.

I suppose there may be some regulars in comp.database.theory who know
what Access is and how it works, but none of those persons has posted to
this thread yet.

>> I follow the same rule. Having an auto-number primary key in each
>> table simplifies the establishment of relationships among tables. In
>> Access, the primary key auto-number ensures that forms bound to the
>> table will be editable, although any unique non-null index will serve
>> that purpose. In scripts using ADO, the primary key auto-number
>> provides an identifier for update and delete actions.
>>
>> Could you cite instances of databases typically used with Access that
>> have been corrupted by auto-number primary keys?
>
> I happen to work with real DBMS products. ACCESS has too many
> limitations. A decent DBMS doesn't handicap the Data model designer
> based on the limits of the application front end tool.

This is like saying, "I drive a Ford to work. Pansonic Viele has too many
limitations."
Access is not a decent DBMS; this is because it is not a DBMS at all.

It’s quite possible that some regular here in CDMA works with your real
DBMS and Access. If you tell us what it is, we’ll know.

Re: Separate PK in Jxn Tbl?

"Tony Toews [MVP]" wrote in message
news:fmdip39pv9vtpetukd82s7vd1l9gi5jaev@4ax.com...
> "Larry Daugherty" wrote:
>
>>This boil up is a variation of the "Autonumber vs. Natural Key"
>>religious wars that sweep the Access groups on even numbered(?) years.
>>In the meantime OP is probably trying to hide the matches with which
>>he started the fires....
>
> Yup, it's amazing how this all happens. To me the
> particularly amusing
> part is that we're generally quite civil in our discussions that stay in
> the Access
> groups.
>
> I'm saddened, although not surprised, at the comments from a few regulars
> from the
> c.d.theory newsgroup using words such as idiot, imbecile and invincibly
> ignorant.
>

Are we feeling a bit abused? Do we need a pity party? All together now,
1...2...3.... There, now, did that make poor little Tony feel any better?

Please don't lump me in with Badour. I rarely use the terms, 'idiot' and
'imbecile,' but (and I think if you'll reexamine it dispassionately, you'll
agree) your original statement in this thread was, in my opinion, pretty
idiotic. Oh, and by the way, you still haven't shared your reasons. What
are you afraid of?

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

Re: Separate PK in Jxn Tbl?

On Jan 25, 2:23 am, "Tony Toews [MVP]" wrote:
> JOG wrote:
> >I was tempted to simply reply:
>
> >"avoiding using Access is just a rule I have. Why? No good reason. The
> >access fanboys will argue. I don't care"
>
> >...but, hey that sort of sentiment would surely just be peurile, and
> >discourage useful discussion would it not?
>
> BTW I didn't realize that there were people in the theory newsgroup who actually
> wanted to discuss the issues in a reasonable fashion.

Then consider yourself corrected ;) One person's posting style does
not a news group make (and FWIW if you can get past that posting style
there is a lot of value to be had imo). I had a look at your own
website and saw the effort you have put in to help other people, so
kudos for that. But this is partly why I was so suprised at your
"That's my rule, no good reason and if anyone disagrees sod 'em" post.

I have given an example of where using an artifical key broke a
database, and I'd stoutly argue that _hidden_ attributes are
dangerous, period. I'm dubious as to how they simplify queries (they
may shorten them, but not reduce their complexity as far as I can
see), but I can also think of instances where they opposite would be
the case. However, I am of course happy to be illuminated by some
examples, if any access people want to convince me that using
artificial keys on every table I create is a good thing...

J.

>
> 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 athttp://www.granite.ab.ca/accsmstr.htm
> Tony's Microsoft Access Blog -http://msmvps.com/blogs/access/

Re: Separate PK in Jxn Tbl?

On Jan 24, 11:00 pm, "James A. Fortune"
wrote:
> Whenever I have multiple key fields, natural or not, I create an
> AutoNumber PK for pragmatic reasons. The main reason is that it makes
> it easier to create the joins. The theorists are champions at joining
> tables and don't have to be concerned with the complexity of the SQL
> they write.

Word to the wise: 'theorists' hate SQL.

> Queries involving many to many relationships often add
> additional tables later and highlight the need to keep joins as simple
> as possible.

I think I'm with Bob Badour (gulp!) on the issue of complexity,
though: if you think more columns in the ON clause makes a SQL join
more 'complex' then I think you could be looking at things wrong.
Having more characters to type increases the risk of typos? More
columns mean you may omit one in error? The SQL engine may be twice as
slow in handling two columns rather than one? Is it more 'complex' to
split a post address into 'subatomic' columns (address lines from
postal code/zip etc)?

Surely the issue you allude to (I think) is the one that Access
Relationships (as distinct from Jet foreign keys) were invented to
solve? i.e. you pre-define the join columns and 'join type' (inner
join, left outer join or right outer join) and the join clause gets
written as SQL for you when you drop the tables into the Query Builder
thing. I would have thought the 'theorists' would love the fact that
you also create foreign keys in the same Relationships dialog i.e. you
end up with a natural join (not having to explicitly specify the
columns yourself) because one table references the other.

[I tend to be dismissive of tools that write SQL code for me but I
think I should perhaps review my stance e.g. I still write all my SQL
Server procs by hand whereas I have tasked myself to investigate CRUD
generators. But, for the time being, ...] As a SQL coder myself, I
find it more annoying that I have to create multiple joins to get the
'natural key' values, having to discover what the 'artificial key'
columns are in the first place.

> Lately, I've increased the amount of normalization in one
> of my databases and the joins got even more complicated, adding about a
> line or so in the SQL view in Access for every new query using those
> tables.

Bad luck: I think you might have got way with "reduced the amount of
denormalization" ;-) In this thread I've already broken my personal
rule (!!) about not mentioning normalization [formulated because the
average 'replier' around here thinks "fully normalized" is BCNF, which
they think is 3NF anyhow, and doesn't pay much attention to anomalies
that normalization doesn't address, unless the 'asker' mentions
storing calculations...]

> I keep Jamie's advice in the back of my mind, about how enforcing
> constraints at the table level is better than enforcing them through
> code

...and best to do it in both places! Bear in mind that it's a rule of
thumb i.e. "strict rules modified in practise." Checking something in
the in front end allows you to give timely user feedback and could
save them some keying, not to mention a database roundtrip. Checking
in the database catches anything neglected in the front end by
omission of validation or introduction of bugs. In practice, some
things are better done in one place but not the other: contrast the
validation of the basic pattern of an email address with the
verification that an addressable entity can be contacted at that email
address; I don't think it would be sensible to put the latter test
into a table constraint, even if it were possible.

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 23, 12:45 am, "Neil" wrote:
> > I've done both; the separate primary key is (strictly speaking) never
> > necessary, but it can be handy if the junction table is itself related
> > one-to-many to an additional table or tables. Access doesn't make
> > multifield
> > foreign keys all that easy to use (e.g. you can't use them in a combobox
> > without some messy code).
>
> Good to know. That makes sense, about needing the PK to refer separately to
> the junction table, if that situation exists.

Why do you say "needing the PK"? JohnV no doubt makes a good case when
considering bound controls in Access but I would point out that he
also said the PK is "never necessary, but it can be handy".

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 25, 1:13 am, "Tony Toews [MVP]" 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/microsoft.public.access.conve rsion/msg/163c373772d787d3

"One idiot student once emailed me"
http://groups.google.com/group/microsoft.public.access.modul esdaovba/msg/4d5e152c11a9e0a3

"tell him he's an idiot"
http://groups.google.com/group/microsoft.public.access/msg/3 1c0abc7b367656d

"This person is an idiot."
http://groups.google.com/group/microsoft.public.access/msg/6 fedf72a4680cf25

"But no, some idiot manager at Mickeysoft..."
http://groups.google.com/group/microsoft.public.access.forms /msg/222d3ed992e9a2ec

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 25, 12:59 pm, JOG wrote:
> I had a look at your own
> website and saw the effort you have put in to help other people, so
> kudos for that. But this is partly why I was so suprised at your
> "That's my rule, no good reason and if anyone disagrees sod 'em" post.

I too would like to pay tribute here to Tony Toews Access MVP, he does
help a lot of people and does not deserve a hard time (same for Larry
Linson, great guy, kind of the grandfather of this group, I like to
think, though I still wonder what I did to offend him). I too read his
website a while back and there's some good stuff in there and some
links to some great chuckle-some comedy. Which leads me nicely...

Sorry to spoil anyone's fund but am I the only one to spot the
duplicitous (pun intended, natch) nature of this post and other
similar ones recently i.e. where he *seems* to treat people he should
care about (community regulars, newbies, his own clients, etc) with
contempt? I think he knew he was lighting the blue touch paper of this
thread by saying, "I don't care" and I might be partly responsible for
this because I did kind of give him a good response to the same trick
last week. Come on people, lighten up! Tony is being light-hearted,
tickling your ribs. And I congratulate him for it, things can get dull
around here :)

Jamie.

--

Re: Separate PK in Jxn Tbl?

Jamie Collins wrote:
> On Jan 25, 1:13 am, "Tony Toews [MVP]" 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/microsoft.public.access.conve rsion/msg/163c373772d787d3
>
> "One idiot student once emailed me"
> http://groups.google.com/group/microsoft.public.access.modul esdaovba/msg/4d5e152c11a9e0a3
>
> "tell him he's an idiot"
> http://groups.google.com/group/microsoft.public.access/msg/3 1c0abc7b367656d
>
> "This person is an idiot."
> http://groups.google.com/group/microsoft.public.access/msg/6 fedf72a4680cf25
>
> "But no, some idiot manager at Mickeysoft..."
> http://groups.google.com/group/microsoft.public.access.forms /msg/222d3ed992e9a2ec
>
> Jamie.

Oops.

Re: Separate PK in Jxn Tbl?

On Jan 25, 9:12=A0am, Jamie Collins wrote:
> On Jan 24, 11:00 pm, "James A. Fortune"
> wrote:
>
> > Whenever I have multiple key fields, natural or not, I create an
> > AutoNumber PK for pragmatic reasons. =A0The main reason is that it makes=

> > it easier to create the joins. =A0The theorists are champions at joining=

> > tables and don't have to be concerned with the complexity of the SQL
> > they write.
>
> Word to the wise: 'theorists' hate SQL.
>
> > Queries involving many to many relationships often add
> > additional tables later and highlight the need to keep joins as simple
> > as possible.
>
> I think I'm with Bob Badour (gulp!) on the issue of complexity,
> though: if you think more columns in the ON clause makes a SQL join
> more 'complex' then I think you could be looking at things wrong.
> Having more characters to type increases the risk of typos? More
> columns mean you may omit one in error? The SQL engine may be twice as
> slow in handling two columns rather than one? Is it more 'complex' to
> split a post address into 'subatomic' columns (address lines from
> postal code/zip etc)?
>
> Surely the issue you allude to (I think) is the one that Access
> Relationships (as distinct from Jet foreign keys) were invented to
> solve? i.e. you pre-define the join columns and 'join type' (inner
> join, left outer join or right outer join) and the join clause gets
> written as SQL for you when you drop the tables into the Query Builder
> thing. I would have thought the 'theorists' would love the fact that
> you also create foreign keys in the same Relationships dialog i.e. you
> end up with a natural join (not having to explicitly specify the
> columns yourself) because one table references the other.
>
> [I tend to be dismissive of tools that write SQL code for me but I
> think I should perhaps review my stance e.g. I still write all my SQL
> Server procs by hand whereas I have tasked myself to investigate CRUD
> generators. But, for the time being, ...] As a SQL coder myself, I
> find it more annoying that I have to create multiple joins to get the
> 'natural key' values, having to discover what the 'artificial key'
> columns are in the first place.
>
> > Lately, I've increased the amount of normalization in one
> > of my databases and the joins got even more complicated, adding about a
> > line or so in the SQL view in Access for every new query using those
> > tables.
>
> Bad luck: I think you might have got way with "reduced the amount of
> denormalization" ;-) In this thread I've already broken my personal
> rule (!!) about not mentioning normalization [formulated because the
> average 'replier' around here thinks "fully normalized" is BCNF, which
> they think is 3NF anyhow, and doesn't pay much attention to anomalies
> that normalization doesn't address, unless the 'asker' mentions
> storing calculations...]
>
> > I keep Jamie's advice in the back of my mind, about how enforcing
> > constraints at the table level is better than enforcing them through
> > code
>
> ..and best to do it in both places! Bear in mind that it's a rule of
> thumb i.e. "strict rules modified in practise." Checking something in
> the in front end allows you to give timely user feedback and could
> save them some keying, not to mention a database roundtrip. Checking
> in the database catches anything neglected in the front end by
> omission of validation or introduction of bugs. In practice, some
> things are better done in one place but not the other: contrast the
> validation of the basic pattern of an email address with the
> verification that an addressable entity can be contacted at that email
> address; I don't think it would be sensible to put the latter test
> into a table constraint, even if it were possible.
>
> Jamie.
>
> --

What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

To that, I would add that the increased simplicity of using a surrogate (or
artificial or autonumber) key as the primary key in place of a composite key
is only half their advantage.

The biggest problem that I have with composite keys is that they share the
same fundamental problem as natural keys: using them as the primary key is
allowing the fact that a primary key can change its value over time. IMHO,
a primary key should never be allowed to change its value once it has been
created; a assumption which will forbid the use of a composite key in many
cases. (Of course, if you don't mind to see a primary key changing its
value after its creation then you are not concerned by this argument.).

This is not only a theoritical argument as many interfaces - like Access -
won't like to see a primary key that could change it value. But even if you
take out such interfaces out of the equation, the use of a surrogate key for
all tables reveals itself to be advantageous in many database problems. For
example, if you want to add a log of all changes to a table, it's much more
easier to design it if the table use a surrogate key for its primary key
than a natural key or a composite key.

Personally, I stopped using natural keys and composite keys many years ago
and probably that something like half of my problems with the design of
databases have vanished with them. On these occasions when I was called to
work on a problematic database, chances was much higher to see that the
problems were associated with the use of natural keys and/or composite keys
than with the use of a surrogate keys and the solutions were usually much
more complicated to solve in the first case than in the second case.

Also, I've remember some peoples who have done like me and have stopped
using natural and composite keys in favor of the exclusive use of surrogate
keys but I don't remember anyone doing the opposite; ie. going from the use
of surrogate keys to the use of natural and composite keys.

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


wrote in message
news:a12c7757-aea7-4d11-bf7c-3bd4b7feb442@n20g2000hsh.google groups.com...
On Jan 25, 9:12 am, Jamie Collins wrote:
> On Jan 24, 11:00 pm, "James A. Fortune"
> wrote:
>
> > Whenever I have multiple key fields, natural or not, I create an
> > AutoNumber PK for pragmatic reasons. The main reason is that it makes
> > it easier to create the joins. The theorists are champions at joining
> > tables and don't have to be concerned with the complexity of the SQL
> > they write.
>
> Word to the wise: 'theorists' hate SQL.
>
> > Queries involving many to many relationships often add
> > additional tables later and highlight the need to keep joins as simple
> > as possible.
>
> I think I'm with Bob Badour (gulp!) on the issue of complexity,
> though: if you think more columns in the ON clause makes a SQL join
> more 'complex' then I think you could be looking at things wrong.
> Having more characters to type increases the risk of typos? More
> columns mean you may omit one in error? The SQL engine may be twice as
> slow in handling two columns rather than one? Is it more 'complex' to
> split a post address into 'subatomic' columns (address lines from
> postal code/zip etc)?
>
> Surely the issue you allude to (I think) is the one that Access
> Relationships (as distinct from Jet foreign keys) were invented to
> solve? i.e. you pre-define the join columns and 'join type' (inner
> join, left outer join or right outer join) and the join clause gets
> written as SQL for you when you drop the tables into the Query Builder
> thing. I would have thought the 'theorists' would love the fact that
> you also create foreign keys in the same Relationships dialog i.e. you
> end up with a natural join (not having to explicitly specify the
> columns yourself) because one table references the other.
>
> [I tend to be dismissive of tools that write SQL code for me but I
> think I should perhaps review my stance e.g. I still write all my SQL
> Server procs by hand whereas I have tasked myself to investigate CRUD
> generators. But, for the time being, ...] As a SQL coder myself, I
> find it more annoying that I have to create multiple joins to get the
> 'natural key' values, having to discover what the 'artificial key'
> columns are in the first place.
>
> > Lately, I've increased the amount of normalization in one
> > of my databases and the joins got even more complicated, adding about a
> > line or so in the SQL view in Access for every new query using those
> > tables.
>
> Bad luck: I think you might have got way with "reduced the amount of
> denormalization" ;-) In this thread I've already broken my personal
> rule (!!) about not mentioning normalization [formulated because the
> average 'replier' around here thinks "fully normalized" is BCNF, which
> they think is 3NF anyhow, and doesn't pay much attention to anomalies
> that normalization doesn't address, unless the 'asker' mentions
> storing calculations...]
>
> > I keep Jamie's advice in the back of my mind, about how enforcing
> > constraints at the table level is better than enforcing them through
> > code
>
> ..and best to do it in both places! Bear in mind that it's a rule of
> thumb i.e. "strict rules modified in practise." Checking something in
> the in front end allows you to give timely user feedback and could
> save them some keying, not to mention a database roundtrip. Checking
> in the database catches anything neglected in the front end by
> omission of validation or introduction of bugs. In practice, some
> things are better done in one place but not the other: contrast the
> validation of the basic pattern of an email address with the
> verification that an addressable entity can be contacted at that email
> address; I don't think it would be sensible to put the latter test
> into a table constraint, even if it were possible.
>
> Jamie.
>
> --

What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:uTvWO%23%23XIHA.1532@TK2MSFTNGP04.phx.gbl...
> To that, I would add that the increased simplicity of using a surrogate
> (or artificial or autonumber) key as the primary key in place of a
> composite key is only half their advantage.
>
> The biggest problem that I have with composite keys is that they share the
> same fundamental problem as natural keys: using them as the primary key is
> allowing the fact that a primary key can change its value over time.
> IMHO, a primary key should never be allowed to change its value once it
> has been created; a assumption which will forbid the use of a composite
> key in many cases. (Of course, if you don't mind to see a primary key
> changing its value after its creation then you are not concerned by this
> argument.).
>

This argument has an inherent fallacy in it. Just because a key is composed
from multiple columns doesn't necessarily mean that its values can be
different in different database states. For example, in an Inventory table
that has the key, {ItemKey, WarehouseKey}, with references to an Item table
and a Warehouse table respectively, the combination values that comprise
each key value can never change from database state to database state. A
particular combination of values identifies a particular individual in the
Universe of Discourse in /every/ database state in which it appears. It can
/never/ identify any other individual. Therefore, it should be obvious that
adding an additional autonumber primary key in this instance would be
superfluous, since each {ItemKey, WarehouseKey} combination already rigidly
designates a distinct individual in the Universe of Discourse.

The same can be said for many natural keys. For example, suppose you have a
table, Queue, that has an integer key, {Position}. Each value for Position
rigidly designates a distinct individual in the Universe of Discourse (3
always means "third in line" in any database state in which there are 3 or
more elements), so therefore there is no need for an additional autonumber
primary key.

> This is not only a theoritical argument as many interfaces - like Access -
> won't like to see a primary key that could change it value. But even if
> you take out such interfaces out of the equation, the use of a surrogate
> key for all tables reveals itself to be advantageous in many database
> problems. For example, if you want to add a log of all changes to a
> table, it's much more easier to design it if the table use a surrogate key
> for its primary key than a natural key or a composite key.
>
> Personally, I stopped using natural keys and composite keys many years ago
> and probably that something like half of my problems with the design of
> databases have vanished with them. On these occasions when I was called
> to work on a problematic database, chances was much higher to see that the
> problems were associated with the use of natural keys and/or composite
> keys than with the use of a surrogate keys and the solutions were usually
> much more complicated to solve in the first case than in the second case.
>
> Also, I've remember some peoples who have done like me and have stopped
> using natural and composite keys in favor of the exclusive use of
> surrogate keys but I don't remember anyone doing the opposite; ie. going
> from the use of surrogate keys to the use of natural and composite keys.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> wrote in message
> news:a12c7757-aea7-4d11-bf7c-3bd4b7feb442@n20g2000hsh.google groups.com...
> On Jan 25, 9:12 am, Jamie Collins wrote:
>> On Jan 24, 11:00 pm, "James A. Fortune"
>> wrote:
>>
>> > Whenever I have multiple key fields, natural or not, I create an
>> > AutoNumber PK for pragmatic reasons. The main reason is that it makes
>> > it easier to create the joins. The theorists are champions at joining
>> > tables and don't have to be concerned with the complexity of the SQL
>> > they write.
>>
>> Word to the wise: 'theorists' hate SQL.
>>
>> > Queries involving many to many relationships often add
>> > additional tables later and highlight the need to keep joins as simple
>> > as possible.
>>
>> I think I'm with Bob Badour (gulp!) on the issue of complexity,
>> though: if you think more columns in the ON clause makes a SQL join
>> more 'complex' then I think you could be looking at things wrong.
>> Having more characters to type increases the risk of typos? More
>> columns mean you may omit one in error? The SQL engine may be twice as
>> slow in handling two columns rather than one? Is it more 'complex' to
>> split a post address into 'subatomic' columns (address lines from
>> postal code/zip etc)?
>>
>> Surely the issue you allude to (I think) is the one that Access
>> Relationships (as distinct from Jet foreign keys) were invented to
>> solve? i.e. you pre-define the join columns and 'join type' (inner
>> join, left outer join or right outer join) and the join clause gets
>> written as SQL for you when you drop the tables into the Query Builder
>> thing. I would have thought the 'theorists' would love the fact that
>> you also create foreign keys in the same Relationships dialog i.e. you
>> end up with a natural join (not having to explicitly specify the
>> columns yourself) because one table references the other.
>>
>> [I tend to be dismissive of tools that write SQL code for me but I
>> think I should perhaps review my stance e.g. I still write all my SQL
>> Server procs by hand whereas I have tasked myself to investigate CRUD
>> generators. But, for the time being, ...] As a SQL coder myself, I
>> find it more annoying that I have to create multiple joins to get the
>> 'natural key' values, having to discover what the 'artificial key'
>> columns are in the first place.
>>
>> > Lately, I've increased the amount of normalization in one
>> > of my databases and the joins got even more complicated, adding about a
>> > line or so in the SQL view in Access for every new query using those
>> > tables.
>>
>> Bad luck: I think you might have got way with "reduced the amount of
>> denormalization" ;-) In this thread I've already broken my personal
>> rule (!!) about not mentioning normalization [formulated because the
>> average 'replier' around here thinks "fully normalized" is BCNF, which
>> they think is 3NF anyhow, and doesn't pay much attention to anomalies
>> that normalization doesn't address, unless the 'asker' mentions
>> storing calculations...]
>>
>> > I keep Jamie's advice in the back of my mind, about how enforcing
>> > constraints at the table level is better than enforcing them through
>> > code
>>
>> ..and best to do it in both places! Bear in mind that it's a rule of
>> thumb i.e. "strict rules modified in practise." Checking something in
>> the in front end allows you to give timely user feedback and could
>> save them some keying, not to mention a database roundtrip. Checking
>> in the database catches anything neglected in the front end by
>> omission of validation or introduction of bugs. In practice, some
>> things are better done in one place but not the other: contrast the
>> validation of the basic pattern of an email address with the
>> verification that an addressable entity can be contacted at that email
>> address; I don't think it would be sensible to put the latter test
>> into a table constraint, even if it were possible.
>>
>> Jamie.
>>
>> --
>
> What part of simpler don't you understand :-). Only one expression in
> the ON is simpler. Needing less indexes is simpler. Not having to
> look for your multi-key fields is easier, although your point that
> Relationships can handle that is valid. If the AutoNumber key has a
> one-to-one relationship with the multi-key fields then it's fine to
> use it. There's no down side that I can see. I also like to rely on
> coding to detect inconsistent data rather than on error trapping, so I
> have to check the multi-key values anyway before adding a new record.
> I think that your idea about enforcing constraints at both the table
> level and in code is an excellent idea. The OP wanted to know what
> people did and why. I still don't see any reason put forward for me
> to change to a multi-field key. Are totals queries easier when multi-
> field keys are used? BTW, "reduced the amount of denormalization"
> works just as well. Real databases experience denormalizing
> influences.
>
> James A. Fortune
> CDMAPoster@FortuneJames.com
>

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:uTvWO%23%23XIHA.1532@TK2MSFTNGP04.phx.gbl...
> To that, I would add that the increased simplicity of using a surrogate
> (or artificial or autonumber) key as the primary key in place of a
> composite key is only half their advantage.
>
> The biggest problem that I have with composite keys is that they share the
> same fundamental problem as natural keys: using them as the primary key is
> allowing the fact that a primary key can change its value over time.
> IMHO, a primary key should never be allowed to change its value once it
> has been created; a assumption which will forbid the use of a composite
> key in many cases. (Of course, if you don't mind to see a primary key
> changing its value after its creation then you are not concerned by this
> argument.).

I have decided not to respond to this post in detail because there isn't a
single point it makes that I agree with (as stated). I really don't know
where to start. One specific comment I will make is that my progression has
been the opposite of yours. I once used synthetic keys everywhere, but now
try to limit my use of them, with wholly beneficial effects. That may be
why I write about this with the fervor of a born-again convert.

I would have a lot more sympathy for these kinds of claims if the people
making them would give any hint that they know what the alternatives are,
and why their solutions make sense within application development tools.
For example, I don't think I've ever seen anyone enthusing about the liberal
use of synthetic keys who also noted that they are aware of the possibility
of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can
persuade me you've looked at it and had to reject for reasons X, Y, and Z, I
can respect that. Or tell me you know about it but your particular product
doesn't support it and I can respect that. Or tell me that you understand
that the DBMS handles the problem almost trivially but the application
development tools make you write extra code and I can respect that.

And what is all this tripe about composite keys making the SQL more complex?
If I bodge up my tables with a spurious third synthetic key (skey) so that
instead of writing:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
using (ordernr,itemnr)

I can instead write:

select i.description, b.cause_of_damage
from orderitems i left join breakages b
on i.skey = b.skey

How much easier is THAT?? And at what cost?

If you don't convince me that you properly understand the problems, and
crucially, where the problems *really* lie, then you aren't going to
convince me that your solutions are anything but cut-and-paste rote-learned
hackery that seems elegant/sensible only to those with limited knowledge of
very limited products. You have to establish that you are credible.
Merely claiming years and years of experience (as others have) could just
mean they've been successfully getting away with being incompetent--and
goodness knows, that happens, so they won't get the benefit of the doubt.

Roy

Re: Separate PK in Jxn Tbl?

Bob,

Here's a programming tip for you: prozac.


"Bob Badour" wrote in message
news:479941ba$0$4039$9a566e8b@news.aliant.net...
> Larry Daugherty wrote:
>
>> Every dog gets one bite. Then they go into the bs/Kill file.
>>
>> Did you note the net contribution to the subject at hand? Their
>> commentary serves only to identify and characterize *themselves*
>>
>> My hope is to make meaningful contributions to those who are actually
>> developing applications or learning to do so.
>
> Your hope is futile in the face of your ignorance and laziness.
>
>
>> Keep up the good work!
>
> I'll try.
>
>
> "Tony Toews [MVP]" wrote in message
> news:fmdip39pv9vtpetukd82s7vd1l9gi5jaev@4ax.com...
>
> >> "Larry Daugherty" wrote:
> >>
> >
> >>> >This boil up is a variation of the "Autonumber vs. Natural Key"
> >>> >religious wars that sweep the Access groups on even numbered(?)
> >>> >years.
> >>> >In the meantime OP is probably trying to hide the matches with which
> >>> >he started the fires....
> >
> >>
> >> Yup, it's amazing how this all happens. To me the
> particularly amusing
> >> part is that we're generally quite civil in our discussions that
> stay in the Access
> >> groups.
>
> Ignorance is bliss.
>
>
> >> I'm saddened, although not surprised, at the comments from a few
> regulars from the
> >> c.d.theory newsgroup using words such as idiot, imbecile and
> invincibly ignorant.
>
> Precious few pleasant ways exist to express unpleasant truths. However,
> intellectual honesty requires the expression of truths regardless of
> appeal to oneself or to others.

Re: Separate PK in Jxn Tbl?

"Jamie Collins" wrote in message
news:08ac2bd3-dcb5-47d4-ae8b-02ec005237fd@c23g2000hsa.google groups.com...
> On Jan 23, 12:45 am, "Neil" wrote:
>> > I've done both; the separate primary key is (strictly speaking) never
>> > necessary, but it can be handy if the junction table is itself related
>> > one-to-many to an additional table or tables. Access doesn't make
>> > multifield
>> > foreign keys all that easy to use (e.g. you can't use them in a
>> > combobox
>> > without some messy code).
>>
>> Good to know. That makes sense, about needing the PK to refer separately
>> to
>> the junction table, if that situation exists.
>
> Why do you say "needing the PK"? JohnV no doubt makes a good case when
> considering bound controls in Access but I would point out that he
> also said the PK is "never necessary, but it can be handy".


OK, I'll rephrase:

"That makes sense, about the PK coming in handy to refer spearately to the
junction table, if that situation exists."

Better? :-)

Re: Separate PK in Jxn Tbl?

"Larry Daugherty" wrote in message
news:urlnw2uXIHA.5448@TK2MSFTNGP04.phx.gbl...
> In the meantime OP is probably trying to hide the matches with which
> he started the fires....
>

Indeed. :-|

Re: Separate PK in Jxn Tbl?

wrote in message
news:a12c7757-aea7-4d11-bf7c-3bd4b7feb442@n20g2000hsh.google groups.com...
On Jan 25, 9:12 am, Jamie Collins wrote:
(quote)
What part of simpler don't you understand :-). Only one expression in
the ON is simpler. Needing less indexes is simpler. Not having to
look for your multi-key fields is easier, although your point that
Relationships can handle that is valid. If the AutoNumber key has a
one-to-one relationship with the multi-key fields then it's fine to
use it. There's no down side that I can see. I also like to rely on
coding to detect inconsistent data rather than on error trapping, so I
have to check the multi-key values anyway before adding a new record.
I think that your idea about enforcing constraints at both the table
level and in code is an excellent idea. The OP wanted to know what
people did and why. I still don't see any reason put forward for me
to change to a multi-field key. Are totals queries easier when multi-
field keys are used? BTW, "reduced the amount of denormalization"
works just as well. Real databases experience denormalizing
influences.

(end quote)

Simplicity is in the eye of the beholder.

I think it's simpler to rely on constraints enforced by the DBMS to prevent
duplicate entries
than it is to write code to accomplish the same thing.

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.

The above comment depends entirely on how you go about organizing you code.
I like to keep my code simple. At least "simple" in my own eyes.

The use of multi-key fields in star schemas doesn't make individual queries
any simpler. But it expands by orders of magnitude the number of different
combinations that can be used as selection criteria when computing totals or
other aggregates. This makes the entire system simpler, on a lerger scale.

I realize that star schema discussions may be out of place in MS access
newsgroups. The same is true in c.d.t. There is almost nothing of
theoretical interest in star schemas.

Re: Separate PK in Jxn Tbl?

Roy Hann wrote:

> "Sylvain Lafontaine"
> wrote in message news:uTvWO%23%23XIHA.1532@TK2MSFTNGP04.phx.gbl...
>
>>To that, I would add that the increased simplicity of using a surrogate
>>(or artificial or autonumber) key as the primary key in place of a
>>composite key is only half their advantage.
>>
>>The biggest problem that I have with composite keys is that they share the
>>same fundamental problem as natural keys: using them as the primary key is
>>allowing the fact that a primary key can change its value over time.
>>IMHO, a primary key should never be allowed to change its value once it
>>has been created; a assumption which will forbid the use of a composite
>>key in many cases. (Of course, if you don't mind to see a primary key
>>changing its value after its creation then you are not concerned by this
>>argument.).
>
>
> I have decided not to respond to this post in detail because there isn't a
> single point it makes that I agree with (as stated). I really don't know
> where to start.

That, in a nutshell, is Date's _Principle of Incoherence_.


One specific comment I will make is that my progression has
> been the opposite of yours. I once used synthetic keys everywhere, but now
> try to limit my use of them, with wholly beneficial effects. That may be
> why I write about this with the fervor of a born-again convert.
>
> I would have a lot more sympathy for these kinds of claims if the people
> making them would give any hint that they know what the alternatives are,
> and why their solutions make sense within application development tools.
> For example, I don't think I've ever seen anyone enthusing about the liberal
> use of synthetic keys who also noted that they are aware of the possibility
> of declaring a foreign key constraint WITH ON UPDATE CASCADE. If you can
> persuade me you've looked at it and had to reject for reasons X, Y, and Z, I
> can respect that. Or tell me you know about it but your particular product
> doesn't support it and I can respect that. Or tell me that you understand
> that the DBMS handles the problem almost trivially but the application
> development tools make you write extra code and I can respect that.
>
> And what is all this tripe about composite keys making the SQL more complex?
> If I bodge up my tables with a spurious third synthetic key (skey) so that
> instead of writing:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> using (ordernr,itemnr)
>
> I can instead write:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> on i.skey = b.skey
>
> How much easier is THAT?? And at what cost?
>
> If you don't convince me that you properly understand the problems, and
> crucially, where the problems *really* lie, then you aren't going to
> convince me that your solutions are anything but cut-and-paste rote-learned
> hackery that seems elegant/sensible only to those with limited knowledge of
> very limited products. You have to establish that you are credible.
> Merely claiming years and years of experience (as others have)

I have found some people can work for 10 years and get a year's
experience 10 times.


could just
> mean they've been successfully getting away with being incompetent--and
> goodness knows, that happens, so they won't get the benefit of the doubt.
>
> Roy

Re: Separate PK in Jxn Tbl?

Hi Sylvain,

First, let me thank you for being so kind as to volunteer the
information that you are a Most Vociferous Person (MVP). It does a fair
service to the world when the self-aggrandizing ignorants self-declare
that information.

Sylvain Lafontaine wrote:

> To that, I would add that the increased simplicity of using a surrogate (or
> artificial or autonumber) key as the primary key in place of a composite key
> is only half their advantage.

At this point, a prudent man would Plonk! you while mentally citing
Date's _Principle of Incoherence_. Never the prudent man, instead, I
observe the absurdity of your suggestion that adding features,
structures or attributes increases simplicity. What nonsense!


> The biggest problem that I have with composite keys is that they share the
> same fundamental problem as natural keys: using them as the primary key is
> allowing the fact that a primary key can change its value over time. IMHO,
> a primary key should never be allowed to change its value once it has been
> created; a assumption which will forbid the use of a composite key in many
> cases.

I find your absolutism foolish suggesting ignorance and/or stupidity.

The design criteria for keys are: uniqueness, irreducibility,
simplicity, stability and familiarity (in no particular order). If any
criterion is absolute, it is uniqueness not stability.


(Of course, if you don't mind to see a primary key changing its
> value after its creation then you are not concerned by this argument.).
>
> This is not only a theoritical argument as many interfaces - like Access -
> won't like to see a primary key that could change it value.

It is not a theoretical argument at all. You simply regurgitate
ignorance and stupidity.

[remaining nonsense snipped]

Plonk!

Re: Separate PK in Jxn Tbl?

David Cressey wrote:

> wrote in message
> news:a12c7757-aea7-4d11-bf7c-3bd4b7feb442@n20g2000hsh.google groups.com...
> On Jan 25, 9:12 am, Jamie Collins wrote:
> (quote)
> What part of simpler don't you understand :-). Only one expression in
> the ON is simpler. Needing less indexes is simpler. Not having to
> look for your multi-key fields is easier, although your point that
> Relationships can handle that is valid. If the AutoNumber key has a
> one-to-one relationship with the multi-key fields then it's fine to
> use it. There's no down side that I can see. I also like to rely on
> coding to detect inconsistent data rather than on error trapping, so I
> have to check the multi-key values anyway before adding a new record.
> I think that your idea about enforcing constraints at both the table
> level and in code is an excellent idea. The OP wanted to know what
> people did and why. I still don't see any reason put forward for me
> to change to a multi-field key. Are totals queries easier when multi-
> field keys are used? BTW, "reduced the amount of denormalization"
> works just as well. Real databases experience denormalizing
> influences.
>
> (end quote)
>
> Simplicity is in the eye of the beholder.

I tend to disagree. I suspect one can quantify simplicity and complexity.


> I think it's simpler to rely on constraints enforced by the DBMS to prevent
> duplicate entries
> than it is to write code to accomplish the same thing.

Using the dbms uses fewer tools, fewer concepts, fewer computational
models, fewer structures, fewer machines. I suggest the observed
simplicity is more than a matter of perspective or opinion.

[further demonstrations of simplicity snipped]

Re: Separate PK in Jxn Tbl?

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.


Marshall

Re: Separate PK in Jxn Tbl?

Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
interesting argument and one that come back often; however it's not a silver
buller. First of all, it's another level of complexity that you must add to
the design of your database; ie, you must make sure that they are all there
and no one is missing. Second, this DRI cannot be used with cyclic
relationship with SQL-Server but with Oracle, you can. (From your example,
I believe that you are working with Oracle). On SQL-Server, you must use
triggers to implement such a feature when there is a cyclic relationship.
Of course, when you are dealing with tens and hundreds of relationships,
this can quickly translate into a nightmare. There is also the qestion of
the diminution of performance and of general design: when you have to update
multiples records on multiple tables for what should be the change of a
single value in a single table make it hard to believe that this is a proper
normalized database design and this situation quickly worsen if you have to
take into account the correspondance with backups, reports and linked
databases; all systems for which there is no automatic DRI.

But why make it simpler when you can make it harder?

Finally, I don't understand your example at all. You are introducing us to
the NATURAL JOIN and USING statement that have been introduced by Oracle in
its 9i version (also in MySQL and Postgres, I believe) but I fail to see
what this has to do with the subject of this thread; the use of a separate
PK in a junction table and its highly related topic, ie. the use of natural
keys versus the use of surrogate keys. There is no relationship at all
between a NATURAL JOIN and a natural key and the Natural Join can be used as
easily with a surrogate key than with a natural key. The only thing that is
important with the Natural Join is the name of the key. (BTW, if you were
to ask me what I'm thinking about this little monstruosity, I would tell you
that this is a perfect example of a Pandora box.).

And finally, a for your request of asking me to convince you that I'm
properly understand the problem here of to etablish that I'm credible: I can
tell you that I have absolutely no intention of doing it and that I have
absolutely no interest at all about what you are thinking of me. The only
things that are of interest to me are the arguments that I'm seeing posted
here - whatever the people who might write them - but for someone who has
just make a confusion between a natural key and the NATURAL JOIN, asking for
such a thing make it looks very strange.

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


"Roy Hann" wrote in message
news:w5adnaS22JoBmwbanZ2dnUVZ8uadnZ2d@pipex.net...
> "Sylvain Lafontaine"
> wrote in message news:uTvWO%23%23XIHA.1532@TK2MSFTNGP04.phx.gbl...
>> To that, I would add that the increased simplicity of using a surrogate
>> (or artificial or autonumber) key as the primary key in place of a
>> composite key is only half their advantage.
>>
>> The biggest problem that I have with composite keys is that they share
>> the same fundamental problem as natural keys: using them as the primary
>> key is allowing the fact that a primary key can change its value over
>> time. IMHO, a primary key should never be allowed to change its value
>> once it has been created; a assumption which will forbid the use of a
>> composite key in many cases. (Of course, if you don't mind to see a
>> primary key changing its value after its creation then you are not
>> concerned by this argument.).
>
> I have decided not to respond to this post in detail because there isn't a
> single point it makes that I agree with (as stated). I really don't know
> where to start. One specific comment I will make is that my progression
> has been the opposite of yours. I once used synthetic keys everywhere,
> but now try to limit my use of them, with wholly beneficial effects. That
> may be why I write about this with the fervor of a born-again convert.
>
> I would have a lot more sympathy for these kinds of claims if the people
> making them would give any hint that they know what the alternatives are,
> and why their solutions make sense within application development tools.
> For example, I don't think I've ever seen anyone enthusing about the
> liberal use of synthetic keys who also noted that they are aware of the
> possibility of declaring a foreign key constraint WITH ON UPDATE CASCADE.
> If you can persuade me you've looked at it and had to reject for reasons
> X, Y, and Z, I can respect that. Or tell me you know about it but your
> particular product doesn't support it and I can respect that. Or tell me
> that you understand that the DBMS handles the problem almost trivially but
> the application development tools make you write extra code and I can
> respect that.
>
> And what is all this tripe about composite keys making the SQL more
> complex? If I bodge up my tables with a spurious third synthetic key
> (skey) so that instead of writing:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> using (ordernr,itemnr)
>
> I can instead write:
>
> select i.description, b.cause_of_damage
> from orderitems i left join breakages b
> on i.skey = b.skey
>
> How much easier is THAT?? And at what cost?
>
> If you don't convince me that you properly understand the problems, and
> crucially, where the problems *really* lie, then you aren't going to
> convince me that your solutions are anything but cut-and-paste
> rote-learned hackery that seems elegant/sensible only to those with
> limited knowledge of very limited products. You have to establish that
> you are credible. Merely claiming years and years of experience (as others
> have) could just mean they've been successfully getting away with being
> incompetent--and goodness knows, that happens, so they won't get the
> benefit of the doubt.
>
> Roy
>

Re: Separate PK in Jxn Tbl?

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

Personally, I don't take the natural keys out either, so they can still
be used for the deletion. The thought of giving the SQL data an address
and following a programmer's mental model did not enter into my thinking
at all. I am not trying to give the data an order either. You've been
listening to Celko too much. Because of his overall manner, which I
find quite offensive, I don't even want to listen to him when he's right
:-). Is the AutoNumber primary key a denormalization of the schema?
Yes. Is it added for a reason? Yes again. I'm still waiting for a
cogent reason for me to go to using natural keys.

James A. Fortune
MPAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

On Jan 27, 2:09 am, "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.
>
> > Marshall
>
> Personally, I don't take the natural keys out either, so they can still
> be used for the deletion. The thought of giving the SQL data an address
> and following a programmer's mental model did not enter into my thinking
> at all. I am not trying to give the data an order either. You've been
> listening to Celko too much. Because of his overall manner, which I
> find quite offensive, I don't even want to listen to him when he's right
> :-). Is the AutoNumber primary key a denormalization of the schema?
> Yes. Is it added for a reason? Yes again. I'm still waiting for a
> cogent reason for me to go to using natural keys.

* Artificial keys allow you to enter the exact same statement of fact
twice. This would simply be nonsense.
* Artificial keys allows a tuple at t1 and a tuple at t2 to be
corresponded to each other, even if they don't have a _single_
attribute from the real world in common. This would also simply be
nonsense.
* 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.

So not one, but three cogent reasons of the top of my head. I wouldn't
say there are never cases when an artificial key is useful, but they
certainly shouldn't be hidden, and adding them blindly to every
relation is surely just a bit silly. Regards, J.

>
> James A. Fortune
> MPAPos...@FortuneJames.com

Re: Separate PK in Jxn Tbl?

JOG wrote:
> On Jan 27, 2:09 am, "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.
>>
>>>Marshall
>>
>>Personally, I don't take the natural keys out either, so they can still
>>be used for the deletion. The thought of giving the SQL data an address
>>and following a programmer's mental model did not enter into my thinking
>>at all. I am not trying to give the data an order either. You've been
>>listening to Celko too much. Because of his overall manner, which I
>>find quite offensive, I don't even want to listen to him when he's right
>>:-). Is the AutoNumber primary key a denormalization of the schema?
>>Yes. Is it added for a reason? Yes again. I'm still waiting for a
>>cogent reason for me to go to using natural keys.
>
>
> * Artificial keys allow you to enter the exact same statement of fact
> twice. This would simply be nonsense.
> * Artificial keys allows a tuple at t1 and a tuple at t2 to be
> corresponded to each other, even if they don't have a _single_
> attribute from the real world in common. This would also simply be
> nonsense.

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


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

>
> So not one, but three cogent reasons of the top of my head. I wouldn't
> say there are never cases when an artificial key is useful, but they
> certainly shouldn't be hidden, and adding them blindly to every
> relation is surely just a bit silly. Regards, J.

Did I imply that that's what I do?

James A. Fortune
MPAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

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.
>>
>> Marshall
>
> Personally, I don't take the natural keys out either, so they can still
> be used for the deletion. The thought of giving the SQL data an address
> and following a programmer's mental model did not enter into my thinking
> at all. I am not trying to give the data an order either. You've been
> listening to Celko too much. Because of his overall manner, which I
> find quite offensive, I don't even want to listen to him when he's right
> :-).

So, you object to Celko's style but consider him right?!? That's a new
one. ::rolls eyes::


Is the AutoNumber primary key a denormalization of the schema?
> Yes.

You are an ignoramus. You don't even have a clue what normalization is.
The addition of an attribute to act as a simple, stable key does not
affect the normal form in any way shape or manner.


Is it added for a reason? Yes again. I'm still waiting for a
> cogent reason for me to go to using natural keys.

It's the familiarity, stupid. The design criteria for keys are (and I
repeat): uniqueness, irreducibility, stability, simplicity and
familiarity (in no particular order.)

Re: Separate PK in Jxn Tbl?

"JOG" wrote in message
news:274f1803-9fe8-4801-af35-e9a8c99cf823@i72g2000hsd.google groups.com...
> On Jan 27, 2:09 am, "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.
>>
>> > Marshall
>>
>> Personally, I don't take the natural keys out either, so they can still
>> be used for the deletion. The thought of giving the SQL data an address
>> and following a programmer's mental model did not enter into my thinking
>> at all. I am not trying to give the data an order either. You've been
>> listening to Celko too much. Because of his overall manner, which I
>> find quite offensive, I don't even want to listen to him when he's right
>> :-). Is the AutoNumber primary key a denormalization of the schema?
>> Yes. Is it added for a reason? Yes again. I'm still waiting for a
>> cogent reason for me to go to using natural keys.
>
> * Artificial keys allow you to enter the exact same statement of fact
> twice. This would simply be nonsense.

Not exactly. Artificial key values are simply names assigned to individuals
in the Universe of Discourse. I would think that it should be possible to
have many different names for the same thing: considering the fact that
there are a great many different languages, there must therefore be a great
many words for each thing.

> * Artificial keys allows a tuple at t1 and a tuple at t2 to be
> corresponded to each other, even if they don't have a _single_
> attribute from the real world in common. This would also simply be
> nonsense.

I wouldn't call it nonsense. It is not necessary that every property that
an individual exemplifies be represented in the database--only those
properties that are relevant to the problem at hand need be included. In
that event, if a particular individual is assigned a name at t1, and then if
the values for all of the properties that are relevant to the problem at
hand at t1 are compared to those from the individual with the same name at
t2, it is possible for all of those properties to be different. That isn't
nonsense, it just is, given the inherent incompleteness of the information
in 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.
>

You left one out. In a table that has multiple natural keys, when an
artificial key is added, which key values are its values surrogates for?

> So not one, but three cogent reasons of the top of my head. I wouldn't
> say there are never cases when an artificial key is useful, but they
> certainly shouldn't be hidden, and adding them blindly to every
> relation is surely just a bit silly. Regards, J.
>
>>
>> James A. Fortune
>> MPAPos...@FortuneJames.com
>

Re: Separate PK in Jxn Tbl?

"James A. Fortune" wrote in message
news:%23xDvuHJYIHA.5472@TK2MSFTNGP06.phx.gbl...
> JOG wrote:
>> On Jan 27, 2:09 am, "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.
>>>
>>>>Marshall
>>>
>>>Personally, I don't take the natural keys out either, so they can still
>>>be used for the deletion. The thought of giving the SQL data an address
>>>and following a programmer's mental model did not enter into my thinking
>>>at all. I am not trying to give the data an order either. You've been
>>>listening to Celko too much. Because of his overall manner, which I
>>>find quite offensive, I don't even want to listen to him when he's right
>>>:-). Is the AutoNumber primary key a denormalization of the schema?
>>>Yes. Is it added for a reason? Yes again. I'm still waiting for a
>>>cogent reason for me to go to using natural keys.
>>
>>
>> * Artificial keys allow you to enter the exact same statement of fact
>> twice. This would simply be nonsense.
>> * Artificial keys allows a tuple at t1 and a tuple at t2 to be
>> corresponded to each other, even if they don't have a _single_
>> attribute from the real world in common. This would also simply be
>> nonsense.
>
> 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.

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

>>
>> So not one, but three cogent reasons of the top of my head. I wouldn't
>> say there are never cases when an artificial key is useful, but they
>> certainly shouldn't be hidden, and adding them blindly to every
>> relation is surely just a bit silly. Regards, J.
>
> Did I imply that that's what I do?
>
> James A. Fortune
> MPAPoster@FortuneJames.com
>
>

Re: Separate PK in Jxn Tbl?

On Jan 27, 12:39=A0am, "Brian Selzer" wrote:
> "James A. Fortune" wrote in messagenews:%

> > Access programmers use forms to interact with the data. =A0If I follow
> > Jamie's advice and constrain the data at both the table level and in cod=
e,
> > then your points make more sense. =A0Right now, they're just arguments f=
or
> > 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. =A0Checks 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. =A0It 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 :-).

> >> * 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. =A0The child table can contain the AutoNu=
mber
> > 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. =A0Maybe=
an
> > example would help me understand what you mean.
>
> An extra join may be needed if the natural key from the parent table is us=
ed
> in a restrict clause. =A0If all you have is the artificial key from the pa=
rent
> 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 appea=
r
> in the child table, so there isn't any need to join. =A0Bottom 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...

James A. Fortune
CDMAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

wrote in message
news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.com...
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.

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:%23zkseSHYIHA.4808@TK2MSFTNGP05.phx.gbl...
> Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
> interesting argument and one that come back often;

Really? I can put my hand on my heart and say I've never seen anyone
suggest it when they cross-post to comp.databases.theory. I don't doubt it
has come up, but I do doubt it is often.

> however it's not a silver buller.

I implicitly allowed that it is not a silver bullet by actually suggesting
the kinds of reasons one might exclude it.

> First of all, it's another level of complexity that you must add to the
> design of your database; ie, you must make sure that they are all there
> and no one is missing.

Of course one must make sure "they are all there"; you are absolutely right.
But it absurd to suggest doing that is "another level of complexity". It is
trivial to do it, and trivial to check that you've done it by querying the
DB catalogs.

> Second, this DRI cannot be used with cyclic relationship with SQL-Server
> but with Oracle, you can. (From your example, I believe that you are
> working with Oracle).

I'm not. But the fact that you are distinguishing the behaviour of
particular products gets close to the real problem. The real problem is
that the products we use are all more or less defective, but instead of
clamouring to have them fixed (by establishing suitable standards and
following them) we promote workarounds as if they are actually desirable. I
have no problem at all with people describing workarounds for defects but I
have a major problem when it is implied that the workaround is some kind of
best-practice or even desirable.

> On SQL-Server, you must use triggers to implement such a feature when
> there is a cyclic relationship. Of course, when you are dealing with tens
> and hundreds of relationships, this can quickly translate into a
> nightmare. There is also the qestion of the diminution of performance and
> of general design: when you have to update multiples records on multiple
> tables for what should be the change of a single value in a single table
> make it hard to believe that this is a proper normalized database design

First of all, updates to keys should be very rare. A fundamental property
of a well-chosen key is that it is reasonably stable. Imposing the little
self-discipline required to make sure you choose stable keys is
inconsequential when compared to the overall database design effort.

Secondly, even having to update hundreds of tables to amend a key is only
about the same effort required to insert all those rows in the first place.
Against the background of work the system does all the time, that will be
inconsequential. (Of course, if you unwisely choose a key that is not
stable, your argument would be more nearly correct. But that is why the
long-standing advice has been to avoid keys that are not stable.)

> and this situation quickly worsen if you have to take into account the
> correspondance with backups, reports and linked databases; all systems for
> which there is no automatic DRI.

I don't entirely agree with all these reasons, but as I said in my earlier
post, there often *are* good reasons why one might not be able to use ON
UPDATE CASCADE in a particular product and I will take your word for it that
these reasons apply with the product you use. My challenge to you was to
signal you know that, and you have now done so.

> But why make it simpler when you can make it harder?

Hm.

> Finally, I don't understand your example at all. You are introducing us
> to the NATURAL JOIN and USING statement that have been introduced by
> Oracle in its 9i version (also in MySQL and Postgres, I believe) but I
> fail to see what this has to do with the subject of this thread; the use
> of a separate PK in a junction table and its highly related topic, ie. the
> use of natural keys versus the use of surrogate keys. There is no
> relationship at all between a NATURAL JOIN and a natural key and the
> Natural Join can be used as easily with a surrogate key than with a
> natural key.

I am confused about your argument here. I was giving counter-example to
disprove the claim that composite keys make the SQL code more complex, which
was being presented as an argument to introduce yet more, spurious,
synthetic/surrogate keys. In fact if you read my example carefully, you
will have seen that I talked explicitly about *three* synthetic keys because
I aware I was already using two (order number and item number).

> The only thing that is important with the Natural Join is the name of the
> key. (BTW, if you were to ask me what I'm thinking about this little
> monstruosity, I would tell you that this is a perfect example of a Pandora
> box.).

Well, I have to admit that I'm not over-fond of relying on names to imply
that two columns represent the same thing, so in fact I never use that
syntax. I do prefer to assert all the conditions on all the key columns
explicitly and I just don't notice the few extra keystrokes when it's a
composite key. But on the other hand, I find it monstrous when I see two or
more distinct names for colunms that do represent the same thing.

> And finally, a for your request of asking me to convince you that I'm
> properly understand the problem here of to etablish that I'm credible: I
> can tell you that I have absolutely no intention of doing it

And yet you have greatly increased your credibility with this post. I still
disagree with what you've said, but I can see you know more about what
you're talking about than it seemed before. Before, I thought you were
ignorant and uncurious. Now I see you are merely wrong. :-)

> and that I have absolutely no interest at all about what you are thinking
> of me.

That's very healthy. You shouldn't.

>The only things that are of interest to me are the arguments that I'm
>seeing posted here - whatever the people who might write them - but for
>someone who has just make a confusion between a natural key and the NATURAL
>JOIN, asking for such a thing make it looks very strange.

I make no such confusion, and a quick glance at my earlier post will confirm
it.

Roy

Re: Separate PK in Jxn Tbl?

Roy Hann wrote:
> "Sylvain Lafontaine"
> wrote in message news:%23zkseSHYIHA.4808@TK2MSFTNGP05.phx.gbl...

[snip]

>>And finally, a for your request of asking me to convince you that I'm
>>properly understand the problem here of to etablish that I'm credible: I
>>can tell you that I have absolutely no intention of doing it
>
> And yet you have greatly increased your credibility with this post.

I disagree. You give him too much credit.


I still
> disagree with what you've said, but I can see you know more about what
> you're talking about than it seemed before. Before, I thought you were
> ignorant and uncurious. Now I see you are merely wrong. :-)

I suspect that is wishful thinking on your part.

Re: Separate PK in Jxn Tbl?

"Marshall" wrote in message
news:4fad9541-6eda-4ceb-b05b-ce564d0abaa0@z17g2000hsg.google groups.com...


> an attempt to make SQL data have an address

Exactly. Literally hundreds of attempts to sell snake oil in c.d.t. can be
reduced to precisely this statement. We've all said variations of the
above, but I've never seen it put so succintly.

As far as the MS Access newsgroups that this discussion is posted to, I
can't speak to how well your summary extends to their mental model. But
many of them seem to write as if
contents as determined by address were the fundamental paradigm of data.

Re: Separate PK in Jxn Tbl?

"James A. Fortune" wrote in message
news:%23y9kCoIYIHA.5164@TK2MSFTNGP03.phx.gbl...

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

There are really two issues being discussed in a single discussion here.

The issue of synthetic keys versus natural keys is one issue. The issue of
a composite PK in a junction table, made up of FKs, versus a new simple key
is a separable issue.

Please note that, if the two FKs under discussion both reference synthetic
PKs, all of your arguments concerning the problems of dealing with natural
keys become moot.

If we have three tables, Students, Courses, and Enrollments, where
enrollments is a junction between Students and Courses, we could have a
synthetic key, StudentID for students, and a synthetic key, CourseID, for
Courses.

The question then remains which is simpler. To define enrollments with a
composite key
(StudentID, CourseID), or to define a new synthetic key, EnrollmentID.
Neither of these two solutions uses natural keys.

I prefer to se natural keys whenever possible, but I use synthetic keys
when natural ones just won't do. When do natural keys fail to do the job?
When the poeple who control them are mismanaging them.

Re: Separate PK in Jxn Tbl?

"Brian Selzer" wrote in message
news:MOUmj.1465$uE.920@newssvr22.news.prodigy.net...

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

The idea of keeping garbage out of the database takes on an entirely
different meaning if you are dealing with hundreds of programs written in
COBOL, Java, or anything in between accessing a single Oracle database on
the one hand. On the other hand, if you are a developer creating a self
contained MS Access database cum application (tables, queries, forms,
reports, modules, etc.) all in one file, the same issues arise, but they
are resolved quite differently.

I'm not saying either one is "right" or "wrong". I'm just suggesting why an
objection that makes perfect sense to you and me might be lost on the MS
Access community.

Re: Separate PK in Jxn Tbl?

David Cressey wrote:
> The idea of keeping garbage out of the database takes on an entirely
> different meaning if you are dealing with hundreds of programs
> written in COBOL, Java, or anything in between accessing a single
> Oracle database on the one hand. On the other hand, if you are a
> developer creating a self contained MS Access database cum
> application (tables, queries, forms, reports, modules, etc.) all in
> one file, the same issues arise, but they are resolved quite
> differently.
>
> I'm not saying either one is "right" or "wrong". I'm just suggesting
> why an objection that makes perfect sense to you and me might be lost
> on the MS Access community.
^
some of

I think if a thorough poll was done it would show that the majority of
professional Access developers (those that make their living at it) would agree
that data integrity rules should be enforced by the database engine wherever
that is possible.

The fact is that Access is a tool predominantly for *users*, not developers, and
Microsoft appears determined with each subsequent version to make that more the
case. The majority of changes make it easier to do things incorrectly because
that makes the program easier to use for people who have no idea what they are
doing. Since that group vastly outnumbers the other one can hardly argue with
their logic from a business standpoint.

--
Rick Brandt, Microsoft Access MVP
Email (as appropriate) to...
RBrandt at Hunter dot com

Re: Separate PK in Jxn Tbl?

On Jan 27, 2:18 pm, "Rick Brandt" wrote:
> David Cressey wrote:
> > The idea of keeping garbage out of the database takes on an entirely
> > different meaning if you are dealing with hundreds of programs
> > written in COBOL, Java, or anything in between accessing a single
> > Oracle database on the one hand. On the other hand, if you are a
> > developer creating a self contained MS Access database cum
> > application (tables, queries, forms, reports, modules, etc.) all in
> > one file, the same issues arise, but they are resolved quite
> > differently.
>
> > I'm not saying either one is "right" or "wrong". I'm just suggesting
> > why an objection that makes perfect sense to you and me might be lost
> > on the MS Access community.
>
> ^
> some of
>
> I think if a thorough poll was done it would show that the majority of
> professional Access developers (those that make their living at it) would agree
> that data integrity rules should be enforced by the database engine wherever
> that is possible.

Well thank goodness for that! For a scary moment I though Sylvian's
views were representative of the access community as a whole, and that
you guys didn't think that data integrity should be enforced primarily
by the db engine.

>
> The fact is that Access is a tool predominantly for *users*, not developers, and
> Microsoft appears determined with each subsequent version to make that more the
> case. The majority of changes make it easier to do things incorrectly because
> that makes the program easier to use for people who have no idea what they are
> doing. Since that group vastly outnumbers the other one can hardly argue with
> their logic from a business standpoint.

My fear though is that many db developers cut their teeth using
Access. If bad practices are encouraged just because access doesn't
handle many concurent users, and tends to manage data where it's
unlikely one will hit the pitfalls that artificial keys can lay, when
developers graduate up to larger server systems they may well carry
those mistakes on with them.

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

>
> --
> Rick Brandt, Microsoft Access MVP
> Email (as appropriate) to...
> RBrandt at Hunter dot com

Re: Separate PK in Jxn Tbl?

On Jan 27, 3:06 am, "James A. Fortune"
wrote:
> JOG wrote:
> > On Jan 27, 2:09 am, "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.
>
> >>>Marshall
>
> >>Personally, I don't take the natural keys out either, so they can still
> >>be used for the deletion. The thought of giving the SQL data an address
> >>and following a programmer's mental model did not enter into my thinking
> >>at all. I am not trying to give the data an order either. You've been
> >>listening to Celko too much. Because of his overall manner, which I
> >>find quite offensive, I don't even want to listen to him when he's right
> >>:-). Is the AutoNumber primary key a denormalization of the schema?
> >>Yes. Is it added for a reason? Yes again. I'm still waiting for a
> >>cogent reason for me to go to using natural keys.
>
> > * Artificial keys allow you to enter the exact same statement of fact
> > twice. This would simply be nonsense.
> > * Artificial keys allows a tuple at t1 and a tuple at t2 to be
> > corresponded to each other, even if they don't have a _single_
> > attribute from the real world in common. This would also simply be
> > nonsense.
>
> 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 :-).

I'd suggest you read Rick's comments James. To be honest, its database
101 really that shared databases should handle data integrity
centrally. But I'm glad you see the logic of not allowing duplicates,
etc
(I've encountered people who don't...)

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

No probs, although off the top of my head its gonna be a bit
contrived. With an artificial key:

Marriages {id, husband, wife, date}
Kids_from_Marriage {from_id, name, birth}

A query that asks "fetch me all the children whose mother is x"
obviously requires an equijoin, matching Marriages.id and
Kids.from_id. However with the original natural keys:

Marriages {id, husband, wife, date}
Kids_from_Marriage {mother, father, name, birth}

The same query is a simple select. That certainly seems a lot less
complicated to me ;)

>
>
> > So not one, but three cogent reasons of the top of my head. I wouldn't
> > say there are never cases when an artificial key is useful, but they
> > certainly shouldn't be hidden, and adding them blindly to every
> > relation is surely just a bit silly. Regards, J.
>
> Did I imply that that's what I do?

Erm, thats the impression you seem to give. Tony Toes certainly stated
he encouraged blindly adding artificial keys to everything. However
with duplicates, the extra reason's that Brian has given, the above
examples and the recommendations from access pro's that you shouldn't
rely on code to maintain integrity, I hopefully can assume that you've
at least seen the strength of the arguments.

>
> James A. Fortune
> MPAPos...@FortuneJames.com

Re: Separate PK in Jxn Tbl?

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?) 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

Re: Separate PK in Jxn Tbl?

On Jan 26, 10:01 pm, CDMAPos...@fortunejames.com wrote:
>
> 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?

Users aren't the only ones who update tables.


Marshall

Re: Separate PK in Jxn Tbl?

On Jan 26, 11:13 pm, "Brian Selzer" wrote:
>
> 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.

Right. And then you need to change a constraint, and you modify the
code and you remember to update three out of the four forms that
implement the check, and you get garbage in the database. Or
any of a number of other things that can go wrong.


Marshall

Re: Separate PK in Jxn Tbl?

On Jan 27, 5:53 am, "David Cressey" wrote:
> "Brian Selzer" wrote in message
>
> > 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.
>
> The idea of keeping garbage out of the database takes on an entirely
> different meaning if you are dealing with hundreds of programs written in
> COBOL, Java, or anything in between accessing a single Oracle database on
> the one hand. On the other hand, if you are a developer creating a self
> contained MS Access database cum application (tables, queries, forms,
> reports, modules, etc.) all in one file, the same issues arise, but they
> are resolved quite differently.
>
> I'm not saying either one is "right" or "wrong". I'm just suggesting why an
> objection that makes perfect sense to you and me might be lost on the MS
> Access community.

There's another approach to enforcing constraints: just tell your
users not to do that. In the hierarchy of what works, it comes
below checks in client code, but above manually inserting
garbage at design-time. It also has the advantage of being
even lower performance overhead for the software. Note
that centrally enforced declarative integrity constraints
are an endpoint in this hierarchy.


Marshall

Re: Separate PK in Jxn Tbl?

"Marshall" wrote in message
news:24e63bab-1720-4762-87a2-e1bcc76c130b@y5g2000hsf.googleg roups.com...
> 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?) 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!
>

I think you're jumping the gun, Marshall. Since a surrogate key value is
just a name, what a projection over the s.k. column would leave you with is
a set of names, and the only meaning that can be derived from each element
is that there is an individual that has been so named and due to domain
closure that that individual actually exists.

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

Re: Separate PK in Jxn Tbl?

>> Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
>> interesting argument and one that come back often;
>
> Really? I can put my hand on my heart and say I've never seen anyone
> suggest it when they cross-post to comp.databases.theory. I don't doubt
> it has come up, but I do doubt it is often.

Oh, the word "often" is all relative here. Of course, in a newsgroup about
Access and/or SQL-Server, you won't see it coming very often because these
two databases don't offer support for DRI updating on cyclic relationships.
What you are more likely to see would be people complaining about the
absence of such support in SQL-Server. However, I suppose that many of
those peoples who are making this complaint have probably the use of natural
keys behind their mind (the other strong possibility beeing the use of some
form of replication); so this close the circle. I've also suppose that on
other newsgroups dedicated to systems like Oracle, the discussion about this
point is probably much more heated.

I should also add that if I remember correctly, this one was a feature that
has been promised to be released with SQL-Server 2000 but that it didn't
make the last cut and a lot of people were angry about that. Nowadays, in
regard to the next release of SQL-Server 2008, peoples seem to be more
interested with features such as the integration with .NET and other big
stuff like that than to know if Katmai will offer support for this or not.

> First of all, updates to keys should be very rare. A fundamental property
> of a well-chosen key is that it is reasonably stable. Imposing the little
> self-discipline required to make sure you choose stable keys is
> inconsequential when compared to the overall database design effort.

This is the big problem with natural keys. When the theory was first
elaborated, the point was that a natural key never change its value. That
was the point that was making the choice of a natural key on par with the
use of a surrogate key as the primary key of a table. However, like anyone
have discovered with experience, a natural key can change its value under a
set of various circonstances. One could argue that if a key can change its
value, than it's not a natural key but as you know, this argument bring
nothing in regard to help you choosing a natural key.

Now, how it's important the fact that the update to a key should be very
rare? To me, there is a big difference between 0 and 1 time but there is
not between 1 and 1 million times. If you have to put code in order to take
into account the fact that the key can change its value, it should be the
same code if the key change its value one single time for the life of the
database or multiple times each day. In many cases, you are even placed in
the situation where there is a strong possibility that the key will never
change its value a single time for the whole lifetime of the database but
that you must take into account the possibility that it might do so.

Of course, there are many possible solutions: put a lot of DRI/triggers and
other pieces of code; forbid any change (and make the clients unhappy on
many occasions); suggest to delete the records and recreate them from
scratch or even rebuild the whole database; etc.; etc. but why bother with
all these in the first place?

To me, the use of natural keys is like someone bringing me a box full of
Damocles' swords: he would usually tell me to be very careful when I will
put these on the ceiling. Later, when he will come back and see the empty
ceiling and ask me why, I would answer him that I've put the box in the
garbage bin but if he want them, he can bring the box home for free; at the
condition that I'll never see them again. The fact that some theorists like
these swords too is of no interest to me. If they like them, they too can
bring them home for free but at the same condition, that I'll never them
again.

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


"Roy Hann" wrote in message
news:HKqdneWeJ7ZO7QHanZ2dneKdnZydnZ2d@pipex.net...
> "Sylvain Lafontaine"
> wrote in message news:%23zkseSHYIHA.4808@TK2MSFTNGP05.phx.gbl...
>> Your argument about the use of a DRI WITH ON UPDATE CASCADE is an
>> interesting argument and one that come back often;
>
> Really? I can put my hand on my heart and say I've never seen anyone
> suggest it when they cross-post to comp.databases.theory. I don't doubt
> it has come up, but I do doubt it is often.
>
>> however it's not a silver buller.
>
> I implicitly allowed that it is not a silver bullet by actually suggesting
> the kinds of reasons one might exclude it.
>
>> First of all, it's another level of complexity that you must add to the
>> design of your database; ie, you must make sure that they are all there
>> and no one is missing.
>
> Of course one must make sure "they are all there"; you are absolutely
> right. But it absurd to suggest doing that is "another level of
> complexity". It is trivial to do it, and trivial to check that you've
> done it by querying the DB catalogs.
>
>> Second, this DRI cannot be used with cyclic relationship with SQL-Server
>> but with Oracle, you can. (From your example, I believe that you are
>> working with Oracle).
>
> I'm not. But the fact that you are distinguishing the behaviour of
> particular products gets close to the real problem. The real problem is
> that the products we use are all more or less defective, but instead of
> clamouring to have them fixed (by establishing suitable standards and
> following them) we promote workarounds as if they are actually desirable.
> I have no problem at all with people describing workarounds for defects
> but I have a major problem when it is implied that the workaround is some
> kind of best-practice or even desirable.
>
>> On SQL-Server, you must use triggers to implement such a feature when
>> there is a cyclic relationship. Of course, when you are dealing with tens
>> and hundreds of relationships, this can quickly translate into a
>> nightmare. There is also the qestion of the diminution of performance
>> and of general design: when you have to update multiples records on
>> multiple tables for what should be the change of a single value in a
>> single table make it hard to believe that this is a proper normalized
>> database design
>
> First of all, updates to keys should be very rare. A fundamental property
> of a well-chosen key is that it is reasonably stable. Imposing the little
> self-discipline required to make sure you choose stable keys is
> inconsequential when compared to the overall database design effort.
>
> Secondly, even having to update hundreds of tables to amend a key is only
> about the same effort required to insert all those rows in the first
> place. Against the background of work the system does all the time, that
> will be inconsequential. (Of course, if you unwisely choose a key that is
> not stable, your argument would be more nearly correct. But that is why
> the long-standing advice has been to avoid keys that are not stable.)
>
>> and this situation quickly worsen if you have to take into account the
>> correspondance with backups, reports and linked databases; all systems
>> for which there is no automatic DRI.
>
> I don't entirely agree with all these reasons, but as I said in my earlier
> post, there often *are* good reasons why one might not be able to use ON
> UPDATE CASCADE in a particular product and I will take your word for it
> that these reasons apply with the product you use. My challenge to you
> was to signal you know that, and you have now done so.
>
>> But why make it simpler when you can make it harder?
>
> Hm.
>
>> Finally, I don't understand your example at all. You are introducing us
>> to the NATURAL JOIN and USING statement that have been introduced by
>> Oracle in its 9i version (also in MySQL and Postgres, I believe) but I
>> fail to see what this has to do with the subject of this thread; the use
>> of a separate PK in a junction table and its highly related topic, ie.
>> the use of natural keys versus the use of surrogate keys. There is no
>> relationship at all between a NATURAL JOIN and a natural key and the
>> Natural Join can be used as easily with a surrogate key than with a
>> natural key.
>
> I am confused about your argument here. I was giving counter-example to
> disprove the claim that composite keys make the SQL code more complex,
> which was being presented as an argument to introduce yet more, spurious,
> synthetic/surrogate keys. In fact if you read my example carefully, you
> will have seen that I talked explicitly about *three* synthetic keys
> because I aware I was already using two (order number and item number).
>
>> The only thing that is important with the Natural Join is the name of the
>> key. (BTW, if you were to ask me what I'm thinking about this little
>> monstruosity, I would tell you that this is a perfect example of a
>> Pandora box.).
>
> Well, I have to admit that I'm not over-fond of relying on names to imply
> that two columns represent the same thing, so in fact I never use that
> syntax. I do prefer to assert all the conditions on all the key columns
> explicitly and I just don't notice the few extra keystrokes when it's a
> composite key. But on the other hand, I find it monstrous when I see two
> or more distinct names for colunms that do represent the same thing.
>
>> And finally, a for your request of asking me to convince you that I'm
>> properly understand the problem here of to etablish that I'm credible: I
>> can tell you that I have absolutely no intention of doing it
>
> And yet you have greatly increased your credibility with this post. I
> still disagree with what you've said, but I can see you know more about
> what you're talking about than it seemed before. Before, I thought you
> were ignorant and uncurious. Now I see you are merely wrong. :-)
>
>> and that I have absolutely no interest at all about what you are thinking
>> of me.
>
> That's very healthy. You shouldn't.
>
>>The only things that are of interest to me are the arguments that I'm
>>seeing posted here - whatever the people who might write them - but for
>>someone who has just make a confusion between a natural key and the
>>NATURAL JOIN, asking for such a thing make it looks very strange.
>
> I make no such confusion, and a quick glance at my earlier post will
> confirm it.
>
> Roy
>

Re: Separate PK in Jxn Tbl?

On Jan 22, 1:26=A0pm, "Neil" wrote:
> Whenever I've created junction tables in the past, I always made the PK of=

> the junction table the combined pks from the two other tables. Ex: Table1
> PK=3DA; Table2 PK=3DB; Junction table consists of two fields, A and B, whi=
ch
> together comprise the PK for the junction table.
>
> However, I just came across some code in which the person created a juncti=
on
> table with a separate PK consisting of an autonumber field, and then the t=
wo
> fields.
>
> So I was wondering how others did junction tables -- with a standalone
> autonumber PK, or with a PK consisting of the PKs of the tables being
> joined? And, if a standalone PK, then why?
>
> Thanks!
>
> Neil

I don't claim to know more about db theory than the cdt regulars, or
to have more experience than the many practioners who have contributed
to this thread.

I do know that the debate over relationship representations has been
going on for years, neither side giving any ground. Some aspects of it
are never addressed. For example, making the two foreign keys in a
junction table a composite PRIMARY key prohibits NULL values from
either key, but declaring a separate surrogate primary key together
with a UNIQUE constraint on the two-foreign-key-composite-key does
allow NULL values for either (or both!). This can be useful for
representing unrelated entity tuples, either childless parents or
orphans.

Practioners in the audience may be interested in this page:

http://www.sfdbs.com/toplevel/fasttrack/fasttrack.shtml

of my website in which I describe an entirely new way to represent
relationships. Or this page:

http://www.sfdbs.com/solopages/relcardtypes.shtml

where the representational capabilities of this new representation
(called Aggregate-Link) are compared to the two more traditional
representations.

Please note, I am not suggesting the use of this representation for
conventional database design. Merely consider it new, and possibly
interesting. (It may not be possible to deploy Aggregate-Link in MS
Access.)

The theorists have already had a field day dumping on this new
representation in this thread:

http://groups.google.com/group/comp.databases.theory/browse_ frm/thread/03651=
4365a600841#

But most theorists seem to be immune to the practical considerations
of IT. In particular, they don't begin to understand that IT
employers' appreciation for Access is based on cost, not beauty.

Rob

Re: Separate PK in Jxn Tbl?

CDMAPoster@fortunejames.com wrote in
news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.co
m:

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

What if there's more than one application built on top of the
database?

I, too, agree that one should put as much of the data logic in the
back end as possible.

However, that doesn't mean I use natural keys very often. I'm
definitely opposed to compound keys for any table whose PK will be a
foreign key in another table. It causes myriad problems of all sorts
(been there, done that), and despite its being theoretically
correct, just doesn't work well in practice.

Just consider one scenario:

You need to build criteria for a query-by-form interface. That means
that to query on the PK of a table with a compound PK, you end up
needing to have multiple fields in your WHERE clause. And if you're
querying multiple records in the table with the compound PK, you'll
need a complex nested OR in your WHERE clause.

I know perfectly well that theoretically speaking you're not
supposed to let your application drive the design of your schema,
but this is a case where common sense tells me that following theory
leads to enormously difficult application logic problems.

Natural keys are great for tables with a single-column natural PK.

Otherwise, surrogate keys make building an application substantially
easier.

And, BTW, I would, of course, advocate that any natural key that is
not used as the PK should naturally have a unique index on it.

And any natural key that can't have a unique index (because some
fields need to be Null) was never a candidate for PK in the first
place, and would have to have had uniqueness enforced in the
application in some fashion anyway.

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

Re: Separate PK in Jxn Tbl?

JOG wrote in
news:c4699a61-e76f-4ff3-aa25-310929b6ecc0@e10g2000prf.google groups.co
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.

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

Re: Separate PK in Jxn Tbl?

>> If I were a library lending 2 undiffentiatable copies of 1 book to the same person, I suppose I would need an Autonumber PK plus A BookID and BorrowerID. <<

No, you would fire your librarian for failure to keep an accession
number or copy number as part of the book's identifier. And the book
should be identified by an ISBN.

Re: Separate PK in Jxn Tbl?

JOG wrote in
news:5afa9a80-f1c5-4ede-8095-1f4c0164417a@s12g2000prg.google groups.co
m:

> No probs, although off the top of my head its gonna be a bit
> contrived. With an artificial key:
>
> Marriages {id, husband, wife, date}
> Kids_from_Marriage {from_id, name, birth}
>
> A query that asks "fetch me all the children whose mother is x"
> obviously requires an equijoin, matching Marriages.id and
> Kids.from_id. However with the original natural keys:
>
> Marriages {id, husband, wife, date}
> Kids_from_Marriage {mother, father, name, birth}
>
> The same query is a simple select. That certainly seems a lot less
> complicated to me ;)

Assuming you've got some form of CASCADE UPDATE on your enforced
relationship, that will work.

But it's repeating a huge amount of data, and adding a bunch more
indexes to keep updated. Perhaps these are insignificant issues to
*you* and *your* apps, but my clients' apps (some using Jet, some
using SQL Server, some using MySQL) don't perform so well when you
add in all the overhead.

And it all leaves aside the question of how you know that
husband/wife/date is always going to be unique. I think that on any
given day in the US, there are plenty of marriages in which those
three values will be identical. You could add place. But then, in
large cities, that might not be enough. So use Postal Code in place
of place, and that might do the trick, although in large cities that
might not do it, either.

Given that I can foresee a reasonable possibility of a collision on
this candidate key as currently defined, I'd think long and hard on
whether to use it or not.

And it's one of the main problems any time you're storing data about
people in a data table -- you often lack pieces of the information,
and you run a high risk of collisions between people with the same
names.

And that fact of the real-world entities being modelled makes
finding a natural key that will work as a PK a very hard task.

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

Re: Separate PK in Jxn Tbl?

Marshall wrote in
news:24e63bab-1720-4762-87a2-e1bcc76c130b@y5g2000hsf.googleg roups.com
:

> 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?) 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!

Junction tables that:

1. include nothing but the foreign keys of the tables they are
joining,

AND

2. have no child tables

need not surrogate key.

Since I use surrogate keys in most of my schemas, most junction
tables are two-columns, and those two columns are the PK.

Once I did have a schema where there was a child table to the
junction table, and in that case, I wish I'd had a surrogate key (it
was a pure natural key design, actually, and the worst project I've
ever worked on; no one to blame but myself, as I designed the schema
from scratch), as it made it awfully difficult to work with that
table that was a child of the junction table.

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

Re: Separate PK in Jxn Tbl?

Rob wrote in
news:0a65204e-068c-4e77-900c-319439ab6707@d70g2000hsb.google groups.co
m:

> I do know that the debate over relationship representations has
> been going on for years, neither side giving any ground. Some
> aspects of it are never addressed. For example, making the two
> foreign keys in a junction table a composite PRIMARY key prohibits
> NULL values from either key, but declaring a separate surrogate
> primary key together with a UNIQUE constraint on the
> two-foreign-key-composite-key does allow NULL values for either
> (or both!). This can be useful for representing unrelated entity
> tuples, either childless parents or orphans.

Er, why go to all that trouble? You can represent the same childless
parents by simply omitting a record in the junction table.

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

Re: Separate PK in Jxn Tbl?

>> Whenever I've created junction tables [sic] in the past, I always made the PK of the junction table the combined pks from the two other tables.. <<

I wish I knew where people got the term "junction table" when they
mean a table that models a relationship . I guess if you are still
thinking in terms PK-FK being pointer chains and structure and not
relational, then you would borrow old terminology from Network
Databases. And bring that mindset with you, too.

>> However, I just came across some code in which the person created a junction table with a separate PK consisting of an autonumber field [sic], and then the two fields [sic]. <<

See what I mean about the poisoned mindset? Rows are not records;
fields are not columns; tables are not files; references are
constraints and not pointers.

And you have been doing it right, in spite of having a bad
vocabulary :)!!

The "id"-iot with the magical auto-numbering is still using a
sequential file model in his head. How can an exposed physical
locator, based on the state of one particular machine at insertion
time, with one copy of one particular release of one particular SQL
product be a relational key? Since a key is subset of the attributes
of an entity, that magical number must exist before each row in
inserted into the table and not be subject to insertion time; this is
by definition. Well, it doesn't, so it is crap.

I would try to clean up this schema since it probably has redundant
duplicates and orphaned rows.

Re: Separate PK in Jxn Tbl?

>> Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time to see if there is a different number Ah! <<

In the US, you get a UPC/EAN code and then a batch number with booze.
The closest thing to a serial number is your cash register receipt.
Now if someone will explain to me why we have expiration dates on salt
and bottled water to me because I don't get it.

Re: Separate PK in Jxn Tbl?

>> It has never caused me the least problem. There has been no revelation in this thread that would cause me to even revisit the decision. <<

How would you know, since autonumbering have no validation or
verification? Trust me, I earn a lot of my living cleaning up these
"fake pointer chain" SQL schemas.

ATTRIBUTES AND FIRST NORMAL FORM (1NF):

An attribute has to belong to an entity in the real world being
modeled by the RDBMS. In First Normal Form, the attributes is modeled
as a column in a table. It must also be an atomic value. I am not
going to get into the differences between atomic and scalar, but
scalar implied atomic and not the other way around.

IDENTITY does not exist in an entity in the real world being modeled
by the RDBMS. Thus, it is not an attribute and cannot be in a table,
by definition.

IDENTITY is a result of the physical state of particular piece of
hardware at a particular time as read by the current release of a
particular database product. It is not a data type. You cannot have
more than one column of this "type" in a table. It is not NULL-able,
which all data types have to be in RDBMS (Dr. Codd again). It is not
a numeric; you cannot do math with it.

It is what is called a "tag number" -- basically, a nominal scale
written with numbers instead of letters. Only equality tests make
sense (see the part about inserting sets of rows, which means that
IDENTITY has no natural ordering, not even temporal).

DEFINITION OF KEYS:

(Dr. Codd, RM II, page 23):
It is equally valid to interpret the uniqueness property in terms of
object identification: the value of the primary key in each row of the
pertinent R-table identifies the particular object represented by that
row uniquely with the type of object s that are presented by that
relation,

Create a table with an IDENTITY as the key and perform a series of
insertions of the same object, say an automobile:

INSERT INTO Vehicles (vin, vehicle_mileage, iso_tire_size) VALUES
(..);
INSERT INTO Vehicles (vin, vehicle_mileage, iso_tire_size) VALUES
(..);

I now have two cars with the same VIN number. Actually, I have two
copies of the same car (object) with an IDENTITY pseudo-key instead of
the industry standard VIN as the proper relational key. This is a
called an insertion anomaly.

Assume that this pair of insertions led to creating vehicles 41 and 42
in the table, which are the same object. I can update 42's mileage
without touching 41. I now have two versions of the truth in my
table. This is a called an update anomaly.

Likewise, if I wreak vehicle 41, I still have copy 42 in the motor
pool in spite of the fact that the object no longer exists. This is
deletion anomaly.

Before you say that you can make a key from (IDENTITY, vin), read more
Dr. Codd (ibid):

If the primary key is composite and if one of the columns is dropped
from the primary key, the first property [uniqueness] is no longer
guaranteed.

Assume that I have correct VINs and use (IDENTITY, vin) as a key.
Dropping the pair clearly does not work -- a lot of vehicles could
have the same mileage and tire sizes, so I do not have unique rows
guaranteed. Dropping IDENTITY will leave me with a proper key that
can be validated, verified and repeated.

Dropping the VIN does not leave me with a guarantee (i.e.
repeatability and predictability). If I run this code:

BEGIN ATOMIC
DELETE FROM Vehicles
WHERE id = 41;
INSERT INTO Vehicles (vehicle_mileage, iso_tire_size)
VALUES (<> );
END;

The relational algebra says that I should have in effect done
nothing. I have dropped and re-inserted the same object - an EXCEPT
and UNION operation that cancel. But since IDENTITY is physical and
not logical, this does not work.

If I insert the same vehicle (object) into another table, the system
will not guarantee me that I get the same IDENTITY as the relational
key in the other table. The VIN would be guaranteed.

The guarantee requirement gets worse. SQL is a set-oriented language
and allows me to write things like this.

INSERT INTO Vehicles (vin, vehicle_emileage, iso_tire_size)
SELECT vin, vehicle__mileage, iso_tire_size
FROM NewPurchases;

Since a query result is a table, and a table is a set which has no
ordering, what should the IDENTITY numbers be? The entire, whole,
completed set is presented to Vehicles all at once, not a row at a
time. There are (n!) ways to number (n) rows. Which one did you
pick? Why? The answer in SQL products has been to use whatever the
*physical* order of the physical table happened to be. That non-
relational phrase "physical order" again!

But it is actually worse than that. If the same query is executed
again, but with new statistics or after an index has been dropped or
added, the new execution plan could bring the result set back in a
different physical order.

Can you explain from a logical model why the same rows in the second
query get different IDENTITY numbers? In the relational model, they
should be treated the same if all the values of all the attributes are
identical and each row models the same object as it did before.

Yes, I can write a lot of procedural code and triggers, thus violating
all Codd's rules about high-level data manipulation and integrity
being handled by the RDBMS. But that is a kludge and good SQL
programmers know it.

THE KINDS OF KEYS:

Now for a little more practice than theory. Here is my classification
of types of keys. It is based on common usage

1) A natural key is a subset of attributes which occur in a table and
act as a unique identifier. They are seen by the user. You can go to
the external reality and verify them. You would also like to have
some validation rule. Example: UPC codes on consumer goods (read the
package barcode) and validate them with a check digit or a
manufacturer's website or a tool (geographical co-ordinates validate
with a GPS).

These should be either industry standards or natural phenomena.

2) An artificial key is an extra attribute added to the table which is
seen by the user. It does not exist in the external reality, but can
be verified for syntax or check digits inside itself. It is up to the
DBA to maintain a trusted source for them. Example: the open codes in
the UPC scheme which a user can assign to his own stuff. The check
digits still work, but you have to verify them inside your own
enterprise.

If you have to construct a key yourself, it takes time to design them,
to invent a validation rule, set up audit trails, etc. Yes, doing
things right takes time and work. Not like just popping an IDENTITY
on every table in the schema, is it?

3) An "exposed physical locator" is not based on attributes in the
data model, but in the physical storage and is exposed to user. There
is no reasonable way to predict it or verify it, since it usually
comes from the physical state of the hardware at the time of data
insertion. The system obtains a value thru some physical process in
the hardware totally unrelated to the logical data model.

Just because IDENTITY does not hold a track/sector address (like
Oracle's row_id) does not make a logical key. A hash points to a
table with the address. An index (the mechanism in IDENTITY) resolves
to the target address via pointer chains. If you re-hash, or re-
index, the physical locator has to resolve to the new physical
location.

Technically, these IDENTITY values are not really keys at all, since
they are derived from the PHYSICAL storage and are not even attributes
in the LOGICAL data model.

Physical locators include assorted flavors of indexes (Ingres uses
over a dozen kinds), pointer chains (Sybase SQL Anywhere's key joins),
row numbers (Oracle), hash tables (Teradata), bit vectors (Rushmore
and Foxpro), non-contiguous storage (SAND, the whole database is
reduced to a multi-dimensional vector space), inverted list (Model
204), etc. Any device that you can use to locate a particular row in
storage without a sequential search of the storage qualifies.

In the old days, we had to explicitly create, use and update indexes.
They were very exposed and one of the classic errors was not to re-
index a file. For Oracle, the classic was storing a row_id in code or
a file, and then doing a storage re-organization. All of the exposed
locators were subject to some characteristic screw up where logical
and physical models met. Thus, we had utilities for cleaning up
broken pointer chains, re-indexing files, etc. in those early days.

One of the major advances in SQL was putting this responsibility on
the system and not the programmers. The bad news is that if someone
uses exposed locators in an SQL product, there are not a lot of
utilities for cleaning up the mess.

** Notice that people get "exposed physical locator" and "surrogate"
mixed up; they are totally different concepts. **

WHAT IS A SURROGATE KEY?

A quote from Dr. Codd: "..Database users may cause the system to
generate or delete a surrogate, but they have no control over its
value, nor is its value ever displayed to them ..."(Dr. Codd in ACM
TODS, pp 409-410) and Codd, E. (1979), Extending the database
relational model to capture more meaning. ACM Transactions on
Database Systems, 4(4). pp. 397-434.

This means that a surrogate ought to act like an index; created by the
user, managed by the system and NEVER seen by a user. That means
never used in queries, DRI or anything else that a user does.

Codd also wrote the following:

"There are three difficulties in employing user-controlled keys as
permanent surrogates for entities.

(1) The actual values of user-controlled keys are determined by users
and must therefore be subject to change by them (e.g. if two
companies merge, the two employee databases might be combined with the
result that some or all of the serial numbers might be changed.).

(2) Two relations may have user-controlled keys defined on distinct
domains (e.g. one uses social security, while the other uses employee
serial numbers) and yet the entities denoted are the same.

(3) It may be necessary to carry information about an entity either
before it has been assigned a user-controlled key value or after it
has ceased to have one (e.g. and applicant for a job and a retiree).

These difficulties have the important consequence that an equi-join on
common key values may not yield the same result as a join on common
entities. A solution - proposed in part [4] and more fully in [14] -
is to introduce entity domains which contain system-assigned
surrogates.

[emphasis begin] Database users may cause the system to generate or
delete a surrogate, but they have no control over its value, nor is
its value ever displayed to them....." (Codd in ACM TODS, pp 409-410)
[emphasis end].

That means if IDENTITY were a surrogate, we would not see its values,
print them out as invoice numbers and so forth. That means if
IDENTITY were a surrogate, we could drop it and the schema would still
work. Like an index. But IDENTITY fails on both those points.

PRACTICAL STUFF

This is a summary of points already made, but put in one place. The
summary is "Good bye, data integrity" in 25 words or less.

IDENTITY does not port. This is such a basic part of Software
Engineering; I am not going to comment on it. If anyone thinks that
they will always deal only with their own code on one machine with one
release of one product is not planning a long career.

IDENTITY leaves gaps. This is because it is not a surrogate and does
not verify its value against a relational key. It is a software
implementation of a counter, like we had on some of the old tape
drives. The practical problems involve having to explain the gaps to
the SOX guy in accounting.

IDENTITY has no validation. One tag number is as good as another.

IDENTITY has no verification rules. Looking at an object, you cannot
tell what IDENTITY value will be assigned to it. Is 41 or 42 the id
of this automobile? Prove it.

IDENTITY has no repeatability. The value depends on the arrival time
in the physical storage.

David Cressey advanced the theory that this all started out with
Access 97. If you build a database with one of the database wizards,
this is usually what you get.

Also, if you design your own database and create your own tables,
there comes a time, for each table, where MS Access intervenes, and
tells you that you have not assigned a primary key, an that one is
recommended, and that Access will do it for you, if that's ok.

If you say "yes" (who would not?) what you get is precisely this: an
extra numeric column, auto assigned, and declared as the key of the
new table.

Actually, I can live with this, for so called "entity tables". It
would be better for the newbie DBA to discover a natural key, and
declare that, but it's not so bad. For "relationship tables", the
primary key ought to be a compound key made up of two or more foreign
keys. But that is not what Access does.

Someone who has learned database design before building their first
Access database would not do this. But Access makes it seductively
simple to design and build your own database, without even a minimal
understanding of the consequences of your decisions.

Re: Separate PK in Jxn Tbl?

Marshall wrote:
> On Jan 26, 11:13 pm, "Brian Selzer" wrote:
>> 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.
>
> Right. And then you need to change a constraint, and you modify the
> code and you remember to update three out of the four forms that
> implement the check, and you get garbage in the database. Or
> any of a number of other things that can go wrong.

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.

Re: Separate PK in Jxn Tbl?

>> 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? 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!

>> It's also slightly easier to delete the record [sic: rows are not records!] in code. <<

How do you know that you got the right entity? Don't you have a text
editor if a little extra typing is that serious a problem that you
need to add errors and overhead to your SQL?

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

I hope that you are not designing forms and reports in the database.
That is a job for the front. Now, in the old days, when COBOL, et al
had a file system, we had to do it this -- monolithic architecture vs
tiered architecture.

>> The theorists will argue. I don't care. <<

Would you say the same thing to an accountant who wanted the books to
balance :)??

Re: Separate PK in Jxn Tbl?

>> I never seek to add columns where an attribute does not exist in the reality being modeled; sometimes I do end up adding something 'artificial' but only when there is a "good data modeling" reason for doing so. <<

May I add to that? When you design -- yes, actually design and not
blindly throw on the first thing at hand -- an artificial key, you
also have to create and document the validation and verification rules
for it.

I am a fan of check digits if this data element has to come in thru
application code front ends. When we get SIMILAR TO in more SQL
products, I will do more with regular expressions.

Re: Separate PK in Jxn Tbl?

On Jan 27, 12:39 pm, "David W. Fenton"
wrote:
> JOG wrote innews:5afa9a80-f1c5-4ede-8095-1f4c0164417a@s12g2000prg.goog legroups.co
> m:
>
> > No probs, although off the top of my head its gonna be a bit
> > contrived. With an artificial key:
>
> > Marriages {id, husband, wife, date}
> > Kids_from_Marriage {from_id, name, birth}
>
> > A query that asks "fetch me all the children whose mother is x"
> > obviously requires an equijoin, matching Marriages.id and
> > Kids.from_id. However with the original natural keys:
>
> > Marriages {id, husband, wife, date}
> > Kids_from_Marriage {mother, father, name, birth}
>
> > The same query is a simple select. That certainly seems a lot less
> > complicated to me ;)
> [...]
> And it all leaves aside the question of how you know that
> husband/wife/date is always going to be unique. I think that on any
> given day in the US, there are plenty of marriages in which those
> three values will be identical. You could add place. But then, in
> large cities, that might not be enough. So use Postal Code in place
> of place, and that might do the trick, although in large cities that
> might not do it, either.

Huh? You think there are some pairs of people who are getting married
more than once on the same day? I mean, even in Hollywood the
crazy "marriages" usually last at least a few *days*. I'm not sure
it's
even technically possible to get married, divorced, and remarried to
the same person in one day. (Although I am no expert in the
*bureaucratic* details of marriage.)


Marshall

Re: Separate PK in Jxn Tbl?

>> .. use of a DRI WITH ON UPDATE CASCADE .. it's another level of complexity that you must add to the design of your database; i.e., you must make sure that they are all there and no one is missing. <<

If the business rule was there, then you have to enforce it --
assuming that you want data integrity. The question is HOW to enforce
it, not IF you are going to. DRI actions are declarative and
deterministic; application code and triggers are procedural,
proprietary and possible non-deterministic. Oh, did I mention that
the optimizer can use DRI actions, but not application code and
triggers? That only have to write the DRI once and not in every
application, present and future? And that I have some graph theory to
validate the chain of actions I set up, rather than doing code
testing?

All that would seem to make DRI both less complex and more practical
than the other possible methods.

>> Second, this DRI cannot be used with cyclic relationship with SQL-Server but with Oracle, you can. (From your example, I believe that you are working with Oracle). On SQL-Server, you must use triggers to implement such a feature when there is a cyclic relationship. <<

Yes, SQL Server is weaker in some features. How does that invalidate
the approach? And you can do some of this with really ugly updatable
VIEWs that use the WITH CHECK OPTION. Triggers can look good after
that.

Re: Separate PK in Jxn Tbl?

« But many of them seem to write as if contents as determined by address ».

The content is not determined by the address and in fact, for those who are
using surrogate keys, the exact value of an address inside the database has
zero importance. To me, a table is a collection of jars, each jar has a
serial number and the information that must be stored into the database -
the information for which the database has been create - is put inside these
jars. Each jar has its own serial number; however, the exact value of this
serial number has not importance at all in regard to what is put inside the
jar and if copy the information from this database to another database, I
don't care to see the information to be put back in a jar with the exact
same serial number.

However, proponents of the use of natural keys insist to have a strong
relationship between the serial number on the jar and what's in it. To me,
such a relationship is pointless and I feel no necessity to use it.

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


"David Cressey" wrote in message
news:oN%mj.4097$u_5.247@trndny09...
>
> "Marshall" wrote in message
> news:4fad9541-6eda-4ceb-b05b-ce564d0abaa0@z17g2000hsg.google groups.com...
>
>
>> an attempt to make SQL data have an address
>
> Exactly. Literally hundreds of attempts to sell snake oil in c.d.t. can
> be
> reduced to precisely this statement. We've all said variations of the
> above, but I've never seen it put so succintly.
>
> As far as the MS Access newsgroups that this discussion is posted to, I
> can't speak to how well your summary extends to their mental model. But
> many of them seem to write as if
> contents as determined by address were the fundamental paradigm of data.
>
>
>
>
>

Re: Separate PK in Jxn Tbl?

David W. Fenton wrote:

> JOG wrote in
> news:c4699a61-e76f-4ff3-aa25-310929b6ecc0@e10g2000prf.google groups.co
> 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.
>
> 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 and really aren't in any position to be making
> disparaging comments about it.

Nothing you have written contradicts JOG. In fact, you have agreed with
him albeit vehemently and derogatively.

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" wrote in message
news:baWmj.3398$nK5.1213@nlpi069.nbdc.sbc.com...
>
> wrote in message
> news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.com...
> 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.
>
>
>

Re: Separate PK in Jxn Tbl?

David W. Fenton wrote:

> CDMAPoster@fortunejames.com wrote in
> news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.co
> m:
>
>>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?
>
> What if there's more than one application built on top of the
> database?
>
> I, too, agree that one should put as much of the data logic in the
> back end as possible.
>
> However, that doesn't mean I use natural keys very often. I'm
> definitely opposed to compound keys for any table whose PK will be a
> foreign key in another table. It causes myriad problems of all sorts
> (been there, done that), and despite its being theoretically
> correct, just doesn't work well in practice.

Reference is one issue that increases the tradeoff importance of
simplicity relative to the other design criteria.


> Just consider one scenario:
>
> You need to build criteria for a query-by-form interface. That means
> that to query on the PK of a table with a compound PK, you end up
> needing to have multiple fields in your WHERE clause. And if you're
> querying multiple records in the table with the compound PK, you'll
> need a complex nested OR in your WHERE clause.
>
> I know perfectly well that theoretically speaking you're not
> supposed to let your application drive the design of your schema,
> but this is a case where common sense tells me that following theory
> leads to enormously difficult application logic problems.

I have yet to see any evidence from you to suggest you know the first
thing about theory. Perhaps you should strive to learn a little more
about it before blathering on about it.


> Natural keys are great for tables with a single-column natural PK.

A natural key is neither more nor less than a familiar surrogate.


> Otherwise, surrogate keys make building an application substantially
> easier.

I disagree. Since all keys are fundamentally the same thing, surrogacy
is irrelevant. The design criteria for keys are: uniqueness,
irreducibility, simplicity, stability and familiarity (in no particular
order.)


> And, BTW, I would, of course, advocate that any natural key that is
> not used as the PK should naturally have a unique index on it.

You confuse physical and logical issues. One should declare all logical
constraints regardless of the indexes used.


> And any natural key that can't have a unique index (because some
> fields need to be Null) was never a candidate for PK in the first
> place, and would have to have had uniqueness enforced in the
> application in some fashion anyway.

One would have to be an idiot to design anything allowing NULL in the
first place.

Re: Separate PK in Jxn Tbl?

David W. Fenton wrote:

> JOG wrote in
> news:5afa9a80-f1c5-4ede-8095-1f4c0164417a@s12g2000prg.google groups.co
> m:
>
>>No probs, although off the top of my head its gonna be a bit
>>contrived. With an artificial key:
>>
>>Marriages {id, husband, wife, date}
>>Kids_from_Marriage {from_id, name, birth}
>>
>>A query that asks "fetch me all the children whose mother is x"
>>obviously requires an equijoin, matching Marriages.id and
>>Kids.from_id. However with the original natural keys:
>>
>>Marriages {id, husband, wife, date}
>>Kids_from_Marriage {mother, father, name, birth}
>>
>>The same query is a simple select. That certainly seems a lot less
>>complicated to me ;)
>
> Assuming you've got some form of CASCADE UPDATE on your enforced
> relationship, that will work.

You mean "Assuming that CASCADE UPDATE is needed..." Modern dbmses all
meet the requirement in any case causing me to wonder what the hell you
thought your point might have been.


> But it's repeating a huge amount of data

Again, you confuse physical and logical.

[further ramblings snipped]

Re: Separate PK in Jxn Tbl?

David Cressey wrote:
> "Brian Selzer" wrote in message
> news:MOUmj.1465$uE.920@newssvr22.news.prodigy.net...
>
>
>>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.
>
>
> The idea of keeping garbage out of the database takes on an entirely
> different meaning if you are dealing with hundreds of programs written in
> COBOL, Java, or anything in between accessing a single Oracle database on
> the one hand. On the other hand, if you are a developer creating a self
> contained MS Access database cum application (tables, queries, forms,
> reports, modules, etc.) all in one file, the same issues arise, but they
> are resolved quite differently.
>
> I'm not saying either one is "right" or "wrong". I'm just suggesting why an
> objection that makes perfect sense to you and me might be lost on the MS
> Access community.

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. I am not unaware
of the desirability of enforcing constraints at the table level. My
reluctance to depend on error trapping is shared by very few, if any,
Access developers. Those who judge my unorthodox style choices as
ignorant or incorrect presume much.

For example, in "Joe Celko's SQL PROGRAMMING STYLE:

Q: Couldn't a natural compound key become very long?

A1: So what? This is the 21st century, and we have much better
computers than we did in the 1950s when key size was a real physical
issue. What is funny to me is the number of idiots who replace a
natural two- or three- integer compound key with a huge GUID, which no
human being or other system can possibly understand, because they think
it will be faster and easy to program.

....

The auto-numbering features are a holdover from the early SQLs, which
were based on contiguous storage file systems. The data was kept in
physically contiguous disk pages, in physically contiguous rows, made up
of physically contiguous columns. In short, just like a deck of
punchcards or a magnetic tape. Most programmers still carry that mental
model, too.

The most pathetic part is that many here even parrot his (incorrect)
reasoning about my reasoning. I submit that they, not I, are succumbing
to subtle psychological effects.

James A. Fortune
MPAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:OowKsDTYIHA.5416@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" wrote in message
> news:baWmj.3398$nK5.1213@nlpi069.nbdc.sbc.com...
> >
> > wrote in message
> >
news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.com...
> > 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.
> >
> >
> >
>
>

Re: Separate PK in Jxn Tbl?

"-CELKO-" wrote in message
news:c0ed64e6-98e8-4eab-95b3-b724ecbf4c68@q77g2000hsh.google groups.com...
> >> Whenever I've created junction tables [sic] in the past, I always made
the PK of the junction table the combined pks from the two other tables..
<<
>
> I wish I knew where people got the term "junction table" when they
> mean a table that models a relationship . I guess if you are still
> thinking in terms PK-FK being pointer chains and structure and not
> relational, then you would borrow old terminology from Network
> Databases. And bring that mindset with you, too.

It probably came from the term "junction record" in CODASYL databases, where
a record can be a member of two sets, in order to enable a many-to-many
connection between
the owners of the record in the two sets.

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

Re: Separate PK in Jxn Tbl?

David Cressey wrote:

> "Sylvain Lafontaine"
> wrote in message news:OowKsDTYIHA.5416@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.

I, of course, disagree. A graph based dbms has no performance advantage
over a relational dbms because a relational dbms may use exactly the
same physical structures.

[snip]

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:OowKsDTYIHA.5416@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.
>
> 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
> news:baWmj.3398$nK5.1213@nlpi069.nbdc.sbc.com...
>>
>> wrote in message
>> news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.com...
>> 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.
>>
>>
>>
>
>

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

Re: Separate PK in Jxn Tbl?

Neil schreef:
> Larry Daugherty wrote:
>> In the meantime OP is probably trying to hide the matches with which
>> he started the fires....
>
> Indeed. :-|

Why? This fire does shed light. Nice crosspost :-)

--
What you see depends on where you stand.

Re: Separate PK in Jxn Tbl?

"mAsterdam" wrote in message
news:479d2271$0$85782$e4fe514c@news.xs4all.nl...
> Neil schreef:
>> Larry Daugherty wrote:
>>> In the meantime OP is probably trying to hide the matches with which
>>> he started the fires....
>>
>> Indeed. :-|
>
> Why? This fire does shed light. Nice crosspost :-)
>

Yeah, there seems to be a terroritorial divide between CDMA and CDT. Weird.

Re: Separate PK in Jxn Tbl?

Neil schreef:
> mAsterdam wrote:
>> Neil wrote:
>>> Larry Daugherty wrote:
>>>> In the meantime OP is probably trying to hide the matches with which
>>>> he started the fires....
>>> Indeed. :-|
>> Why? This fire does shed light. Nice crosspost :-)
>
> Yeah, there seems to be a terroritorial divide between CDMA and CDT. Weird.

Keep 'm dry. You may need them to burn down other fences.

Re: Separate PK in Jxn Tbl?

Neil wrote:

> "mAsterdam" wrote in message
> news:479d2271$0$85782$e4fe514c@news.xs4all.nl...
>
>>Neil schreef:
>>
>>>Larry Daugherty wrote:
>>>
>>>>In the meantime OP is probably trying to hide the matches with which
>>>>he started the fires....
>>>
>>>Indeed. :-|
>>
>>Why? This fire does shed light. Nice crosspost :-)
>
> Yeah, there seems to be a terroritorial divide between CDMA and CDT. Weird.

The divide has a name: "education"

Re: Separate PK in Jxn Tbl?

On Jan 27, 8:33 pm, "David W. Fenton"
wrote:
> JOG wrote innews:c4699a61-e76f-4ff3-aa25-310929b6ecc0@e10g2000prf.goog legroups.co
> 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/

Re: 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

Re: Separate PK in Jxn Tbl?

On Jan 27, 8:39 pm, "David W. Fenton"
wrote:
> JOG wrote innews:5afa9a80-f1c5-4ede-8095-1f4c0164417a@s12g2000prg.goog legroups.co
> m:
>
> > No probs, although off the top of my head its gonna be a bit
> > contrived. With an artificial key:
>
> > Marriages {id, husband, wife, date}
> > Kids_from_Marriage {from_id, name, birth}
>
> > A query that asks "fetch me all the children whose mother is x"
> > obviously requires an equijoin, matching Marriages.id and
> > Kids.from_id. However with the original natural keys:
>
> > Marriages {id, husband, wife, date}
> > Kids_from_Marriage {mother, father, name, birth}
>
> > The same query is a simple select. That certainly seems a lot less
> > complicated to me ;)
>
> Assuming you've got some form of CASCADE UPDATE on your enforced
> relationship, that will work.
>
> But it's repeating a huge amount of data,

I'd think adding two new unecessary columns full of data is rather
adding redundancy too.

> and adding a bunch more
> indexes to keep updated. Perhaps these are insignificant issues to
> *you* and *your* apps, but my clients' apps (some using Jet, some
> using SQL Server, some using MySQL) don't perform so well when you
> add in all the overhead.

Again, I think you have missed the fact that James asked me for an
example where using an artificial key can complicate _queries_, which
I tried to provide. We weren't discussing anything else.

>
> And it all leaves aside the question of how you know that
> husband/wife/date is always going to be unique. I think that on any
> given day in the US, there are plenty of marriages in which those
> three values will be identical. You could add place. But then, in
> large cities, that might not be enough. So use Postal Code in place
> of place, and that might do the trick, although in large cities that
> might not do it, either.

Well, yes what Marshall said, and I'll raise you an SSN.

>
> Given that I can foresee a reasonable possibility of a collision on
> this candidate key as currently defined, I'd think long and hard on
> whether to use it or not.
>
> And it's one of the main problems any time you're storing data about
> people in a data table -- you often lack pieces of the information,
> and you run a high risk of collisions between people with the same
> names.
>
> And that fact of the real-world entities being modelled makes
> finding a natural key that will work as a PK a very hard task.
>
> --
> David W. Fenton http://www.dfenton.com/
> usenet at dfenton dot com http://www.dfenton.com/DFA/

Re: 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/microsoft.public.access.conv ersion/msg/163c373772d787d3
>
>"One idiot student once emailed me"
>http://groups.google.com/group/microsoft.public.access.modu lesdaovba/msg/4d5e152c11a9e0a3
>
>"tell him he's an idiot"
>http://groups.google.com/group/microsoft.public.access/msg/ 31c0abc7b367656d
>
>"This person is an idiot."
>http://groups.google.com/group/microsoft.public.access/msg/ 6fedf72a4680cf25
>
>"But no, some idiot manager at Mickeysoft..."
>http://groups.google.com/group/microsoft.public.access.form s/msg/222d3ed992e9a2ec

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/

Re: Separate PK in Jxn Tbl?

Bob Badour wrote:

>> Yeah, there seems to be a terroritorial divide between CDMA and CDT. Weird.
>
>The divide has a name: "education"

Another word that suits might be "pompous"

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/

Re: 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/

Re: 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/

Re: 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/

Re: 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/

Re: 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

Re: Separate PK in Jxn Tbl?

Brian Selzer wrote:

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

I'm sure you already know this, but:

1) Assuming proper indexes, the extra time required to process the extra
joins should be negligible.

2) If the natural keys are changeable, then synthetic keys are a Big
Win. (Example: One of my higher-end clients has lots of items that
are essentially the same except for color, and routinely discontinues
slow-moving options - including, for various reasons, prepending 'Z'
to the item number.)

Re: Separate PK in Jxn Tbl?

rkc wrote:

> Marshall wrote:
>> On Jan 26, 11:13 pm, "Brian Selzer" wrote:
>>> 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.
>>
>> Right. And then you need to change a constraint, and you modify the
>> code and you remember to update three out of the four forms that
>> implement the check, and you get garbage in the database. Or
>> any of a number of other things that can go wrong.
>
> 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.

The few Access projects that I've taken over have precisely followed
the "just bind a form" approach. Which is presumably why the original
developers were sacked and I was brought in... :)

Re: Separate PK in Jxn Tbl?

On Jan 27, 6:30 pm, Marshall wrote:
> There's another approach to enforcing constraints: just tell your
> users not to do that. In the hierarchy of what works, it comes
> below checks in client code, but above manually inserting
> garbage at design-time. It also has the advantage of being
> even lower performance overhead for the software.

Funny but I've actually seen this in the Access groups as an answer to
the alluded to question, "How do you prevent data corruption to your
'Access' data by users connecting via Excel?" (a common enough
scenario) i.e. put it in the staff handbook and make doing so a
disciplinary matter. Yes, I know: door...horse...bolted.

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 28, 3:38 am, "Tony Toews [MVP]" wrote:
> 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.

I saw your earlier post on this and wondered if you were being
genuine. Now that I see you are...

Seriously, have you tried doing this kind of thing in Visual Studio
2005 i.e. using the .NET framework? I was helping a work colleague
(non-dev) recently who was using the free version of VS to do a simple
club subscriptions app, with data bound controls, navigation bar, etc
i.e. at a glance very reminiscent of Access Forms (except the look and
feel was much nicer ). I've also made initial investigations into
third party frameworks (i.e. extending .NET framework) to bind
business rules' logic to controls. If you believe that MS are not
evolving Access in the developer's interests (and I think you may well
be correct) and at the same time are making .NET Windows Forms
development more accessible (pun intended), maybe it's time to re-
evaluate your personal direction...

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 27, 9:59 pm, "Sylvain Lafontaine" blanks, no spam please)> wrote:
> =AB But many of them seem to write as if contents as determined by address=
=BB.
>
> The content is not determined by the address and in fact, for those who ar=
e
> using surrogate keys, the exact value of an address inside the database ha=
s
> zero importance.

For you maybe.

In a recent thread on this subject, Tony Toews Access MVP qualified
that he liked using incremental autonumbers (rather than random)
because they where easier to type (WHERE ID =3D -2001736589 may
encourage typos) and easier to drop into conversation ("Hello Tony?
I'm seeing a problem with the record where the ID is -2001736589...").
Did I mention that I sincerely appreciate Tony's honesty?

Also consider the amount of posts we see in the Access groups asking
to reseeding autonumbers, gaps in sequences, etc. Wrong mental model,
perhaps, but the mentality certainly exists.

Jamie.

--

Re: Separate PK in Jxn Tbl?

"Sylvain Lafontaine"
wrote in message news:uzBiNoRYIHA.4172@TK2MSFTNGP02.phx.gbl...

> This is the big problem with natural keys. When the theory was first
> elaborated, the point was that a natural key never change its value. That
> was the point that was making the choice of a natural key on par with the
> use of a surrogate key as the primary key of a table. However, like
anyone
> have discovered with experience, a natural key can change its value under
a
> set of various circonstances. One could argue that if a key can change
its
> value, than it's not a natural key but as you know, this argument bring
> nothing in regard to help you choosing a natural key.
>

This is a specific example of a syndrome that I described more generally:
the mismanagement of natural keys.

If a key is "natural", it is managed, if at all, beyond the scope of the
DBMS. It might be managed at the application layer, or it might be managed
by people, or it might indeed be unmanaged data, like sunspots.

When it is managed by other people, it is subject to mismanagement.
Changing values that ought to be immutable is one of many ways that natural
keys can be mismanaged.

As I said before, I prefer to use natural keys where ever possible. If
that's not possible due to mismanagement of the natural keys, I'll use
synthetic keys.

Re: Separate PK in Jxn Tbl?

"Rob" wrote in message
news:0a65204e-068c-4e77-900c-319439ab6707@d70g2000hsb.google groups.com...
On Jan 22, 1:26 pm, "Neil" wrote:

(quote)
I don't claim to know more about db theory than the cdt regulars, or
to have more experience than the many practioners who have contributed
to this thread.

I do know that the debate over relationship representations has been
going on for years, neither side giving any ground. Some aspects of it
are never addressed. For example, making the two foreign keys in a
junction table a composite PRIMARY key prohibits NULL values from
either key, but declaring a separate surrogate primary key together
with a UNIQUE constraint on the two-foreign-key-composite-key does
allow NULL values for either (or both!). This can be useful for
representing unrelated entity tuples, either childless parents or
orphans.

(unquote)

In the case of a junction table, this point is moot.

If both FK values are NULL, the entire row of the junction table can be
omitted with no loss of information.

Childless parents will exist in the table that contains parents, where the
id of the parent is not an FK, but a PK. Same pattern for orphans. The two
tables can even be the same table.

Re: Separate PK in Jxn Tbl?

"David W. Fenton" wrote in message
news:Xns9A329D4116528f99a49ed1d0c49c5bbb2@64.209.0.89...
> CDMAPoster@fortunejames.com wrote in
> news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.co
> m:
>
> > 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?
>
> What if there's more than one application built on top of the
> database?
>

I believe this is a moot point when it comes to MS Access. The app and the
database are all stored together in Access. There is, by definition, only
one Access.

Access regulars, feel free to correct this if it's wrong.

Re: Separate PK in Jxn Tbl?

"-CELKO-" wrote in message
news:93e32413-efca-4133-abb5-d0b76b944a19@q21g2000hsa.google groups.com...
> >> Does a bottle of Gin have a serial no? I am too poor to buy 2 at a time
to see if there is a different number Ah! <<
>
> In the US, you get a UPC/EAN code and then a batch number with booze.
> The closest thing to a serial number is your cash register receipt.

This is the "cat food problem" in a nutshell. (Or, in this discussion, the
"gin bottle" problem.)
The problem has been known as the "cat food" problem for years and years.

Items on a cash register tape represent entities with no identity at the
individual level, only at the batch or product level. You therefore can't
tell whether two bottles of gin were checked out, or whether one bottle was
scanned twice.

Re: Separate PK in Jxn Tbl?

"mAsterdam" wrote in message
news:479d2271$0$85782$e4fe514c@news.xs4all.nl...
> Neil schreef:
> > Larry Daugherty wrote:
> >> In the meantime OP is probably trying to hide the matches with which
> >> he started the fires....
> >
> > Indeed. :-|
>
> Why? This fire does shed light. Nice crosspost :-)
>
"Les conseils de la vieillesse sont comme le soleil d'hiver. Ils eclairent
sans echauffer."

Re: Separate PK in Jxn Tbl?

"David Cressey" wrote in message
news:%9lnj.5893$cm6.4751@trndny05...
>
> When it is managed by other people, it is subject to mismanagement.
> Changing values that ought to be immutable is one of many ways that
> natural
> keys can be mismanaged.
>
> As I said before, I prefer to use natural keys where ever possible. If
> that's not possible due to mismanagement of the natural keys, I'll use
> synthetic keys.

I think you may be asking too much of a natural key. I always took "stable"
to mean *almost* never changes ratherer than meaning absolutely immutable
(which I take to mean "intrinsically incapable of changing"). But whatever
one thinks it should mean, one always has to accommodate the possibility
that a key value will be mis-typed during manual entry by a user, and
therefore that it will have to be corrected. Mismanagement is, as you
rightly say, a fact of life.

But that's not usually a sufficiently good reason to introduce a synthetic
key. I have found it possible to tolerate quite a lot of updates to
supposedly stable keys using ON UPDATE CASCADE, with imperceptible
response-time costs. (One can of course measure the costs, but that's not
what counts.) A key would have to be really unstable to justify using a
synthetic key IMO.

Roy

Re: Separate PK in Jxn Tbl?

"David Cressey" wrote in message
news:fhlnj.2005$k1.588@trndny02...
>
> "David W. Fenton" wrote in message
> news:Xns9A329D4116528f99a49ed1d0c49c5bbb2@64.209.0.89...
>> CDMAPoster@fortunejames.com wrote in
>> news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.google groups.co
>> m:
>>
>> > 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?
>>
>> What if there's more than one application built on top of the
>> database?
>>
>
> I believe this is a moot point when it comes to MS Access. The app and
> the
> database are all stored together in Access. There is, by definition, only
> one Access.
>
> Access regulars, feel free to correct this if it's wrong.


The app and the data *can* be stored in one MDB, but do not *have* to be
stored in one MDB (linked tables). It is entirely possible - and in my
experience not unusual - to have more than one application reading and or
updating data in an MDB (or ACCDB).

--
Brendan Reynolds

Re: Separate PK in Jxn Tbl?

David Cressey wrote:
> "David W. Fenton" wrote in message
> news:Xns9A329D4116528f99a49ed1d0c49c5bbb2@64.209.0.89...
>
>>CDMAPoster@fortunejames.com wrote in
>>news:db4e7666-f9ef-4c72-a483-f951b80c6183@k39g2000hsf.goog legroups.co
>>m:
>>
>>>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?
>>
>>What if there's more than one application built on top of the
>>database?
>
> I believe this is a moot point when it comes to MS Access. The app and the
> database are all stored together in Access. There is, by definition, only
> one Access.
>
> Access regulars, feel free to correct this if it's wrong.

While awkward, slow and kludgy, it is possible to attach an Access app
to an external database.

Re: Separate PK in Jxn Tbl?

On Jan 28, 2:03 pm, "David Cressey" wrote:
> > What if there's more than one application built on top of the
> > database?
>
> I believe this is a moot point when it comes to MS Access. The app and the
> database are all stored together in Access. There is, by definition, only
> one Access.

The properties of the Access user interface clearly need to be stored
somewhere and the place is in an mdb (or equivalent) file. Jet table
data is also persisted in mdb files. Yes, in a single user,
traditional Access/Jet application, this is likely to be the same file
as the Jet tables. However, in a multiuser Access/Jet application set
up is most common to have the Forms, Reports, etc ('front end')
'split' from the 'back end; requiring a FE/BE split is one of the
Access Ten Commandments (I kid you not). Typically, each user machine
will have its own copy of a 'front end', therefore in such a situation
there are indeed multiple applications accessing the same 'back end'.

Jamie.

--

Re: Separate PK in Jxn Tbl?

On Jan 28, 3:10 am, "Tony Toews [MVP]" wrote:
> You almost got me there. However I was ridiculuing other people who
> weren't part of the conversation.

I acknowledge the fact I have nothing on you :)

> Not much of an excuse.

Well, my quick search revealed you have called *yourself* an 'idiot'
far more often and recently admonished a regular for name calling. You
are a gentleman.

Jamie.

--

Re: Separate PK in Jxn Tbl?

"Roy Hann" wrote in message
news:Mt2dnd8_2Y5tewDanZ2dneKdnZydnZ2d@pipex.net...
> "David Cressey" wrote in message
> news:%9lnj.5893$cm6.4751@trndny05...
> >
> > When it is managed by other people, it is subject to mismanagement.
> > Changing values that ought to be immutable is one of many ways that
> > natural
> > keys can be mismanaged.
> >
> > As I said before, I prefer to use natural keys where ever possible. If
> > that's not possible due to mismanagement of the natural keys, I'll use
> > synthetic keys.
>
> I think you may be asking too much of a natural key. I always took
"stable"
> to mean *almost* never changes ratherer than meaning absolutely immutable
> (which I take to mean "intrinsically incapable of changing"). But
whatever
> one thinks it should mean, one always has to accommodate the possibility
> that a key value will be mis-typed during manual entry by a user, and
> therefore that it will have to be corrected. Mismanagement is, as you
> rightly say, a fact of life.
>

You are right. Whether or not mismanagement is a sufficient reason for
distrust of a natural key depends on the degree of mismanagement. I should
have made that explicit in my earlier post.


> But that's not usually a sufficiently good reason to introduce a synthetic
> key. I have found it possible to tolerate quite a lot of updates to
> supposedly stable keys using ON UPDATE CASCADE, with imperceptible
> response-time costs. (One can of course measure the costs, but that's not
> what counts.) A key would have to be really unstable to justify using a
> synthetic key IMO.
>

One problem with cascading updates occurs when you want to relate data still
in the database to data that was extracted from the database at an earlier
point in time. This includes, but is not limited to, data in the log file.

Re: Separate PK in Jxn Tbl?

"David Cressey" wrote in message
news:U6nnj.8$1f.5@trndny02...
>
> One problem with cascading updates occurs when you want to relate data
> still
> in the database to data that was extracted from the database at an earlier
> point in time. This includes, but is not limited to, data in the log
> file.

Good points, and I will always concede that these are the kinds of external
factors that in practice force one into using more synthetic keys than we'd
like. Happily in my case the problem isn't too bad because we have a very
nice journal analyzer that quickly reveals any key updates.

I hope I have also been pretty careful to warn people that creating
redundant copies of data outside the database will cause all the problems
one always associates with redundant copies of data. We tend to do a lot of
refreshes instead of hoping our snapshots haven't mutated too badly. (In a
way it's been a chicken and egg solution: we never allowed the egg, so we
don't have to deal with the chicken! :-) We also make use of replication
in several systems, and to our replicator an updated key is just another
update.

But to repeat, I do understand synthetic keys will sometimes be forced on
me. I don't insist they can always be avoided. They are my just last
resort, not my first.

Roy

Re: Separate PK in Jxn Tbl?

"David Cressey" wrote in message
news:%9lnj.5893$cm6.4751@trndny05...
>
> "Sylvain Lafontaine"
> wrote in message news:uzBiNoRYIHA.4172@TK2MSFTNGP02.phx.gbl...
>
>> This is the big problem with natural keys. When the theory was first
>> elaborated, the point was that a natural key never change its value.
>> That
>> was the point that was making the choice of a natural key on par with the
>> use of a surrogate key as the primary key of a table. However, like
> anyone
>> have discovered with experience, a natural key can change its value under
> a
>> set of various circonstances. One could argue that if a key can change
> its
>> value, than it's not a natural key but as you know, this argument bring
>> nothing in regard to help you choosing a natural key.
>>
>
> This is a specific example of a syndrome that I described more generally:
> the mismanagement of natural keys.
>
> If a key is "natural", it is managed, if at all, beyond the scope of the
> DBMS. It might be managed at the application layer, or it might be
> managed
> by people, or it might indeed be unmanaged data, like sunspots.
>
> When it is managed by other people, it is subject to mismanagement.
> Changing values that ought to be immutable is one of many ways that
> natural
> keys can be mismanaged.
>
> As I said before, I prefer to use natural keys where ever possible. If
> that's not possible due to mismanagement of the natural keys, I'll use
> synthetic keys.
>
>
>

I don't think it's productive to blame it on the users. The problem you're
referring to is due to the nature of keys, and misunderstandings on the part
of the database designer as to what constitutes a key. All that is required
for a key to be a key is that in every possible database instance, a
projection over the attributes in the key for a relation has the same
cardinality as the relation. This does /NOT/ mean that a particular
combination of values /always/ identifies the same individual in the
Universe of Discourse, but only in the picture of the Universe that is a
database instance. In other words, a particular combination of values may
not /necessarily/ identify an individual, but rather may only /contingently/
identify an individual. This is the nature of keys: either the values for a
key are permanent identifiers, or they're not. It has nothing to do with
how well keys are managed. The values for a key may be managed perfectly,
yet still not be permanent identifiers--the position of something in a list
of things comes to mind.

Re: Separate PK in Jxn Tbl?

Bof, if you live in a perfect world, one with infinite budget and infinite
time to do any project, then I understand your concerns.

However, in my case and probably in the case of Tony Toews - but I cannot
vouch for sur for him - I don't live in such a world. First of all, if I
were to live in a perfect world, I wouldn't have to work to earn a living in
the first place. Second, you cannot codifying everything for a variety of
reasons: budget, system already in place and working well, impossibility to
anticipate everything, more art than a science, etc., etc.

I have a client who has such a system at the moment as one critical part of
their business process, this system is working well at the moment and has
done so for many years; it would probably make your teeth gnashing but it's
not on their radar at this moment to change it. Why would they pay to
change something that had worked well for them for many years and at the
risk of finding themselves at the front of something new that might not work
as well as the one system? To give pleasure to people like Celko? If I
were to tell them that Celko would like to see them changing their system,
they would probably tell that if Celko was to bring them a check to pay for
the change, then maybe they would give it a try.

In this message, you can replace the name of Celko with the name of a lot of
persons around here but not with mine.

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


"Jamie Collins" wrote in message
news:0620febc-be3d-4c68-98e4-d8896415285c@i72g2000hsd.google groups.com...
On Jan 27, 9:59 pm, "Sylvain Lafontaine" blanks, no spam please)> wrote:
> « But many of them seem to write as if contents as determined by
> ddress ».
>
> The content is not determined by the address and in fact, for those who
> are
> using surrogate keys, the exact value of an address inside the database
> has
> zero importance.

For you maybe.

In a recent thread on this subject, Tony Toews Access MVP qualified
that he liked using incremental autonumbers (rather than random)
because they where easier to type (WHERE ID = -2001736589 may
encourage typos) and easier to drop into conversation ("Hello Tony?
I'm seeing a problem with the record where the ID is -2001736589...").
Did I mention that I sincerely appreciate Tony's honesty?

Also consider the amount of posts we see in the Access groups asking
to reseeding autonumbers, gaps in sequences, etc. Wrong mental model,
perhaps, but the mentality certainly exists.

Jamie.

--

Re: Separate PK in Jxn Tbl?

Jamie Collins wrote:

>> You almost got me there. However I was ridiculuing other people who
>> weren't part of the conversation.
>
>I acknowledge the fact I have nothing on you :)
>
>> Not much of an excuse.
>
>Well, my quick search revealed you have called *yourself* an 'idiot'
>far more often and recently admonished a regular for name calling. You
>are a gentleman.

Ah, thanks. I had actually forgotten all that.

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/

Re: Separate PK in Jxn Tbl?

Bob Badour wrote:

>> I believe this is a moot point when it comes to MS Access. The app and the
>> database are all stored together in Access. There is, by definition, only
>> one Access.
>>
>> Access regulars, feel free to correct this if it's wrong.
>
>While awkward, slow and kludgy, it is possible to attach an Access app
>to an external database.

And how is Access more awkward, slow and kludgy than other products such as say VB
6.0, VB.Net, or whatever?

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/

Re: Separate PK in Jxn Tbl?

Jamie Collins wrote:

>On Jan 28, 3:38 am, "Tony Toews [MVP]" wrote:
>> 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.
>
>I saw your earlier post on this and wondered if you were being
>genuine. Now that I see you are...
>
>Seriously, have you tried doing this kind of thing in Visual Studio
>2005 i.e. using the .NET framework? I was helping a work colleague
>(non-dev) recently who was using the free version of VS to do a simple
>club subscriptions app, with data bound controls, navigation bar, etc
>i.e. at a glance very reminiscent of Access Forms (except the look and
>feel was much nicer ). I've also made initial investigations into
>third party frameworks (i.e. extending .NET framework) to bind
>business rules' logic to controls. If you believe that MS are not
>evolving Access in the developer's interests (and I think you may well
>be correct) and at the same time are making .NET Windows Forms
>development more accessible (pun intended), maybe it's time to re-
>evaluate your personal direction...

It's my understanding that the ease of use and speed of development of the latest
..Net products is getting closer to that of Access. So I'm certainly keeping my eye
on these.

Not convinced yet but then I haven't done next to no research in this area. Too
busy.

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/

Re: Separate PK in Jxn Tbl?

"Brian Selzer" wrote in message
news:odonj.910$R84.571@newssvr25.news.prodigy.net...
>
> I don't think it's productive to blame it on the users. The problem
> you're referring to is due to the nature of keys, and misunderstandings on
> the part of the database designer as to what constitutes a key. All that
> is required for a key to be a key is that in every possible database
> instance, a projection over the attributes in the key for a relation has
> the same cardinality as the relation. This does /NOT/ mean that a
> particular combination of values /always/ identifies the same individual
> in the Universe of Discourse, but only in the picture of the Universe that
> is a database instance. In other words, a particular combination of
> values may not /necessarily/ identify an individual, but rather may only
> /contingently/ identify an individual. This is the nature of keys: either
> the values for a key are permanent identifiers, or they're not. It has
> nothing to do with how well keys are managed. The values for a key may be
> managed perfectly, yet still not be permanent identifiers--the position of
> something in a list of things comes to mind.

No, the position number identifies the same position, for all time. The
facts about the current occupant of the position may change willy-nilly.

Roy

Re: Separate PK in Jxn Tbl?

Brian Selzer wrote:

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

Brian,

Thanks for attempting a concrete example to show your point. Although
the first schema doesn't look like something I would have created I will
go over your example and post back later, perhaps when the dust has
settled a bit in this thread. In spite of my sometimes unorthodox ways
I am a reasonable person who is always looking for better ways to do things.

James A. Fortune
MPAPoster@FortuneJames.com

Re: Separate PK in Jxn Tbl?

On Jan 28, 7:01 am, "David Cressey" wrote:
> "Rob" wrote in message
>
> news:0a65204e-068c-4e77-900c-319439ab6707@d70g2000hsb.google groups.com...
> On Jan 22, 1:26 pm, "Neil" wrote:
>
> (quote)
> I don't claim to know more about db theory than the cdt regulars, or
> to have more experience than the many practioners who have contributed
> to this thread.
>
> I do know that the debate over relationship representations has been
> going on for years, neither side giving any ground. Some aspects of it
> are never addressed. For example, making the two foreign keys in a
> junction table a composite PRIMARY key prohibits NULL values from
> either key, but declaring a separate surrogate primary key together
> with a UNIQUE constraint on the two-foreign-key-composite-key does
> allow NULL values for either (or both!). This can be useful for
> representing unrelated entity tuples, either childless parents or
> orphans.
>
> (unquote)
>
> In the case of a junction table, this point is moot.
>
I'm not sure what you mean by "moot". According to:

http://www.usingenglish.com/reference/idioms/moot+point.html


If something's a moot point, there's some disagreement about it: a
debatable point. In the U.S., this expression usually means that there
is no point in debating something, because it just doesn't matter. An
example: If you are arguing over whether to go the beach or to the
park, but you find out the car won't start and you can't go anywhere,
then the destination is said to be a moot point.


I googled "moot" to make sure I knew what you meant, and was surprised
by the definition. I will take your meaning to be '"there is no point
in debating" whether NULL values in junction tables are ever useful
because they are not'.

Suppose I have an application that (among other things) assigns child
tuples in a child relation to parent tuples in a parent relation. I've
studied the relationship and determined it can be many-to-many, so I
implement the relationships using a junction table.

Consider the following 2 cases (MS SQL syntax):
IMPLICIT: Implicit Junction Table Representation of childless parents
and orphans:
create table impJT(
impJT_parentFK int not null,
impJT_childFK int not null,
primary key (impJT_parentFK,impJT_childFK)
)
EXPLICIT: Explicit Junction Table Representation of childless parents
and orphans:
create table expJT(
expJT_PK int identity(1,1) primary key,
expJT_parentFK int,
expJT_childFK int,
UNIQUE(expJT_parentFK,expJT_childFK)
)

In the IMPLICIT case, there will be a primary key index on
(impJT_parentFK,impJT_childFK).
In the EXPLICIT case, there will be a primary key index on expJT_PK
and a unique index on (expJT_parentFK,expJT_childFK). NULL values are
allowed for parentFK, for childFK or both.

Now suppose that there is a parent tuple in the parent relation for
"alphaparent" with primary key "alphaparentPK", and that "alphaparent"
is childless. My application wants to know if "alphaparent" is
childless: If so, it will assign "betachild" with primary key
"betachildPK" to "alphaparent" by inserting
("alphaparentPK","betachildPK") into the junction table.

In the IMPLICIT case, ("alphaparentPK",NULL) won't occur in the
junction table. How does my application determine that "alphaparent"
is childless?

a.) select count(*) from parent as p, impJT as j where p.parentname =
"alphaparent" and p.parentPK = j.impJT_parentFK

This is guaranteed to be slow, and the result is equivocal: I can't be
certain whether alphaparent is in the parent table or not, so a count
of zero is not enough. I could insure that "alphaparent" is in the
parent relation with:

b.) select parentPK,count(*) from parent as p, impJT as j where
p.parentname = "alphaparent" and p.parentPK = j.impJT_parentFK group
by p.parentPK

Probably even slower, but gets the result I need. The point is, you
must access both relations.

In the EXPLICIT case, ("alphaparentPK",NULL) does occur in the
junction table which is indexed. In this case, my application can use:

c.) select parentPK from parent as p, expJT as j where p.parentname =
"alphaparent" and j.expJT_childFK is NULL

A smart query optimizer would avoid access to the junction table
altogether, using only its unique index.

There are of course design tradeoffs between the IMPLICIT and EXPLICIT
cases, and I'm not going to debate database and application designs.
But to say '"there is no point in debating" whether NULL values in
junction tables are ever useful' is just a little hasty.
>
> If both FK values are NULL, the entire row of the junction table can be
> omitted with no loss of information.
>
Disagree. There is a tendency among the cdt purists to assume that
meta models (i.e., database schemas) describe set-oriented storage
structures for facts and propositions. I take a different approach: I
consider these as meta models of dynamic universes. Using the EXPLICIT
junction table above, I can visualize a multi-agent application in
which (NULL,NULL) inserted to the junction table is a way for one
agent to signal another agent that some set of actions is required. In
that case, omitting the (NULL,NULL) tuple means information would
indeed be lost. (Imagine that the agents are transient, they have no
ability to "call" one another and they have no persistent storage
besides what is in the database.) Stop squirming purists!!
>
> Childless parents will exist in the table that contains parents, where the
> id of the parent is not an FK, but a PK. Same pattern for orphans. The two
> tables can even be the same table.
>
Not sure what you are driving at here. If this is important, please
clarify and I'll try to respond.

Rob

P.S. I'm no fan of junction tables. I use the Aggregate-Link
representation of relationships described here:

http://www.sfdbs.com/toplevel/fasttrack/fasttrack.shtml

The A-L representation is so explicit, it allows for childless parent-
and orphan child tuples in the relationship as well as parent- and
child tuples NOT in the relationship. A junction table admits one or
the other, not both.

Your opinion may differ, but I assure you is most welcome.