How do I get this query to work?

[Karen Hill]

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
[surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Thanks in advance for all your help.

[Bob Stearns]

Karen Hill wrote:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Thanks in advance for all your help.

You you need some ddl to make this clear, but I'll take a stab at what I
think you want:
AND (([surgeries].[date_op]='2005-07-04') OR
([surgeries].[date_op]='2005-05-30')));
This is the standard sql technique. You'll have to adjust for Access's
method of substituting input parameters, I'm a db2 user myself.

[MGFoster]

Karen Hill wrote:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
AND WHERE (([surgeries].[date_a]=Date()) Or
[surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals and
surgeries where surgeries.id = animals.id and only where the surgery
date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Not sure how a surgery equals an animal?! You sure you have your tables
set up correctly?

Your query has incorrect syntax - there is no AND before WHERE.

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON [surgeries].[id]=[animals].[id]
WHERE [surgeries].[date_a]=Date()
OR [surgeries].[date_b]=Date())

A good rule of thumb is to always use a column list in the SELECT
clause, not the ALL wildcard (*).

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQtHi7YechKqOuFEgEQIERQCg8m9qdvyTO9s3YJ1NTUwpdK 1jSwcAn0T8
dqa96HvXQCHAkJdZkrQyHrFJ
=a9kM
-----END PGP SIGNATURE-----

[David W. Fenton]

"Karen Hill" <ka**********@yahoo.com> wrote in
news:11**********************@g49g2000cwa.googlegr oups.com:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON
[surgeries].[id]=[animals].[id] AND WHERE
(([surgeries].[date_a]=Date()) Or [surgeries].[date_b]=Date());

I'm trying to write a query that joins two table together, animals
and surgeries where surgeries.id = animals.id and only where the
surgery date was date_a or date_b.

I'm doing this in Microsoft Access 2000 and am tearing out my hair
trying to figure it out.

Is the ID field in the two tables the same data? That is, does the
animals table have its own Autonumber field with the exact same name
as the Autonumber field in the surgeries table? If so, you don't
want to use it to join the two tables, as, despite the fact that
you've unwisely given it the same name, it's not the same data. One
is the ID for a surgery, and the other is an ID for an animal.

I never use "ID" as a field name. I always use names in the form
AnimalID or SurgeryID. I also name my tables in the form tblAnimal
and tblSurgery (singular, not plural, since the entity represented
by each record is a single instance of whatever entity the table is
storing data about). Then I can figure out the name of the primary
key by taking the table name, lopping off the first three characters
and adding "ID" to it.

Anyway, you should be storing the AnimalID in the surgeries table,
and linking on that:

SELECT surgeries.*, animals.*
FROM surgeries INNER JOIN animals ON
surgeries.AnimalID=animals.AnimalID
WHERE ((surgeries.[date_a]=Date()) Or surgeries.[date_b]=Date());

Also note that there oughtn't be an AND before the word WHERE.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[--CELKO--]

You are giving the worst possible advise to the Newbie.

I also name my tables in the form tblAnimal and tblSurgery (singular, not plural, since the entity represented by each record [sic] is a single instance of whatever entity the table is storing data about). <<
First of all, there are no records and fields in RDBMS; rows and
columns are totally different concepts. I agree that "the entity
represented by each record [sic] is a single instance.." but that has
nothing with naming the table that holds those entities. What is the
name of the set, not the entities? This is one reason why a table is
not a file. Sets are not sequential, are not a list of entities. Read
any book on Sets. Or data modeling. You are doing record processing
in your head and have not moved to a set oriented model.

ISO-11179 Standards tells us not to put silly prefixes like "tbl-"
that describe the physical storage into a data element name. Name each
data element for what it is; never for how it is used in one place, how
it is encoded, where it occurs, etc. A data element should have one and
only one name.
Then I can figure out the name of the primary key by taking the table name, lopping off the first three characters and adding "ID" to it. <<

Amazing how you do not have to refer to industry standards, or even do
even minimal research! Gee, what a waste of time it was to create VIN,
ISBN, UPC, etc. codes! Of course it has no data integrity, but boy it
was quick!

Since autoincrement numbers cannot be validated or verified in the data
model, you would NEVER use them in an RDBMS. They are exposed physical
locators, usually an attmept to mimic the record numbers from a file
model.

[David W. Fenton]

"--CELKO--" <jc*******@earthlink.net> wrote in
news:11*********************@z14g2000cwz.googlegro ups.com:

You are giving the worst possible advise to the Newbie.

Oh, shut up, Celko.

You definitely know your stuff, but you are vastly impractical in
your advice.

--
David W. Fenton http://www.bway.net/~dfenton
dfenton at bway dot net http://www.bway.net/~dfassoc

[David Schofield]

On Wed, 13 Jul 2005 02:03:51 GMT, "David W. Fenton"
<dX********@bway.net.invalid> wrote:

"--CELKO--" <jc*******@earthlink.net> wrote in
news:11*********************@z14g2000cwz.googlegr oups.com:

You are giving the worst possible advise to the Newbie.

Oh, shut up, Celko.

You definitely know your stuff, but you are vastly impractical in
your advice.

--

Hi
Its a good job Chris Date doesn't read this group.

Personally I do everything in code in stored procedures and use things
like mdiff as much as possible in my SQL. I'm just waiting for
everyone to update to the post-relational hierarchical model.

Just kidding!
David

[--CELKO--]

So why are so many of my consulting jobs doing clean up work on RDBMS
projects done in the manner you described:) I guess when the auditors
show up or the data integrity is gone or the whole thing falls apart,
the owners are willing to be "vastly impractical" and follow good
practices and standards.

[AK]

>>So why are so many of my consulting jobs doing clean up work on RDBMS
projects done in the manner you described:
<<

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

[Gene Wirchenko]

