Primary Keys

[Jamie Collins]

Re: Primary Keys


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.

--

[Amy Blankenship]

Re: Primary Keys

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" <jamiecollins@x smail.comwrote in message
news:1152971717 .360104.171190@ 75g2000cwc.goog legroups.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
>relationship s,
>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.
>
--
>

[polite person]

Re: Primary Keys

On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship" <Amy_nospam@mag noliamultimedia .comwrote:

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

[Lyle Fairfield]

Re: Primary Keys

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]

Re: Primary Keys


"polite person" <snip@snippers. comwrote in message
news:k3gib25se0 l2a8jn1sh1prcrg 8mm4f5117@4ax.c om...

On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship"
<Amy_nospam@mag noliamultimedia .comwrote:
>
<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.

[Tim Marshall]

Re: Primary Keys

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
"profession al" 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

[Tim Marshall]

Re: Primary Keys

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

[David W. Fenton]

Re: Primary Keys

polite person <snip@snippers. comwrote in
news:22bhb2hggh d5he8u36a8lg4r8 m5dsphrlf@4ax.c om:

On Fri, 14 Jul 2006 20:06:45 -0500, "David W. Fenton"
<XXXusenet@dfen ton.com.invalid wrote:
>

>>polite person <snip@snippers. comwrote in
>>news:ircfb25t 9skekh9guf9frp1 3b1t0bmei6n@4ax .com:
>>

>>On Fri, 14 Jul 2006 09:52:16 -0500, "David W. Fenton"
>><XXXusenet@df enton.com.inval idwrote:
>>>
>>>>polite person <snip@snippers. comwrote in
>>>>news:ii6fb2 df2h9ati2fg2e3j c3vdmvunqfnkg@4 ax.com:
>>>>
>>>>My experience is that most tables in published databases make
>>>>no effort to prevent duplicate records in tables with a single
>>>>artificia l key, starting with the customer table in NorthWind.
>>>>
>>>>The sample databases and templates that ship with Access are
>>>>terrible in every way. They encourage bad schema design and
>>>>horrid UI design and programming practices.
>>>
>>So, if this has been encouraged, why is it wrong to point out
>>what the errors are?

>>
>>To whom? In what context?

>
To the person who originally asked the question here

The original poster asked:

I have several tables that have unique fields. Can I use them as

primary keys or should I define an autonum primary key?

I read that as saying he/she already had unique indexes on the
candidate keys.

Thus, the advice about unique indexes was unnecessary.

The suggestion was actually made by you in a reply to Pete
Cresswell, who knows perfectly well that unique indexes should be in
place with surrogate keys. Perhaps Pete didn't mention that in his
reply to the original poster precisely because the original post
suggested that it didn't need to be mentioned.

So, it looks to me like the constant citing of this caveat is
something of a knee-jerk reaction on the part of those promoting
natural keys.

Which was my point from the beginning.

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

[David W. Fenton]

Re: Primary Keys

"Jamie Collins" <jamiecollins@x smail.comwrote in
news:1152971952 .491521.67220@m 73g2000cwd.goog legroups.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/

[David W. Fenton]

Re: Primary Keys

"Jamie Collins" <jamiecollins@x smail.comwrote in
news:1152971717 .360104.171190@ 75g2000cwc.goog legroups.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/

[David W. Fenton]

Re: Primary Keys

"Amy Blankenship" <Amy_nospam@mag noliamultimedia .comwrote in
news:OnsFgKGqGH A.2464@TK2MSFTN GP03.phx.gbl:

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

[polite person]

Re: Primary Keys

On Sat, 15 Jul 2006 20:23:05 -0500, "David W. Fenton" <XXXusenet@dfen ton.com.invalid wrote:

>polite person <snip@snippers. comwrote in
>news:22bhb2hgg hd5he8u36a8lg4r 8m5dsphrlf@4ax. com:
>

>On Fri, 14 Jul 2006 20:06:45 -0500, "David W. Fenton"
><XXXusenet@dfe nton.com.invali dwrote:
>>

>>>polite person <snip@snippers. comwrote in
>>>news:ircfb25 t9skekh9guf9frp 13b1t0bmei6n@4a x.com:
>>>
>>>On Fri, 14 Jul 2006 09:52:16 -0500, "David W. Fenton"
>>><XXXusenet@d fenton.com.inva lidwrote:
>>>>
>>>>>polite person <snip@snippers. comwrote in
>>>>>news:ii6fb 2df2h9ati2fg2e3 jc3vdmvunqfnkg@ 4ax.com:
>>>>>
>>>>>My experience is that most tables in published databases make
>>>>>no effort to prevent duplicate records in tables with a single
>>>>>artifici al key, starting with the customer table in NorthWind.
>>>>>
>>>>>The sample databases and templates that ship with Access are
>>>>>terrible in every way. They encourage bad schema design and
>>>>>horrid UI design and programming practices.
>>>>
>>>So, if this has been encouraged, why is it wrong to point out
>>>what the errors are?
>>>
>>>To whom? In what context?

>>
>To the person who originally asked the question here

>
>The original poster asked:
>

>I have several tables that have unique fields. Can I use them as

>primary keys or should I define an autonum primary key?
>
>I read that as saying he/she already had unique indexes on the
>candidate keys.
>
>Thus, the advice about unique indexes was unnecessary.
>
>The suggestion was actually made by you in a reply to Pete
>Cresswell, who knows perfectly well that unique indexes should be in
>place with surrogate keys. Perhaps Pete didn't mention that in his
>reply to the original poster precisely because the original post
>suggested that it didn't need to be mentioned.
>
>So, it looks to me like the constant citing of this caveat is
>something of a knee-jerk reaction on the part of those promoting
>natural keys.
>
>Which was my point from the beginning.

In another thread you say
"The poster simply asked about the pros and cons of surrogate vs.
natural keys. "
which is how I read it too. Surely I haven't committed such a great offence?

Actually I am far from an advocate of natural keys, in fact I don't believe they exist. We can't put
real life objects into tables, only their names, which has the problem mentioned by Pete, eg two
things can have the same name without us at the time knowing it but finding out later.

This also applies to things identified by properties. Think of a database of games.

There is a case I suppose for treating an autonumber as part of the data itself, which would allow
us to have two people (for example) about which we hold exactly the same information but which we
know are actually different people.

My remark should have also have been unnecessary because in the relational model (or any model based
on sets) duplicates should not exist - in fact the concept of duplicateness (duplicity?) doesn't
occur, but some dbs allow it both in tables and in result "sets".

[Amy Blankenship]

Re: Primary Keys

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" <TIMMY!@PurpleP andaChasers.Moe rtheriumwrote in message
news:e9bvg5$ls9 $1@coranto.ucs. mun.ca...

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

[Amy Blankenship]

Re: Primary Keys

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
misunderstandin g 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" <XXXusenet@dfen ton.com.invalid wrote in message
news:Xns9801DAB 1559DBf99a49ed1 d0c49c5bbb2@127 .0.0.1...

"Amy Blankenship" <Amy_nospam@mag noliamultimedia .comwrote in
news:OnsFgKGqGH A.2464@TK2MSFTN GP03.phx.gbl:
>

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

[Amy Blankenship]

Re: Primary Keys


"Tim Marshall" <TIMMY!@PurpleP andaChasers.Moe rtheriumwrote in message
news:e9bs2k$9ag $1@coranto.ucs. mun.ca...

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