Primary Keys

David W. Fenton wrote:
As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.

The table may not be updated frequently e.g. how often does your
telephone company send you a new paper copy directory, how frequent is
your subscription for a refreshed data set? I don't mean to argue
pointlessly, merely point out that you have to base your PKs (on a
table by table basis) on SQL DML which may or may not include frequent
SQL UPDATE statements.

Jamie.

--

David W. Fenton wrote:
with Jet you can only have the one clustered index

Erm, think it is fundamental that a table can have only one clustered
index, regardless of SQL implementation. Surely a table with two
phyical orders is in fact tow tables!

I never use
the BETWEEN operator on anything but date fields, which in the vast
majority of tables could not possibly ever be a candidate for PK
(and very seldom even a candidate for inclusion in a compound
natural key, which wouldn't give you the clustered index benefit,
anyway, unless the date was the first field of the compound key)

You seem to have talked yourself into seeing my point i.e. make the PK
compound with first your date column followed by a candidate key. This
will favour your BETWEEN constructs.

Jamie.

--

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. With cheap fast processors,
labor time of the developer saved by using PK's that make development
efficient is far more cost-effective than any small speed gain realized by
trying to physically order records next to each other. Access is not the
tool of choice for DBA's obsessed with application speed ;-).

"Jamie Collins" wrote in message
oups.com...

David W. Fenton wrote:
As long as you've got records added after the last compact, there's
always going to be some data pages (and index data pages) that are
not in the final order (whether your natural key order or my random
Autonumber order), and thus the performance gain can never be fully
realized in an actively used database.

The table may not be updated frequently e.g. how often does your
telephone company send you a new paper copy directory, how frequent is
your subscription for a refreshed data set? I don't mean to argue
pointlessly, merely point out that you have to base your PKs (on a
table by table basis) on SQL DML which may or may not include frequent
SQL UPDATE statements.

Jamie.

--

"Jamie Collins" wrote in
oups.com:

David W. Fenton wrote:
with Jet you can only have the one clustered index

Erm, think it is fundamental that a table can have only one
clustered index, regardless of SQL implementation. Surely a table
with two phyical orders is in fact tow tables!

Yes. What I should have said was that Jet allows only the PK to be
clustered, not the indexed field of your choice.

I never use
the BETWEEN operator on anything but date fields, which in the
vast majority of tables could not possibly ever be a candidate
for PK (and very seldom even a candidate for inclusion in a
compound natural key, which wouldn't give you the clustered index
benefit, anyway, unless the date was the first field of the
compound key)

You seem to have talked yourself into seeing my point i.e. make
the PK compound with first your date column followed by a
candidate key. This will favour your BETWEEN constructs.

But it's a nonsensical way to pick PKs. Dates are very seldom going
to be part of a natural key, at least not for very many types of
entities.

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

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

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?
I certainly get you point but I'm trying to address another issue i.e.
what's the difference between NOT NULL UNIQUE and PRIMARY KEY in
Access/Jet to which the answer is the clustered index.

We need to differentiate between data integrity and indexing. I agree
that data integrity is vital, whereas indexing is less significant but
still important.

You should think of PRIMARY KEY as your most powerful index for a
table. If you are ignoring this aspect of PK then you are at best
missing out on some potential gain e.g. better performance, improved
concurrency, etc.

It sounds like you have no need for indexes: that's fine, that's your
choice and if your databases are small you will probably not notice any
difference. 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.

Jamie.

--

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

Jamie.

--

I think YOU need to reread it. The poster asked the
advantages/disadvantages of using natural keys vs. autonumber. The words
not null unique did NOT appear.

In your own words, indexing is less significant than data integrity (and,
presumably, developer time). Therefore, the things that are more
significant should be considered first.

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 ;-)?

-Amy

"Jamie Collins" wrote in message
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?
I certainly get you point but I'm trying to address another issue i.e.
what's the difference between NOT NULL UNIQUE and PRIMARY KEY in
Access/Jet to which the answer is the clustered index.

We need to differentiate between data integrity and indexing. I agree
that data integrity is vital, whereas indexing is less significant but
still important.

You should think of PRIMARY KEY as your most powerful index for a
table. If you are ignoring this aspect of PK then you are at best
missing out on some potential gain e.g. better performance, improved
concurrency, etc.

It sounds like you have no need for indexes: that's fine, that's your
choice and if your databases are small you will probably not notice any
difference. 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.

Jamie.

--

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

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

You have Access Databases without tables or records? Cool!

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

As I said, if you consider yourself a database developer, all I can say
is...

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