On 13 Jul 2005 14:59:11 -0700, "AK" <AK************@hotmail.COM>
wrote:

So why are so many of my consulting jobs doing clean up work on RDBMS

projects done in the manner you described:

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it.

Sincerely,

Gene Wirchenko

[Paul]

Gene Wirchenko <ge***@ucantrade.com.NOTHERE> wrote:

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it.
Maybe, however that assumes

- excellent developers who understand (truly understand) databases,

- DBA's who are willing to go the extra mile to help the developers,

- no project managers on the dev team's back screaming "Just fix bug X
and get it out to *_my_* client who found it",

- no programmer who responds "Well, I can do it this way, but it's a
bit of a kludge, or the right way, which will take a wee bit longer",

- no manager who responds "I don't give a flying f***, just get it
out!",

- no programmer who writes said kludge and then in the white hot heat
of the "get it out at all costs" mentality forgets to document said
kludge,

- no programmer leaving after writing said kludge,

- said kludge never being touched again, until it is found that the
system can cope with 10 users, but when it's up to 100 it's a
different story

&c., &c., .........
Paul...

Gene Wirchenko

--

plinehan __at__ yahoo __dot__ __com__

XP Pro, SP 2,

Oracle, 9.2.0.1.0 (Enterprise Ed.)
Interbase 6.0.1.0;

When asking database related questions, please give other posters
some clues, like operating system, version of db being used and DDL.
The exact text and/or number of error messages is useful (!= "it didn't work!").
Thanks.

Furthermore, as a courtesy to those who spend
time analysing and attempting to help, please
do not top post.

[AK]

>> Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it. <<

OK, let us try. Let us be specific.

You are a developer in a small but ambitious startup.
You need to design a table to store traffic incidents, like cars
colliding with other cars, lightpoles, mailboxes, deers, pedestrians
etc. and a child table (or tables) to store parties involved in the
incident, like vehicles, drivvers, passengers, trees etc.

What will be the natural primary key for the Incident table? How much
time did it take you to make your choice?

[David Schofield]

On 12 Jul 2005 16:35:35 -0700, "--CELKO--" <jc*******@earthlink.net>
wrote:

Since autoincrement numbers cannot be validated or verified in the data
model, you would NEVER use them in an RDBMS. They are exposed physical
locators, usually an attmept to mimic the record numbers from a file
model.

Hi
On thinking about this, I don't think it is correct. Autoincrement
numbers are not normally exposed but are used only (through relations
and in SQL) to indicate that two things are the same. (Strictly, that
two expressions co-refer). In that sense the id is just like the bound
variables used in predicate logic,eg
all humans are mortal
=
for all x, if x is human then x is mortal

Used in this way artificial keys (id) are surely harmless, and only
affect the efficiency ot the representation. However to ensure that
the table (net of the id) does not contain two rows which are the
same, the table should always have one other unique index not
involving the id.

Items like VINs are a naming convention. The relation between an
object and its name lies outside normal logic and doesn't yet have any
agreed thoretical basis, in my opinion.

So there! My consultation rates are very modest!
David

[Tim Marshall]

--CELKO-- wrote:

ISO-11179 Standards tells us not to put silly prefixes like "tbl-"
that describe the physical storage into a data element name. Name each
data element for what it is; never for how it is used in one place, how
it is encoded, where it occurs, etc. A data element should have one and
only one name.

What is wrong with "silly little prefixes"? In a development milieu
like Access (whether the engine is Jet, Oracle, SQL SErver or what have
you) it makes it very practical for understanding what is causing errors
when custom or system errors sprout up.

Why not use the prefixes along with what the data element is?

Thanks in advance for any perspective on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Gene Wirchenko]

On Thu, 14 Jul 2005 14:15:41 +0100, Paul <pa**@see.my.sig.com> wrote:

Gene Wirchenko <ge***@ucantrade.com.NOTHERE> wrote:

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it.

Maybe, however that assumes
"however" means "but". Are you seriously arguing against doing
it right?
- excellent developers who understand (truly understand) databases,
Which would be doing it right in the first place.
- DBA's who are willing to go the extra mile to help the developers,
Which would be doing it right in the first place.
- no project managers on the dev team's back screaming "Just fix bug X
and get it out to *_my_* client who found it",
Which would be doing it right in the first place.
- no programmer who responds "Well, I can do it this way, but it's a
bit of a kludge, or the right way, which will take a wee bit longer",
Which would be doing it right in the first place.
- no manager who responds "I don't give a flying f***, just get it
out!",
Which would be doing it right in the first place.
- no programmer who writes said kludge and then in the white hot heat
of the "get it out at all costs" mentality forgets to document said
kludge,
Which would be doing it right in the first place.
- no programmer leaving after writing said kludge,
Which would be doing it right in the first place.
- said kludge never being touched again, until it is found that the
system can cope with 10 users, but when it's up to 100 it's a
different story
Which would be doing it right in the first place.
&c., &c., .........

You have provided a catalog of wrong ways to do it. That does
not excuse them, nor does it make doing it right wrong.

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

On 14 Jul 2005 07:35:28 -0700, "AK" <AK************@hotmail.COM>
wrote:

Maybe, they would do better if they did it right in the first

place. Doing it right does not necessarily mean taking a long time
about it. <<

OK, let us try. Let us be specific.

You are a developer in a small but ambitious startup.
You need to design a table to store traffic incidents, like cars
colliding with other cars, lightpoles, mailboxes, deers, pedestrians
etc. and a child table (or tables) to store parties involved in the
incident, like vehicles, drivvers, passengers, trees etc.

What will be the natural primary key for the Incident table? How much
time did it take you to make your choice?

Probably a lot shorter than the time to do it wrong, clean up the
resulting problems, and redo it later.

