Primary Keys

[Amy Blankenship]

On Sat, 15 Jul 2006 13:44:34 -0500, "Amy Blankenship"


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

as a

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.

 

[David W. Fenton]

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.

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]

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]

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

 

[Amy Blankenship]

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.

 

[Amy Blankenship]

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!

 

[Amy Blankenship]

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

 

[David W. Fenton]

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]

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]

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

Just wondering...

We're not talking about Access databases here, but Jet.

D'oh.

 

[Amy Blankenship]

See, that right there argues against his point. You couldn't use a date,
for instance, because that might not be unique but you might then want to
use it as a condition in a BETWEEN clause.

 

[Amy Blankenship]

We're not talking about Access databases here, but Jet.

Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, and are accessed from inside the Access
application (with forms, reports, and module). However, you can also access
the database tables with other engines when calling the *Access Database*
from outside the Access application. In all probability, you could probably
call an Access Database file from another Access Database file using a
different engine, though I have not tried it. It's on my to-do list.

Saying that "Access Database" is not a valid way to refer to a file created
in the Access application containing tables that _can_ be accessed by Jet is
in my opinion a bit of a stretch. However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up your
indexes from within Access, but then you call the file with another driver,
how do the indexes behave?

-Amy

 

[Rick Brandt]

Now this is just plain silly. The access file is actually a complete
application that contains, among other things, tables that _can be_
accessed by the Jet engine, and are accessed from inside the Access
application (with forms, reports, and module). However, you can also
access the database tables with other engines when calling the
*Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I have
not tried it. It's on my to-do list.
Saying that "Access Database" is not a valid way to refer to a file
created in the Access application containing tables that _can_ be
accessed by Jet is in my opinion a bit of a stretch. However, it
does bring up an interesting question, one I don't claim to know the
answer to: if you set up your indexes from within Access, but then
you call the file with another driver, how do the indexes behave?

-Amy

I believe David's point is that one can use other programming environments like
VB to create an MDB containing tables along with an entire application to
interface with that MDB all on a PC that does not even have Access installed.
Would you still call that MDB an "Access Database"?

In addition to being able to use a database stored in an MDB file without using
Access one can also build an interface with Access to a non-Jet database like
SQL Server or Oracle. Would you call those "Access Databases"?

Pedantically, when most people talk about an Access Database, the *database* is
a Jet database and the *application* part is Access. I usually use the term
"Access/Jet" if I mean a database application consisting completely of MDB files
and "Access Application" if I am talking about the front end to any other
database engine.

For me the distinction only matters when the discussion concerns "engine level"
stuff. Discussions about keys, constraints, relationships etc., only make sense
when you are specific about whether the engine is Jet or something else.

As for your question, you would be using Access to create Jet indexes so they
would still apply when you interface with the database from another program.

 

[Larry Linson]

I believe David's point is that one can use
other programming environments like
VB to create an MDB containing tables
along with an entire application to
interface with that MDB all on a PC that
does not even have Access installed.
Would you still call that MDB an
"Access Database"?

Many do, including Microsoft.

Larry

 

[polite person]

Many do, including Microsoft.

Larry

If you create a word document using open office it is still a word document. Or is it?
My god these threads could go on forever!

 

[Lyle Fairfield]

If you create a word document using open office it is still a word document. Or is it?
My god these threads could go on forever!

In the olden days an mdb file was a JET file. If it were created in VB
3's (and others) utility it would not have a reference to Access ...
someone who didn't know about Access might not even guess there was a
close relationship. Since Access objects have been stored in blobs I
don't know whether or not this is still pertinent and I'm not sure
about such things existing any more but my guess is that they do ... or
can. It would be kinda delusionary to call such a file an Access DB?

 

[Amy Blankenship]

I believe David's point is that one can use other programming environments
like VB to create an MDB containing tables along with an entire
application to interface with that MDB all on a PC that does not even have
Access installed. Would you still call that MDB an "Access Database"?

Absolutely. That's how I use most Access databases I create.

In addition to being able to use a database stored in an MDB file without
using Access one can also build an interface with Access to a non-Jet
database like SQL Server or Oracle. Would you call those "Access
Databases"?

Depending on who I am talking to about it. For most people, probably. Most
people I might need to talk to about it wouldn't understand the distinction,
orcare.

Pedantically, when most people talk about an Access Database, the
*database* is a Jet database and the *application* part is Access. I
usually use the term "Access/Jet" if I mean a database application
consisting completely of MDB files and "Access Application" if I am
talking about the front end to any other database engine.

For me the distinction only matters when the discussion concerns "engine
level" stuff. Discussions about keys, constraints, relationships etc.,
only make sense when you are specific about whether the engine is Jet or
something else.

Again, depends on who you're talking to. Most people understand "Access
database" but couldn't care less what the Jet engine does.

As for your question, you would be using Access to create Jet indexes so
they would still apply when you interface with the database from another
program.

Cool.

 

[David W. Fenton]

See, that right there argues against his point. You couldn't use
a date, for instance, because that might not be unique but you
might then want to use it as a condition in a BETWEEN clause.

Er, ever heard of compound keys?

 

[David W. Fenton]

Now this is just plain silly. . . .

No, it's not the least bit silly. Discussion in this newsgroup would
go much more smoothly if people maintained the distinction between
Jet and Access in their posts. Often, it's necessary to sort out
what someone is trying to do and whether they are asking about an
Access problem or a Jet problem.

. . . The access file is actually a complete
application that contains, among other things, tables that _can
be_ accessed by the Jet engine, . . .

But indexes RI are *not* an Access feauture, but a Jet features. On
that level we are talking purely about Jet and not about Access at
all.

. . . and are accessed from inside the Access
application (with forms, reports, and module). However, you can
also access the database tables with other engines when calling
the *Access Database* from outside the Access application. In all
probability, you could probably call an Access Database file from
another Access Database file using a different engine, though I
have not tried it. It's on my to-do list.

Who gives a rat's ass?

Saying that "Access Database" is not a valid way to refer to a
file created in the Access application containing tables that
_can_ be accessed by Jet is in my opinion a bit of a stretch. . .
.

Well, the reason you're "upset" is because you have completely
misunderstood the point.

The subject of this thread is *not* an Access issue -- it has
nothing to do with the properties of Jet databases created by Access
that are specific to Access. It is entirely about properties of the
Jet database engine.

. . . However, it does bring up an interesting
question, one I don't claim to know the answer to: if you set up
your indexes from within Access, but then you call the file with
another driver, how do the indexes behave?

Well, d'oh. Through Jet. And only the data is available.

D'oh.