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/

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.

--

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.

--

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/micro...3c373772d787d3

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

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

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

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

Jamie.

--

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.

--

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

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

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

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.

What happens if something happens to the warehouse that makes it un-usable.
Not enough to affect the items "in" the warehouse. Would this not mean the
items are moved to another warhouse. Why not just use the item key as a
stand alone or (as Sylvain suggested) have the Item Key as an autonumber.

I'm not arguing either way - I am trying to learn better but it seems that
createing a muliple layer primary field is just asking for problems when
there is no need to do this as all DB's can cope perfectly well with just an
autonumber. I was always told that the primary field was "not" there for an
other purpose than to indetify that specific recordset.

If you use multiple layered key fields are you not assigning another value
to the primary (that of a - in your example - a product/item locator).

As I said I'm not standing on either side I'm just wanting to me knowledge
increase.

Thank you
--
Wayne
Manchester, England.



"Brian Selzer" wrote:


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

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