If you are not planning to do it right though, you can skip the
third step. If you are really, ah, efficient, you can skip the second
as well.

I understand that trade-offs must sometimes be made, but this
does not mean that they always must be made. A bit of thought (called
"analysis and design") beforehand (before coding) can sometimes
totally avoid the problem area.

Yes, there may be cases when plowing ahead with whatever is the
most viable (Experimenting? Prototyping?), but that should not be the
usual model for software development.

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

On 14 Jul 2005 11:30:02 -0500, sitting@pc (David Schofield) wrote:

[snip]

On thinking about this, I don't think it is correct. Autoincrement
numbers are not normally exposed but are used only (through relations
and in SQL) to indicate that two things are the same. (Strictly, that
two expressions co-refer). In that sense the id is just like the bound
variables used in predicate logic,eg
all humans are mortal
=
for all x, if x is human then x is mortal
A very important difference is the scope. In the above example,
the scope is the one statement. A further statement using x, say
There is an x such that x is a box and x is blue.
is using a different x. This is not so with ids.
Used in this way artificial keys (id) are surely harmless, and only
affect the efficiency ot the representation. However to ensure that
the table (net of the id) does not contain two rows which are the
same, the table should always have one other unique index not
involving the id.

Then why not use that other id?

[snip]

Sincerely,

Gene Wirchenko

[AK]

Gene,
I asked you a very specific question:
"What will be the natural primary key for the Incident table?"

So far you have chosen to deliver yet another sermon rather than to
give a specific answer. Can you give me a concrete answer to my
question?
Just tell me what in your opinion should be the primary key, can you
not?

[Gene Wirchenko]

On 14 Jul 2005 10:52:47 -0700, "AK" <AK************@hotmail.COM>
wrote:

Gene,
I asked you a very specific question:
"What will be the natural primary key for the Incident table?"

So far you have chosen to deliver yet another sermon rather than to
give a specific answer. Can you give me a concrete answer to my
question?
Just tell me what in your opinion should be the primary key, can you
not?

No.

My point is that one should think it over, not rush to come up
with something that can be called a solution, regardless of whether it
actually is one.

I would want to make sure of the facts before giving a solution
that becomes the next problem. There are probably other factors
involved that have not been stated. What external entities will be
wanting to access the data? Are there any applicable laws or other
systems in place that are relevant? You can probably think of other
questions, too.

An offhand "solution" may be worse than no solution.

Sincerely,

Gene Wirchenko

[Tim Marshall]

Gene Wirchenko wrote:

Just tell me what in your opinion should be the primary key, can you
not?

No.

I get the impression you folks lean towards not using a sequence of some
sort for a primary key in something like the traffic incident example.

Why would this be wrong?
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[--CELKO--]

>> Autoincrement numbers are not normally exposed but are used only (through relations and in SQL) to indicate that two things are the same. <<

When I do a SELECT *, do they show up in the rows? Yep! Can I do
queries on them? Yep! That is "exposed".

When I get one of these things used as an entity identifier, can i go
to the reality of the data model and verify it? Nope. I9t is not part
of the reality; it is part of physical storage. That means if the
references get out of synch, I am screwed.

I know what you are trying to say. You want them to be pointer chains,
like the old IDMS or other navigational databases. this avoids
research, thinking and learning the relational model for something
more comfortable.

Used in this way artificial keys (id) are surely harmless, and only affect the efficiency ot the representation.<<
NO. They destroy data integrity, prevetn portability and distribution.
the old pointer chain system you mimic had a lot of stuff under the
covers to maintain the chains; you do not have that with IDENTITY and
other auto-numbering schemes.
Items like VINs are a naming convention. <<
No, I would call them an external industry standard with creation rules
that cannot violation Dr. Codd's Distribution rule. That is, when a
Ford plant creates a VIN, it will be different from a VIN created at a
Nissan plant or another Ford plant. The VIN points to one and only one
vehicle in the universe. How many times do youthinkt hat 42 appears in
ID columns in all the SQL Server databases on the planet?
The relation between an object and its name lies outside normal logic and doesn't yet have any agreed thoretical basis, in my opinion. <<
I would think that is a matter of mappings between sets via an
isomorphism. And three is a lot of math to prove things are
isomorphic.
My consultation rates are very modest! <<

I am a bit pricey compared to an hourly programmer, but MUCH less than
a failed Data Warehouse project :)

Your project can be (1) Done fast (2) Done right (3) Done cheap; Pick
any two! Your other option is (4) Done over again

[--CELKO--]

>> What is wrong with "silly little prefixes"? <<

1) They contain physical information that has nothing to do with the
data model.

2) ISO-11179 says not to and we are professionals who follow standards

3) You mess up the data dictionary with "i_foobar", s_foobar",
d_foobar", retc. when the same data element can have multiple names.
We want one name, one data type, one definition enfoced by consistent
constraints.

I get really get the giggles from "tbl-" because the language only has
one structure. This give zero information. And when I see "vw-", I
think "Volkswagen" and laugh a lttle. Here is someone who does not
know that a view is a virtual table.

Did you ever program in BASIC in the 1960's? The variables were
prefixed (or postfixed) with $ to show they were stirngs. A one pass
interpreter needed those hints to work. FORTRAN II used the letters I
thru N to show a six-oetter name was INTEGER, etc.

Today, we have compiled languages and type declaration instead. Altho
if you look at how people use Dynamic SQL on this newsgroup, you would
not know it. We also work at an abstract level.

Get a copy of SQL PROGRAMMIGN STYLE for some more material and research
on the topic.

[Ed Prochak]

Tim Marshall wrote:

Gene Wirchenko wrote:

Just tell me what in your opinion should be the primary key, can you
not?

No.

I get the impression you folks lean towards not using a sequence of some
sort for a primary key in something like the traffic incident example.

