Data Primary key vs. Artificial (Autonumber) primary key

(psst, Fred, can you say New Madrid?)

Jeff
"Fred" wrote in message
...
I'm from Chicago, we don't know what fault lines are. Could you use a
tornado analogy so that I can understand?

:-)


Fred


"John W. Vinson" wrote:

On Wed, 5 Dec 2007 05:18:01 -0800, Fred
wrote:

And that means at least occasionally correcting or
changing it.

well... there *is* Cascade Updates to cover that possibility.

But I agree, it's ideal if the primary key is rock solid stable. If it's
only
San Andreas faultline rock solid stable, you need to depend on cascade
updates, which do work but have their own problems!

John W. Vinson [MVP]

On Dec 5, 10:57 pm, Fred wrote:
But I agree, it's ideal if the primary key is rock solid stable. If it's only
San Andreas faultline rock solid stable, you need to depend on cascade
updates, which do work but have their own problems!

I'm from Chicago, we don't know what fault lines are. Could you use a
tornado analogy so that I can understand?

I'm from the UK, where both weather and geology is relatively benign.
So that we all may understand the point, can you please use an example
(in place of an analogy) of an industry standard key with a trusted
source which manages changes. As a suggestion, consider ISBN which
recently changed from 10 to 13 characters (which Amazon and many
public libraries seem to have survived g) and for which duplication
occasionally occurs.

Jamie.

--

On Dec 5, 10:11 pm, M. wrote:
Thanks for this helpful answer.

Is the following summary correct:

The (unique) index that would be used most frequently for sorting or
filtering the table records, should be defined as primary index from a
performance point of view, because the primary index determines the
sequential physical order on harddisk ?

Not exactly. To repeat: "This allows Microsoft Jet to take full
advantage of its read-ahead cache and also reduces disk I/O when doing
sequential scans of a table." The best example I can think of for
sequential scans of a table is using BETWEEN in SQL because the data
would be on contiguous pages. Tthink of a paper copy telephone
directory ordered by last name then first names and I asked you to get
me all the telephone numbers of people whose last names begin with the
letter 'C' and how easy it would be to rip out the appropriate pages,
then think about how difficult it would be to do the same if the task
was to get all the telephone numbers which began with the numeral '5'.
The next best example would be GROUP BY.

I suggest you do some research on clustered indexes *generally*; you
won't find much in the Jet world because most users disregard or are
unaware of (or even are in denial of g) the effects of clustering in
Jet. David W. Fenton's suggestion of looking at SQL Server (where you
have an explicit choice of clustered index) seems a good one. Which
reminds me, David came up with the brilliant suggestion of making a
random Autonumber column the sole PK to favour concurrency (because
values created close together in transaction time have more chance of
appearing on *different* pages).

Jamie.

--

On Dec 6, 2:57 am, "David W. Fenton"
wrote:
=?Utf-8?B?TS4=?= wrote :

Have you tried it? For instance, try creating a join between two
tables where the key they are joined on has 5 fields in it.

Multi-column keys are a horrid amount of work, and duplicate a
helluva lot of data.

Isn't this the problem Access Relationships were invented to solve?
i.e. defining the Relationship on those five columns is a one-time
task, after which you simply add the tables to the query builder tool
thing and it types out the JOIN syntax for you. Me, I can type fast
(and the parser does spell chekcing for me g) so I don't really mind
the 'hard' work. And if you are concerned about disk space, port to a
back end without a 2GB (or whatever) limit and buy a bigger drive
(relatively inexpensive these days).

Using meaningless keys in your tables results in many table joins to
get basic key data so you have to balance these things out. I'm wary
of people who use the same hammer to bash every kind of fixing g.

Last of all, very few of the tables in the apps I'm asked by clients
to build can have no Nulls, so natural keys are only usable if you
assign a default value.

I also have only a few nullable columns (subatomic elements, usually
of type DATETIME) and absolutely zero columns that allow a zero length
string but still your point alludes me. I suspect the answer is that
you are have to denormalized tables but could you please clarify with
an example? TIA.

Jamie.

--

On Dec 5, 4:48 pm, Dennis wrote:
I was speaking from a pure theory perspective, not from an Access-specific one.

I get into trouble myself for doing that g.

OLE objects aren't "data" per se; they're, well, OBJECTS.

LOL! Did I interpret the word 'data' too literally g? Is OLEOBJECT a
*data* type? Can columns of type OLEOBJECT be found in a Jet
'database'?

