Primary Keys

"polite person" wrote in message
...
On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship"
wrote:

snip

If my Access databases were large enough that indexing were an issue, they
wouldn't be in Access.

snip
I don't want to intrude in other people's point scoring but newbies might
read this.
Indexing is essential in most Access databases. I think you probably mean
"the efficiency of the
indexing."

Agreed. However, for most newbies and even many more advanced users, the
indexing Access does on its own is sufficient.

Also the effectiveness of Access as against other dbs depends on other
things besides size, as a
matter of fact Access can be used for pretty big databases.

Sure, but if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Amy Blankenship wrote:

as a
matter of fact Access can be used for pretty big databases.

Sure, but if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

What irresponsible nonsense. Simply setting up a field with an index is
"squeezing"?. And minute performance? Perhaps with a couple of tables
of 1 to 10 records each... I suggest you do some development in the
real world and see how much difference there is in indexed joins versus
unindexed join fields. It's ridiculously easy to do this in "Access"
compared to say, Oracle. But in either platform, indexing is a simple
and very basic design principle.

No amount of rationalization for **** poor design someone might have
done in the past will change that fact.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

"Jamie Collins" wrote in
ups.com:

David W. Fenton wrote:
Dates are very seldom going
to be part of a natural key, at least not for very many types of
entities.

It seems I was wrong then and you haven't had the epiphany yet.

Stop thinking in terms of PRIMARY KEY as being your primary key,
candidate key, natural key, etc because you can use NOT NULL
UNIQUE for those purposes. For Jet you must think in terms of
PRIMARY KEY meaning clustered index and nothing else, then choose
whatever columns makes sense in that context.

I think you're mis-using your RDBMS if you treat it that way.

I think your suggestion is bloody stupid, as it optimizes
something that very seldom needs further optimization in the
first place.

The important word there is 'seldom'. If 'optimization' is the
*only* thing that differentiates PRIMARY KEY from NOT NULL UNIQUE
then why use PK for any other purpose?

Why would you think I believe that that's the only difference? I
certainly don't think so, and have never suggested as much.

I'm not going to take the time to enumerate the many differences, as
you just seem fixated on one subject, a very impractical and
ridiculous idea, it seems to me.

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

"Jamie Collins" wrote in
ups.com:

Amy Blankenship wrote:
If it's that important to you, Access is probably the wrong
database to be using. If, on the other hand, you use your PK's
to establish relationships, then physical location on disk is
unimportant.

Did you read the OP's post? They asked (paraphrasing), if I can
use NOT NULL UNIQUE to define my relationships, what do I need
PRIMARY KEY for?

No, you're completely wrong -- that isn't by any stretch of the
imagination the question the OP asked.

The poster simply asked about the pros and cons of surrogate vs.
natural keys.

. . . However, If you do use regular indexes yet don't consider
the indexing aspect of PRIMARY KEY then maybe it's time to
reassess you indexing strategy.

Primary keys are important beyond their indexing and it's bloody
stupid to designate an index as a PK just so you get a clustered
index if it's *not* best candidate for the PK.

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

"Amy Blankenship" wrote in
:

. . . if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just fine,
but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It only
falls down in comparison to other databases in terms of handling
large numbers of simultaneous users and in terms of the size of the
data store.

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

Whoah, whoah. Hang on. I was referring to the topic of this part of the
thread, which was the use of primary keys that are not unique identifiers of
the record in order to cluster them on disk, NOT simply indexing.

"Tim Marshall" wrote in message
...
Amy Blankenship wrote:

as a
matter of fact Access can be used for pretty big databases.

Sure, but if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

What irresponsible nonsense. Simply setting up a field with an index is
"squeezing"?. And minute performance? Perhaps with a couple of tables of
1 to 10 records each... I suggest you do some development in the real
world and see how much difference there is in indexed joins versus
unindexed join fields. It's ridiculously easy to do this in "Access"
compared to say, Oracle. But in either platform, indexing is a simple and
very basic design principle.

No amount of rationalization for **** poor design someone might have done
in the past will change that fact.
--
Tim http://www.ucs.mun.ca/~tmarshal/
^o
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto "TIM-MAY!!" - Me

Hey, hang on here. I never was referring to simple indexing as the
squeezing of performance. I was referring to the bizarre suggestion of
Jamie Collins that people pick primary keys not for their uniqueness but to
physically cluster the records on disk. This is a practice you, yourself
have taken issue with. Let me completely eliminate any possibility of
misunderstanding here for those unable to take things in context:

If you are so concerned with performance that you are picking your primary
key in order to physically cluster the records, you need to be using a
different database because Access, while a fine database and able to hold up
to pretty stiff requirements, was not build for _that_ type of fine tuning.

Gees!

"David W. Fenton" wrote in message
. 1...
"Amy Blankenship" wrote in
:

. . . if you're trying to squeeze this kind of minute performance
advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just fine,
but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It only
falls down in comparison to other databases in terms of handling
large numbers of simultaneous users and in terms of the size of the
data store.

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

"Tim Marshall" wrote in message
...
Amy Blankenship wrote:

If my Access databases were large enough that indexing were an issue,
they wouldn't be in Access.

OK, I'll leave the church of the not null unique vs. primary key to you.
How many times do you kneel facing Redmond each day,anyway ;-)?

It is Edgar Codd's legacy to which most of us pay homage, the platform on
which database development takes place is utterly irrelevant.

I haven't followed this thread, but I have seen this post. No offence,
but you've shown a complete and, if you are anything close to a
"professional" developer, shocking lack of understanding of relational
database design and of "Access" by:

1) Indicating indexing is not an issue; and

2) by referring to "Access databases".

So, you're saying that multiple files created in access are not Access
databases? What do you call them then...?

Just wondering...

"Amy Blankenship" wrote in
:

"David W. Fenton" wrote in message
. 1...
"Amy Blankenship" wrote in
:

. . . if you're trying to squeeze this kind of minute
performance advantage out, Access isn't the right tool.

Oh, give me a break. Jet can handle millions of records just
fine, but without proper indexing, it wouldn't be usable.

Proper indexing is essential in *all* database engines.

And Jet is *not* a toy database in terms of data handling. It
only falls down in comparison to other databases in terms of
handling large numbers of simultaneous users and in terms of the
size of the data store.

Hey, hang on here. I never was referring to simple indexing as
the squeezing of performance. I was referring to the bizarre
suggestion of Jamie Collins that people pick primary keys not for
their uniqueness but to physically cluster the records on disk.
This is a practice you, yourself have taken issue with. Let me
completely eliminate any possibility of misunderstanding here for
those unable to take things in context:

If you are so concerned with performance that you are picking your
primary key in order to physically cluster the records, you need
to be using a different database because Access, while a fine
database and able to hold up to pretty stiff requirements, was not
build for _that_ type of fine tuning.

Gees!

If that's what you meant to say, then that's what you should have
said.

I don't think you've really got grounds to complain, given how far
this nuanced statement of your position is from the original one I
replied to above.

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

"Amy Blankenship" wrote in
:

Whoah, whoah. Hang on. I was referring to the topic of this part
of the thread, which was the use of primary keys that are not
unique identifiers of the record in order to cluster them on disk,
NOT simply indexing.

Um, how do you set a non-unique index as a PK? The index has to be
unique to qualify as a PK, however artificially you've created it.

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