Why would this be wrong?
--
Tim http://www.ucs.mun.ca/~tmarshal/

Because it violates the basic guidelines of Normalization.

The pseudokeys merely transform a perfectly good relational model DB to
what I consider to be a network model DB. You get a false sense of
space savings since those ID's are usually numerics replacing text
data. And if the ID value is wrong, the error is hard to detect and fix
(all the RI tests pass, but the connection is bogus.)

There are times to use such ID fields. (simple example Work Order
Number) and times not to use it. Being aware that ID's as Primary Keys
is generally an idea to avoid.

Ed.

[Ed Prochak]

Hmm, how about Police report number?

Yes there are specific cases where an ID is numeric, BUT using ID's
willy nilly throughout the DB is just using w RDBMS to build a network
model DB.

took be about 3 seconds.

[Tim Marshall]

--CELKO-- wrote:

Nissan plant or another Ford plant. The VIN points to one and only one
vehicle in the universe. How many times do youthinkt hat 42 appears in
ID columns in all the SQL Server databases on the planet?

But why would one be concerned about other databases?

I ask this in another part of this thread - This is an interesting
subject, though I'm not sure what it is you're getting at here with
respect to an identifier and I'm not convinced why an artificial
increment/sequence is a bad thing to use. I see far too many examples
of carefully thought out equipment numbering systems, for example,
changed because some irresponsible manager decides for a particular
place, HIS numbering system is better, and everything must change. I've
seen it happen with accounting systems (components of an cost centre
identification, for example) and people tracking systems (name changes).
In the world of maintaining engines and machinery, for example, with
so many different manufacturers, there simply are no universal
identifiers like a VIN. In the world of registered pressure vessels and
boilers, different jurisdictional authorities issue what are supposed to
be unique identifiers for registered vessels, but they are not always
uniquer (a clerk gets lazy, an iunspector screws up, etc).

In developing applications for some of the examples above, I can't think
of how an immutable identification for each instance/record/whatever of
data is possible - only agreeing to a universal standard would allow
this and in many areas in which I work, this is more troubesome than
having the whole world agree on a single language to be spoken by
earthlings... 8)

It's fine to tell me too bad, there has to be a single source of unique
practical identifiers for entities, but the reality is that it is
impossible. Or is it?

I'd be very interested in seeing any other sources (on line, if at all
possible, please, my job is only partly DB development).

Thanks in advance for any thoughts on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

--CELKO-- wrote:

1) They contain physical information that has nothing to do with the
data model.

2) ISO-11179 says not to and we are professionals who follow standards

3) You mess up the data dictionary with "i_foobar", s_foobar",
d_foobar", retc. when the same data element can have multiple names.
We want one name, one data type, one definition enfoced by consistent
constraints.

Thanks for your response.

But what about components of a table? Surely it helps when prefixes are
used to indicate what table a field comes from. It makes it a heck of a
lot easier to understand what is happening when you're taking over an
application. I am not familiar at all with the above standard. I'm not
at all certain what your point 3 is about.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[Tim Marshall]

Ed Prochak wrote:

There are times to use such ID fields. (simple example Work Order
Number) and times not to use it. Being aware that ID's as Primary Keys
is generally an idea to avoid.

Thanks for the response, Ed.

Your last sentence is something I've always agreed with but something
that I thought some of the fellows in this thread such as Gene (and
elsewhere Celko) were casting out as bad practice.

Are we talking about the same thing? I've always felt it very unwise to
use a physical identifier that the normal user sees and has control over
for a primary key. What happens when an accounting system changes and
Account ABCD is now the same account but called 100001?

In equipment or room labelling, for example (say for rooms/equipment
against which a work order is written), it is very common in every
facility I've ever managed for standards to change, a building name for
example can be changed from "Services" to "Facilities" and the
organization president wants each and every room number to be chnaged
from SV-whatever to FM-whatever. No matter how much rdb theory one can
spout, a developer or support person is not going to change this from
happening.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

[David Schofield]

On Thu, 14 Jul 2005 10:40:41 -0700, in comp.databases.ms-access you
wrote:

On 14 Jul 2005 11:30:02 -0500, sitting@pc (David Schofield) wrote:

[snip]

On thinking about this, I don't think it is correct. Autoincrement
numbers are not normally exposed but are used only (through relations
and in SQL) to indicate that two things are the same. (Strictly, that
two expressions co-refer). In that sense the id is just like the bound
variables used in predicate logic,eg
all humans are mortal
=
for all x, if x is human then x is mortal

A very important difference is the scope. In the above example,
the scope is the one statement. A further statement using x, say
There is an x such that x is a box and x is blue.
is using a different x. This is not so with ids.

I don't think so. The scope of each id is limited to the joins made on
it. Actual values of keys occur nowhere in SQL (or code for that
matter) and if you were to use cascaded updates you could change any
of the key values without having any effect on the system, just as you
could change x to y in the formula.
This was only an analogy. Leda's neck is like the swan, not like the
swan's neck.

Used in this way artificial keys (id) are surely harmless, and only
affect the efficiency ot the representation. However to ensure that
the table (net of the id) does not contain two rows which are the
same, the table should always have one other unique index not
involving the id.

Then why not use that other id?

Don't understand this - what other id?. I just mean that when you a
add an artificial key to a table there is a possibility of generating
two rows which differ only in the key. This shouldn't ever happen as
(i) the relational model doesn't allow two row to be the same (though
many databases do) and (ii) the artificial key isn't really part of
the table,it is only a "helper" so its value should be ignored in
comparing the two rows.

David

[Gord]

Hi, Tim.

I'd be very interested in seeing any other sources

There is a related discussion here

http://thedailywtf.com/forums/38073/ShowPost.aspx
Gord

[Ed Prochak]

This has morphed more into a software engineering issue than a DB
design issue, but here goes -

Paul wrote:

