A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Data Primary key vs. Artificial (Autonumber) primary key



 
 
Thread Tools Display Modes
  #71  
Old December 11th, 2007, 09:27 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Data Primary key vs. Artificial (Autonumber) primary key

On Dec 11, 5:31 am, "Tony Toews [MVP]" wrote:
How does clustering apply to Access databases?


If by "Access databases" you mean Jet then it has already been
mentioned several times in this thread alone. Here I even got accused
by the OP of providing a "helpful answer":

http://groups.google.com/group/micro...add6aaab8ef045

Jamie.

--

  #72  
Old December 11th, 2007, 09:59 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Data Primary key vs. Artificial (Autonumber) primary key

On Dec 8, 7:43 pm, "David W. Fenton"
wrote:
Jamie Collins wrote
:


I broadly agree with everything you've said here. I do still think
that many (perhaps most) users of Autonumber columns use it for their
one and only unique constraint per table; for the record, I think such
people are ill informed rather than stupid. Some should now be better
informed after reading your post

One point:

in a lot of situations, forcing someone to use
the natural key can lead to all sorts of other problems, like
default values to avoid Nulls, and then you have to program around
those (e.g., you won't be able to use this to concatenate the name
fields: Mid(("12" + [LastName]) & (", " + [FirstName]),3), because
with default values, none of your "empty" fields will be Null).


I don't design my schemas to fit your ready-rolled formulas g!. I
have no doubt that if I define my middle_name column as NOT NULL with
a DEFAULT of '{{NONE}}' (plus CHECK constraints to disallow the empty
string, spaces, etc) then you would be able to come up with a similar
formula to derive a full name. My create/update stored procs handle
the NULL value and the empty string so it shouldn't be too onerous on
the 'front end guy' to replace '{{NONE}}', '{{NK}}', etc, with
whatever is appropriate in context.

That said, person's name rarely makes a good key, so is not IMO a good
example of a natural key. My favourite is the ISBN: industry standard
(ask at your local public library or look at the URLs on Amazon),
trusted source (will resolve rare cases of duplication), familiar and
verifiable in reality (take a look at the bar code on the back cover
of your favourite book), stable (sure, they have recently changed from
10 to 13 characters but this has been managed by the trusted source to
provide an upgrade path), fixed length with a check digit (easy to
validate at time of keying in), a so-called 'intelligent' key (it
comprises detail of region, publisher, etc) and so on.

Jamie.

--

  #73  
Old December 11th, 2007, 05:01 PM posted to microsoft.public.access.tablesdbdesign
Pat Hartman
external usenet poster
 
Posts: 392
Default Data Primary key vs. Artificial (Autonumber) primary key

I think the answer you've gotten is that none of us can think of a good
reason to include an autonumber in a table if it is NOT the primary key.

"Jamie Collins" wrote in message
...
On Dec 10, 3:32 pm, "Pat Hartman" please no wrote:
If you want to conduct an intellectual discussion
you really need to be
able to handle it when people disagree with you and stop taking cheap
shots.


If I really was conducting this discussion I would have answers to my
direct questions (ping Dale Fye: "given that someone has used
Autonumber as a so-called surrogate, what reasons would they have for
not making the Autonumber the PK?")

And if I couldn't handle people around here disagreeing with me I'd
have left years ago!

FYI:

The Access Web: Netiquette
http://www.mvps.org/access/netiquette.htm
Be thoughtful of bandwidth and other folks' thought processes:
[quote] Look for Smileys :-), grins g, and other "Emoticons". When
you see one, the preceding statement was not meant to be really
serious, don't take it as such. [Unquote]

I don't think the poster was being serious either (hence my short
dismissal with a grin). Designing database for one's own convenience
rather than your client's doesn't sound like a credible proposition to
me.

Jamie.

--



  #74  
Old December 14th, 2007, 10:10 AM posted to microsoft.public.access.tablesdbdesign
Jamie Collins
external usenet poster
 
Posts: 1,705
Default Data Primary key vs. Artificial (Autonumber) primary key

On Dec 11, 4:01 pm, "Pat Hartman" please no wrote:
I think the answer you've gotten is that none of us can think of a good
reason to include an autonumber in a table if it is NOT the primary key.


I'm assuming that by 'primary key' you mean the SQL keyword PRIMARY
KEY rather than the logical meaning of 'primary key', on the basis
that an Autonumber only comes into existence in the physical
implementation and has no place in the logical model. I'm also
assuming that by 'us' you aren't alienating me g.

1) Clustering: as an extreme example, imagine a table with a ten-
column compound key and you wanted to cluster on just one of those
columns being a DATETIME consisting of non-unique values. In SQL
Server, if you created a clustered index solely on a column without a
unique constraint then the system would create an auto-increment
column under the hood to 'uniqueify' the values. In Jet, the only way
of influencing clustering is to use the PRIMARY KEY designation and
Jet will not allow you to create a PK solely on a column containing
non-unique values. You can do the same trick as SQL Server but you
have to explicitly create the 'uniqueifier' yourself and an Autonumber
column is a very convenient way of doing this. You'd then create a
compound PK on (datetime_col, unique_col) _in that order_ of course
(BTW how does one do this using the Access interface rather than
code?) In this scenario, the autonumber in the table is not the
primary key, though it is _part of_ the PRIMARY KEY.

2) PRIMARY KEY is arbitrary: you can put it on *any* columns (bar
those of type OLEOBJECT) in the table (unless existing values are NULL
and/or non-unique) or you can opt to omit a PRIMARY KEY completely: if
you have a unique constraint (e.g. unique index and NOT NULL
designation) on a candidate key I don't think anyone could fault you
(though they could point out that you were potentially missing out on
an opportunity for optimization). Therefore, a table with an
Autonumber column but without a PRIMARY KEY is perfectly acceptable.

I'm sure there are other reasons which smarter people than me could
come up with; perhaps a little thinking outside the box is required?

Jamie.

--

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:11 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.