And memo fields
cannot be used as indexes or searched on

I don't know what you mean but memo fields can be indexed and can be
used in WHERE and HAVING clauses; you can cast them using CSTR() in
SQL code to prevent them from being truncated in certain
circumstances.

Jamie.

--

Jamie,

You forgot to mention the penultimate phone number change that meant phone
numbers would never have to change again - that is until the next phone
number change about four years later. :-)

Let me nail my colours to the mast; I belong to the religious sect of a
'meaningless' pk. I believe the job of a pk is to identify the row and
nothing more and I've the dog collar to demonstrate my faith - well that is
until I come across a situation where to include a meaningless key simply
doubles the effort such as an accounting period table that I key with yyyymm
- surely there will never be another Pope Gregory!

As for 'Rock Solid:' I'm in the Philippines and we experienced an earthquake
last week so geological analogies do not work here.

Regards,

Rod

On Dec 6, 12:17 pm, Rod Plastow
wrote:
Let me nail my colours to the mast; I belong to the religious sect of a
'meaningless' pk. I believe the job of a pk is to identify the row and
nothing more and I've the dog collar to demonstrate my faith

I'm borderline 'atheist' because I don't use the PRIMARY KEY on every
table, and I have a relationally sound basis for doing so because a
key is a key is a key; calling one 'primary' is arbitrary.

But really it has nothing to do with 'religion': PRIMARY KEY behaves
the same in Jet regardless of your beliefs. So let's stick to the
facts.

What does PRIMARY KEY do for you that other keys ('non-primary keys'?)
do not? Doesn't allow the NULL value but I've got NOT NULL at the
column level for that. Let's you omit the PK columns in the RFERENCES
clause in SQL DDL but I prefer to write things out longhand. Puts the
columns in bold text in the Access UI but I don't look at the
Relationships picture (I find SQL code easier to interpret). So
clustering is the only aspect of PRIMARY KEY which I find appealing
for Jet. But try telling someone to use NOT NULL UNIQUE for their
existing PKs and leveraging the clustering nature of PRIMARY KEY as a
'physical' index and in most cases it's too much of a paradigm shift
for them to comprehend.

Jamie.

--

Can someone tell me what this mere mortal is missing regarding how to look at
these long threads? The summary page says 40 posts (which looks correct)
but a look at the posts (2 pages with only one "more" between them) only
shows 30, and some that I know were there before are gone.

Fred

"Jamie Collins" wrote:

On Dec 4, 9:15 pm, "Jeff Boyce" wrote:

Calling all Jeff Boyces...

BlueClaw setup for Employee table
Employee_ID (autonumber, unique index)

Why? By definition, an Autonumber is supposed to already be unique, so

An Autonumber is supposed to auto-generate a value according to an
algorithm; the choices are increment, random or GUID. Show me the
section of the Jet specification which says Autonumber is supposed to
be unique. If you like I can post some code to demonstrate the fact
that Jet can auto-generate duplicate Autonumber values (hint: you
change increment value from the default value of one to a value very
close to the maximum for Long Integer).

you
wouldn't gain anything by indexing it.

Can you say "data integrity"? what makes you think that values in an
Autonumber column will be unique unless you put a unique index (or
constraint) on it? Again, I can post code to explicitly insert
duplicate values into an Autonumber column if you like.

Jamie.

--

That's about 325 miles away. Although the shocks will travel farther in
Midwest geology, that's still like another country to us.

:-)

Fred

"Jeff Boyce" wrote:

(psst, Fred, can you say New Madrid?)

Jeff
"Fred" wrote in message
...
I'm from Chicago, we don't know what fault lines are. Could you use a
tornado analogy so that I can understand?

:-)


Fred


"John W. Vinson" wrote:

On Wed, 5 Dec 2007 05:18:01 -0800, Fred
wrote:

And that means at least occasionally correcting or
changing it.

well... there *is* Cascade Updates to cover that possibility.

But I agree, it's ideal if the primary key is rock solid stable. If it's
only
San Andreas faultline rock solid stable, you need to depend on cascade
updates, which do work but have their own problems!

John W. Vinson [MVP]

On Dec 6, 3:11 pm, Fred wrote:
Can someone tell me what this mere mortal is missing regarding how to look at
these long threads?

This is what I see:

http://groups.google.com/group/micro...1e479a6765df2/

Jamie.

--