Gene Wirchenko <ge***@ucantrade.com.NOTHERE> wrote:

I guess that's because the startups that try to do it perfect the first
time just don't make it to profitability. Only the ones that take
shortcuts to get things done quickly grow up and eventually can afford
Mr Celko's advice

Maybe, they would do better if they did it right in the first
place. Doing it right does not necessarily mean taking a long time
about it.

Maybe, however that assumes

- excellent developers who understand (truly understand) databases,

just takes some learning. Something most professionals do. Are you
saying you do not understand databses? Are you saying that hiring
engineers that do not understand design concepts in their profession is
justification for bad software systems?

- DBA's who are willing to go the extra mile to help the developers,
that helps, but having EVERYONE working as a team is a better shot at
getting things right.

- no project managers on the dev team's back screaming "Just fix bug X
and get it out to *_my_* client who found it",
They can scream all they want. (personally, I'd reply that that doesn't
make me work any faster, thank you.) It's the developer's
professionalism to point out the costs and benefits. Issuing a
workaround to one customer (if they are unable to proceed at all) while
fixing the real problem is the way to resolve this. (And might I note:
a project manager should not have any customers if the project is
intended for all the customers of the company/department.)

- no programmer who responds "Well, I can do it this way, but it's a
bit of a kludge, or the right way, which will take a wee bit longer",
if it's a wee bit longer, then it's useless to even mention the kludge.
This is just a programmer looking for trouble.

- no manager who responds "I don't give a flying f***, just get it
out!",
Decent developers will get it out without the kludges. Hard deadlines
are not an excuse to do crap work.

- no programmer who writes said kludge and then in the white hot heat
of the "get it out at all costs" mentality forgets to document said
kludge,
not even time for an email to himself and a few knowledgable teammates?
with one line saying "fix the kludge in source file xxx.c"?

- no programmer leaving after writing said kludge,
With managers like that no wonder he left! 8^)
- said kludge never being touched again, until it is found that the
system can cope with 10 users, but when it's up to 100 it's a
different story
The the kludge comes out in the open and the cost benefits discusion
comes back. (the engineer quietly says "I told you so" to himself and
smiles)

&c., &c., .........
Paul...

there are lots of excuses for doing it wrong, but they are just that,
Excuses.

Ed

"an engineer is someone who can do for one dollar,
what any dam fool can do for two" - Wellington

[Ed Prochak]

Tim Marshall wrote:

--CELKO-- wrote:

Nissan plant or another Ford plant. The VIN points to one and only one
vehicle in the universe. How many times do youthinkt hat 42 appears in
ID columns in all the SQL Server databases on the planet?
But why would one be concerned about other databases?

because systems communicate data? Why one trucking company sells some
trucks to another one, do you think it would be nice to transfer the
service history data? Oh but wait, the developers at the seller company
thought they could store the truck info under an autoID, and the VIN
information happens to be in another table and the joins got messed up
on the dump file. So all teh wrong VIN got sent over. The buying
company is suing because they cannot get registrations completed in a
timely manner.


I ask this in another part of this thread - This is an interesting
subject, though I'm not sure what it is you're getting at here with
respect to an identifier and I'm not convinced why an artificial
increment/sequence is a bad thing to use. I see far too many examples
of carefully thought out equipment numbering systems, for example,
guess what, this is the case where it makes sense. Your company builds
a product and they are all the same except for the (gasp!) serial
number!
Here it adds information to the item. Note this isn't always the case.
It doesn't pay to assign serial numbers to copies of a book for
example.
changed because some irresponsible manager decides for a particular
place, HIS numbering system is better, and everything must change.
This just demonstrates that the numbering scheme is a pseudokey.
... I've
seen it happen with accounting systems (components of an cost centre
identification, for example) and people tracking systems (name changes).
I don't get the last example. Maybe you mean the name columns were
changed (e.g. one "wholename" column to a "lastname" and "firstname"
pair of columns.
In the world of maintaining engines and machinery, for example, with
so many different manufacturers, there simply are no universal
identifiers like a VIN. Manufaturer, product code and serial number gets pretty close.
... In the world of registered pressure vessels and
boilers, different jurisdictional authorities issue what are supposed to
be unique identifiers for registered vessels, but they are not always
uniquer (a clerk gets lazy, an iunspector screws up, etc).
Again a case against pseudokeys. the fact that somebody else's
pseudokey is broken isn't a justification for creating your own.

In developing applications for some of the examples above, I can't think
of how an immutable identification for each instance/record/whatever of
data is possible - only agreeing to a universal standard would allow
this and in many areas in which I work, this is more troubesome than
having the whole world agree on a single language to be spoken by
earthlings... 8)
So you at least agree the pseudo key in this case is a workaround.

It's fine to tell me too bad, there has to be a single source of unique
practical identifiers for entities, but the reality is that it is
impossible. Or is it?
But to fix it do you need to make a single column ID as the PK?
I think you's be better served with a combined key with that ID column
serving to break the tie, so to speak.

I'd be very interested in seeing any other sources (on line, if at all
possible, please, my job is only partly DB development).

Thanks in advance for any thoughts on this.
--
Tim http://www.ucs.mun.ca/~tmarshal/

HTH,
Ed

[Ed Prochak]

David Schofield wrote:

On Thu, 14 Jul 2005 10:40:41 -0700, in comp.databases.ms-access you
wrote:

On 14 Jul 2005 11:30:02 -0500, sitting@pc (David Schofield) wrote:

[snip]

On thinking about this, I don't think it is correct. Autoincrement
numbers are not normally exposed but are used only (through relations
and in SQL) to indicate that two things are the same. (Strictly, that
two expressions co-refer). In that sense the id is just like the bound
variables used in predicate logic,eg
all humans are mortal
=
for all x, if x is human then x is mortal
A very important difference is the scope. In the above example,
the scope is the one statement. A further statement using x, say
There is an x such that x is a box and x is blue.
is using a different x. This is not so with ids.

