B
Bob Badour
Larry said:
Bullshit! Ignorance, stupidity and laziness are not practical.
[snip]
Bullshit! Ignorance, stupidity and laziness are not practical.
[snip]
T
Tony Toews [MVP]
Larry Daugherty said:
<chuckle> 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/
L
Larry Daugherty
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!
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!
L
lyle fairfield
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!
B
Bob Badour
Larry said:
Your hope is futile in the face of your ignorance and laziness.
I'll try.
Ignorance is bliss.
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.
T
Tony Toews [MVP]
JOG said:
But in many situations why go to the extra effort of Oracle or <cough> 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
<smile>
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/
T
Tony Toews [MVP]
JOG said:
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/
T
Tony Toews [MVP]
Bob Badour said:
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/
B
Bob Badour
lyle said:
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
because you can't program or design databases well?
Since they are simpletons, I have no doubt they would simplify the
concepts ... beyond all recognition.
B
Brian Selzer
Tony Toews said:
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?
J
Jamie Collins
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.
--
S
Sylvain Lafontaine
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)
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
(e-mail address removed)
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)
What part of simpler don't you understand
. Only one expression inthe 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
(e-mail address removed)
B
Brian Selzer
Sylvain Lafontaine said:
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)
What part of simpler don't you understand
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
(e-mail address removed)
W
Wayne-I-M
For example, in an Inventory table
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.
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 said:Sylvain Lafontaine said:
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)
What part of simpler don't you understand
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
(e-mail address removed)
N
Neil
Bob,
Here's a programming tip for you: prozac.
Here's a programming tip for you: prozac.
Bob Badour said:
N
Neil
Jamie Collins said:
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?
N
Neil
Larry Daugherty said:
Indeed. :-|
D
David Cressey
(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.
What part of simpler don't you understand
. Only one expression inthe 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.
B
Bob Badour
Roy said:
That, in a nutshell, is Date's _Principle of Incoherence_.
One specific comment I will make is that my progression has
I have found some people can work for 10 years and get a year's
experience 10 times.
could just
B
Bob Badour
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.
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!
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
It is not a theoretical argument at all. You simply regurgitate
ignorance and stupidity.
[remaining nonsense snipped]
Plonk!
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 said:
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!
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
It is not a theoretical argument at all. You simply regurgitate
ignorance and stupidity.
[remaining nonsense snipped]
Plonk!