I don't think so. The scope of each id is limited to the joins made on
it. Actual values of keys occur nowhere in SQL (or code for that
matter) and if you were to use cascaded updates you could change any
of the key values without having any effect on the system, just as you
could change x to y in the formula.

but nothing in SQL prevents
select * from accounts, animals
where accounts.id=animals.id and accounts.is=x

effectively creating an account for my new cat.
This was only an analogy. Leda's neck is like the swan, not like the
swan's neck.
we aren't talking poetry here, but technology.

Used in this way artificial keys (id) are surely harmless, and only
affect the efficiency ot the representation.

Why do supporters of pseudokeys always mention "efficiency"? It really
isn't saving you anything in a RDBMS except a few bytes of space. An
SSN still must scan the index and then fetch from teh table internally.
The person's name takes the same route, so it doesn't save time. It
just makes people slaves to teh system because they must remember
account numbers from the bank, the videostore, the grocery store, each
one different.

... However to ensure thatthe table (net of the id) does not contain two rows which are the
same, the table should always have one other unique index not
involving the id.
Then why not use that other id?

Don't understand this - what other id?. I just mean that when you a
add an artificial key to a table there is a possibility of generating
two rows which differ only in the key.

In other words, you really don't have the key do you?
... This shouldn't ever happen as
(i) the relational model doesn't allow two row to be the same (though
many databases do)
More specifically the PK must be unique, no matter what the rest of the
data is. This rule fails, as you agree (in item ii) when using
pseudoKey ID columns.
... and (ii) the artificial key isn't really part of
the table,it is only a "helper" so its value should be ignored in
comparing the two rows.
IOW the artifical key isn't the key. That's the whole point. When the
key contributes nothing to the information, don't use it. If it does
contribute something to the information, then it is useful. It's just
that most times it doesn't contribute anything.

David

HTH,
ed

[Gene Wirchenko]

On Thu, 14 Jul 2005 16:08:24 -0230, Tim Marshall
<TI****@PurplePandaChasers.Moertherium> wrote:

Ed Prochak wrote:

There are times to use such ID fields. (simple example Work Order
Number) and times not to use it. Being aware that ID's as Primary Keys
is generally an idea to avoid.
Thanks for the response, Ed.

Your last sentence is something I've always agreed with but something
that I thought some of the fellows in this thread such as Gene (and
elsewhere Celko) were casting out as bad practice.

I agree with Ed's statement. Simply automatically running to an
id column is not a good idea.

One problem with id columns is that there is an additional layer
of indirection. If you ever have to repair a database, you have to
translate every id into something Real World in order for the end user
to understand you. "Is this associated with client Smith or client
Jones?" is much easier than ". . . client 14972 or client 17887?"
(This is assuming that clients are usually referred to by the former.)
Are we talking about the same thing? I've always felt it very unwise to
use a physical identifier that the normal user sees and has control over
for a primary key. What happens when an accounting system changes and
Account ABCD is now the same account but called 100001?

In equipment or room labelling, for example (say for rooms/equipment
against which a work order is written), it is very common in every
facility I've ever managed for standards to change, a building name for
example can be changed from "Services" to "Facilities" and the
organization president wants each and every room number to be chnaged
from SV-whatever to FM-whatever. No matter how much rdb theory one can
spout, a developer or support person is not going to change this from
happening.

This keeps getting hauled out. How common is it, really? And
how difficult is it to update the table? Something like:
update rooms set roomnbr="FM-"+substr(roomnbr,4)
where left(roomnbr,3)="SV-"
Assuming you have cascading updates, that would do it.

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

On 14 Jul 2005 14:47:01 -0500, sitting@pc (David Schofield) wrote:

On Thu, 14 Jul 2005 10:40:41 -0700, in comp.databases.ms-access you
wrote:

[Gene Wirchenko:]

On 14 Jul 2005 11:30:02 -0500, sitting@pc (David Schofield) wrote:
[snip]

for all x, if x is human then x is mortal

A very important difference is the scope. In the above example,
the scope is the one statement. A further statement using x, say
There is an x such that x is a box and x is blue.
is using a different x. This is not so with ids.

I don't think so. The scope of each id is limited to the joins made on
it. Actual values of keys occur nowhere in SQL (or code for that

Which is more than the row the id appears in. In the above, x
has no meaning outside of the statement.

[snip]

Sincerely,

Gene Wirchenko

[David Schofield]

On 14 Jul 2005 11:08:42 -0700, "--CELKO--" <jc*******@earthlink.net>
wrote:

Autoincrement numbers are not normally exposed but are used only (through relations and in SQL) to indicate that two things are the same. <<
When I do a SELECT *, do they show up in the rows? Yep! Can I do
queries on them? Yep! That is "exposed".
No,you are falling into the trap you blame others for, of confusing
the implementation with the design. Tables and SELECT statements are
only an internal mechanism of the software. If there is no way that
the value of an artifical key can influence the behaviour of the
application, then the value is not exposed.
I know what you are trying to say. You want them to be pointer chains,
like the old IDMS or other navigational databases. this avoids
research, thinking and learning the relational model for something
more comfortable.
No idea what this is about.

Used in this way artificial keys (id) are surely harmless, and only affect the efficiency ot the representation.<<
NO. They destroy data integrity, prevetn portability and distribution.
the old pointer chain system you mimic had a lot of stuff under the
covers to maintain the chains; you do not have that with IDENTITY and
other auto-numbering schemes.

Phooey. USED IN THIS WAY I could write a program which mechanically
translates everything into a system with no artificial keys. Items like VINs are a naming convention. <<
No, I would call them an external industry standard with creation rules
that cannot violation Dr. Codd's Distribution rule. That is, when a
Ford plant creates a VIN, it will be different from a VIN created at a
Nissan plant or another Ford plant. The VIN points to one and only one
vehicle in the universe. How many times do youthinkt hat 42 appears in
ID columns in all the SQL Server databases on the planet?
The relation between an object and its name lies outside normal logic and doesn't yet have any agreed thoretical basis, in my opinion. <<
I would think that is a matter of mappings between sets via an
isomorphism. And three is a lot of math to prove things are
isomorphic.

An object can have none or many names. Objects without names have to
be referred to via descriptions. A definite description is one which
refers to just one object. Some descriptions apply to no objects.
In real life the same name can refer to many objects.
Most problems with naming don't really hit extensional systems so I
was out of order mentioning it.

My consultation rates are very modest! <<

I am a bit pricey compared to an hourly programmer, but MUCH less than
a failed Data Warehouse project :)

Your project can be (1) Done fast (2) Done right (3) Done cheap; Pick
any two! Your other option is (4) Done over again

[AK]

>>I get the impression you folks lean towards not using a sequence of some
sort for a primary key in something like the traffic incident example.
<<

no, no, not that. In fact I've seen a team trying hard to come up with
a feasible natural PK for a traffic incident table in a reasonable
time, involving experts etc.
They ended up making a compromise, a surrogate key. Just because for
every reasonable candidate for a PK there was a rare but possible real
life situation where such a PK would be violated.

In theory, there is no difference between theory and practice.
Yet in practice there is...

[AK]

>> They destroy data integrity, prevetn portability and distribution. <<

why are you assuming that portability is a requirement at all in this
particular situation?

Fully portable applications are dull and less performant than the ones
using vendotr specifi features, just as a comics book based on
Shakespear's play is dull and poor in content when compared to the
original play

[AK]

>> The pseudokeys merely transform a perfectly good relational model DB to
what I consider to be a network model DB. You get a false sense of
space savings since those ID's are usually numerics replacing text
data <<

"FALSE sense of space savings"? Just consider a child table with 30
byte natural FK column and 40 bytes of other data. Switching to a 4
byte integer for a surrogate key will lead to 25% space saving in the
table, and 700% space saving for the index. I would not scorn at that
much savings. Would you?

[David Schofield]

On 14 Jul 2005 14:01:06 -0700, "Ed Prochak"
<ed********@magicinterface.com> wrote:
<big snip>

IOW the artifical key isn't the key. That's the whole point. When the
key contributes nothing to the information, don't use it. If it does
contribute something to the information, then it is useful. It's just
that most times it doesn't contribute anything.

Well. I agreed that the artifical key isn't the key and doesn't form
part of the information. But if it contributes something to the whole
process of delivering the application, and the way I use it is
harmless, why shouldn't I use it?

By the way, which DBMS do you use? Ask them whether they have paid any
attention to efficiency in executing SQL.

We will just have to agree to differ.
David

[AK]

>> Nissan plant or another Ford plant. The VIN points to one and only one

vehicle in the universe. How many times do youthinkt hat 42 appears in
ID columns in all the SQL Server databases on the planet?

But why would one be concerned about other databases?

for instance, imagine a merger or an acquisition. Such things do
happen.
Yet they are usually to expensive to consider for a startup or a rapid
temporary project

[--CELKO--]

>> I'm just waiting for everyone to update to the post-relational hierarchical model. <<

LOL! That should be "the post-relational hierarchical model .. du
jour"!

Actually, RDBMS seems to beat out all the contenderes in that
"structured data" niche after all these decades. Implementations get
better for sub-niches -- streaming data, small DB, VLDB, warehousing,
etc. but the basic model holds true. But I admit there are other kinds
of data.

I am the guy who gets funny looks on consulting jobs for NOT
recommending SQL. They hire me as the "SQL Guru / savior / "Western
sheriff who will clean up the town [database] that has been overrun by
cowboys [application programmers]" and then I come up with a different
solution.

Example: a major utilty company in Southern California wanted to put
its labor relations library into the DB2 they had just bought. I
showed them that (1) it would not work (2) they could scan in all the
Union contracts, labor laws, memos, agreements, etc. to an Optical disk
in a few months with temp help, buy a copy of ZyIndex for a five user
server and have an inerface their lawyers understood from WestLaw and
Lexis training, and pocket over $100,000 the first year and about $50K
every year thereafter.

I also recommended upgrading IMS over SQL for a banking application
that will never break a particular hierarchy.

[AK]

>> This keeps getting hauled out. How common is it, really? And
how difficult is it to update the table? Something like:
update rooms set roomnbr="FM-"+substr(roomnbr,4*)
where left(roomnbr,3)="SV-"
Assuming you have cascading updates, that would do it.
<<

beleive me or not, this may require a very big maintenance window,
might not fit into a long July 4th weekend. Even if you replace
non-sargeable
left(roomnbr,3)="SV-"
with sargeable
roomnbr like 'SV-'
which will dramatically speed it up under most circumstances ;)

in real life I would avoid such a scenario

[Rick Brandt]

AK wrote:

The pseudokeys merely transform a perfectly good relational model
DB to

what I consider to be a network model DB. You get a false sense of
space savings since those ID's are usually numerics replacing text
data <<

"FALSE sense of space savings"? Just consider a child table with 30
byte natural FK column and 40 bytes of other data. Switching to a 4
byte integer for a surrogate key will lead to 25% space saving in the
table, and 700% space saving for the index. I would not scorn at that
much savings. Would you?

This is where I see a problem with the "purist" mind-set. If I have a table
and I determine that I will need 10 "natural" fields to create a valid
Primary Key and I now need a table with a one-to-many relationship to store
one additional entity. Would the natural key purists really build a table
consisting of 11 fields having 10 of those fields be foreign keys and only
one field of actual data? That just seems ludicrous on the face of it.

Even if I took the view of "storage space be damned, it's practically free
now anyway" won't this lead to queries with incredibly complex joins since I
have to deal with so many fields making up the relationship?

Some have argued that you should use the natural fields for the PK and then
add an identity type field only to be used for the foreign key in the
related table. Does anyone see merit in that? I'm accustomed to having the
FK in the child table be the PK in the parent table, but I suppose that is
not a requirement (right?).

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com

[David Schofield]

On Thu, 14 Jul 2005 14:08:50 -0700, Gene Wirchenko
<ge***@ucantrade.com.NOTHERE> wrote:

I don't think so. The scope of each id is limited to the joins made on
it. Actual values of keys occur nowhere in SQL (or code for that

Which is more than the row the id appears in. In the above, x
has no meaning outside of the statement.

The analogy for the logic statement is the SQL statement, not the row!

David

[--CELKO--]

>> But why would one be concerned about other databases? <<

Data exchange. Tracking history. Validation of data by external
sources.

I see far too many examples of carefully thought out equipment numbering systems, for example, changed because some irresponsible manager decides for a particular place, HIS numbering system is better, and everything must change. <<
two of my favorite quotes:

"Caesar: Pardon him, Theodotus. He is a barbarian and thinks the
customs of his tribe and island are the laws of nature." - Caesar and
Cleopatra; George Bernard Shaw 1898

"Against stupidity the gods themselves struggle in vain." - Die
Jungfrau von Orleans; Friedrich von Schiller (1759-1805)

This is why we need the VIN, ISBN, and industry level standard
identifiers. That is why I get invovled with ANSI, ISO and whatever
other group applies. It is also why I spent two chapters on how to
design encoding in two of my books.

As an aside, I thought that boilers were very standardized and the
specs put on a metal plate on boilers after so many of them exploded in
the early industrial revolution days -- hence the slang term "boiler
plate" to refer to any required specs. Is this an Urban Myth?
I can't think of how an immutable identification for each instance/record/whatever of data is possible - only agreeing to a universal standard would allow this and in many areas in which I work, this is more troubesome than having the whole world agree on a single language to be spoken by earthlings... 8) <<

1) Other industries have done this -- UPC in retail, VIN in automotive,
ISBN in publishing, and about hundred thousand others. It an be done!!

2) English or Esperanto . Anything but French; they deserve to be
punished for screwing up Standards :)

[AK]

>> This is where I see a problem with the "purist" mind-set. If I have a table
and I determine that I will need 10 "natural" fields to create a valid
Primary Key and I now need a table with a one-to-many relationship to
store
one additional entity. Would the natural key purists really build a
table
consisting of 11 fields having 10 of those fields be foreign keys and
only
one field of actual data? That just seems ludicrous on the face of it.

<<

Rick, I have seen just that - a child table with a PK composed of more
than 10 columns. The reason I have seen that system is simple: the
system grinded to a halt when it exceeded 5K (five thousand) rows,
despite all the buzzwords about "fully normalized data structure",
"using the best practices in database design" etc.

[--CELKO--]

>> Surely it helps when prefixes are used to indicate what table a field [sic] comes from. <<

Files are not columns; tables are not files. The "unit of work" is the
ENTIRE schema, not one file mounted on a tape drive. The data model is
bigger than, actually. it can go across many database schemas. In
SQL, if you need to know where that darta element occurs, then use
<table name>.<column name> syntax. Do you become "LivingRoom_Tim" then
"BedRoom_Tim" then "BathRoom_Tim" as you move from room to room [table
to table] in your house [DB Schema]? Silly, unh? Illogical? See the
point.

Okay, this is a bit harder. Do you know what a data dictionary is and
how to do use it to write SQL? A lot newbie do not and never build
one.

[David Schofield]

On 14 Jul 2005 14:51:54 -0700, "--CELKO--" <jc*******@earthlink.net>
wrote:

As an aside, I thought that boilers were very standardized and the
specs put on a metal plate on boilers after so many of them exploded in
the early industrial revolution days -- hence the slang term "boiler
plate" to refer to any required specs. Is this an Urban Myth?

Hi
Many years ago a colleague of mine at the NPL went to a meeting of the
British Standards Institution where BSI described the results of a big
streamlining of the multiplicity of standards committees. They had
chosen boilers as their pilot project. They proudly announced that
they had managed to reduce the number of standards committees dealing
with boilers to 15.
Maybe it is different now.
David

[Gene Wirchenko]

On 14 Jul 2005 16:41:02 -0500, sitting@pc (David Schofield) wrote:

On Thu, 14 Jul 2005 14:08:50 -0700, Gene Wirchenko
<ge***@ucantrade.com.NOTHERE> wrote:

I don't think so. The scope of each id is limited to the joins made on
it. Actual values of keys occur nowhere in SQL (or code for that

Which is more than the row the id appears in. In the above, x
has no meaning outside of the statement.

The analogy for the logic statement is the SQL statement, not the row!

Ah, but the x in the logic statement appears nowhere else,
whereas the one in the SQL statement is also in the table.

Sincerely,

Gene Wirchenko

[Gene Wirchenko]

On 14 Jul 2005 14:19:38 -0700, "AK" <AK************@hotmail.COM>
wrote:

They destroy data integrity, prevetn portability and distribution. <<

why are you assuming that portability is a requirement at all in this
particular situation?

Because with a little more care, you can make the system much
more useful. These days with the Web, old apps are being adjusted to
run on the Web.
Fully portable applications are dull and less performant than the ones
using vendotr specifi features, just as a comics book based on
Shakespear's play is dull and poor in content when compared to the
original play

Being locked in to one vendor is great, eh?

I do not agree.

Sincerely,

Gene Wirchenko