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  

Autonumber using alpanumerics



 
 
Thread Tools Display Modes
  #11  
Old August 16th, 2005, 04:55 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Rick Brandt wrote:

Let me qualify that statement by saying that I would do this if I were
designing a database for the entity that "owns" the creation of ISBN
numbers. If I were designing a database for a library (an entity that
merely consumes ISBNs) then I would put it into a single field.


I'm getting a little confused. Let's assume the 'library' example.
Could you relate you last comment (quoted above) back to you earlier
comments about 'how the data is stored [and] how it is consumed'
because the library would do both. Thanks again,

Jamie.

--

  #12  
Old August 16th, 2005, 05:19 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

Jamie Collins wrote:
Rick Brandt wrote:

Let me qualify that statement by saying that I would do this if I
were designing a database for the entity that "owns" the creation of
ISBN numbers. If I were designing a database for a library (an
entity that merely consumes ISBNs) then I would put it into a single
field.


I'm getting a little confused. Let's assume the 'library' example.
Could you relate you last comment (quoted above) back to you earlier
comments about 'how the data is stored [and] how it is consumed'
because the library would do both. Thanks again,

Jamie.


Does the library issue ISBNs? I think not. It is a number that they use
that has been determined by an outside authority. As a consumer of that
number it might well be fine to store as a single atribute in a single field
because they are primarily using it to uniquely identify books.

However; the entity that is issuing ISBN numbers might more likely have a
field for Country code, a field for Publisher, a field for Title, and a
field for the Check Digit. On output interfaces this would be combined and
dislayed as the single attribute [ISBN Number], but that is not how it would
be stored in the database (if they did it correctly).

If a library frequently has a need to do searches by the individual pieces
of information contained within an ISBN number then they would be better off
splitting it into separate fields as well.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com



  #13  
Old August 17th, 2005, 11:41 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Jamie

What are your thoughts on the subject? You have a lot of comments and
questions, but I don't have a sense of where you stand.

You state that you "know that the ISBN is an excellent example of a key" --
on what do you base this assertion?

The "one fact, one field" statement is a paraphrase of the notion of
atomicity, which seems to be at the heart of 1NF.

Perhaps your Google preferences need resetting -- I just found a slew of
references to ways of stating ("one fact, one field", and "one field, one
fact").

Regards

Jeff Boyce
Access MVP

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

Jeff Boyce wrote:
Periodically the subject of "intelligent keys" shows up here in the
tablesdbdesign newsgroup. The consensus has seemed to me to argue

against
combining multiple facts into a single field


Is this the same consensus that recommends autonumber primary keys, by
any chance g?

The underlying notion is "one fact,
one field".


I've not seen this soundbite before. My google search:


http://groups.google.com/groups?q=%2....*&qt_s=Search

turned up nothing. Could you clarify your point, please?

That an ISBN "contains many facts" doesn't, in itself, qualify it as a

good
example of a key, does it? In fact, I don't know if an ISBN is a single
field (then, by 1NF standards, it would not be a good design), or is

itself
a concatenation of multiple, single-fact fields...


I'm not sure what you are getting at here. I know that the ISBN is an
excellent example of a key and that it contains multiple facts. Are you
saying the ISBN is a violation of 1NF? This is the first time I've
heard this suggested but this could be interesting. Could you expand
your thoughts, please?

Jamie.

--


  #14  
Old August 17th, 2005, 11:48 AM
Jeff Boyce
external usenet poster
 
Posts: n/a
Default

Jamie

I'm replying in a separate thread to your "aside" comment autonumber
primary keys. I believe this to be an aside because the topic was use of
intelligent keys.

I have seen considerable debate about using autonumber primary keys. One
school appears to subscribe to the notion that a natural key can (and
perhaps even 'must') be found for every row/record. Another school
subscribes to the notion that an autonumber primary key simply provides a
unique row/record identifier.

What works for you? I usually refrain from joining one camp or the other,
and use what seems practical, given the entity.

Regards

Jeff Boyce
Access MVP
"Jamie Collins" wrote in message
oups.com...

Jeff Boyce wrote:
Periodically the subject of "intelligent keys" shows up here in the
tablesdbdesign newsgroup. The consensus has seemed to me to argue

against
combining multiple facts into a single field


Is this the same consensus that recommends autonumber primary keys, by
any chance g?

The underlying notion is "one fact,
one field".


I've not seen this soundbite before. My google search:


http://groups.google.com/groups?q=%2....*&qt_s=Search

turned up nothing. Could you clarify your point, please?

That an ISBN "contains many facts" doesn't, in itself, qualify it as a

good
example of a key, does it? In fact, I don't know if an ISBN is a single
field (then, by 1NF standards, it would not be a good design), or is

itself
a concatenation of multiple, single-fact fields...


I'm not sure what you are getting at here. I know that the ISBN is an
excellent example of a key and that it contains multiple facts. Are you
saying the ISBN is a violation of 1NF? This is the first time I've
heard this suggested but this could be interesting. Could you expand
your thoughts, please?

Jamie.

--


  #15  
Old August 17th, 2005, 01:14 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Jeff Boyce wrote:
I have seen considerable debate about using autonumber primary keys. One
school appears to subscribe to the notion that a natural key can (and
perhaps even 'must') be found for every row/record.


Good timing: I put an autonumber (incrementing INTEGER) into a database
just one hour ago. I needed to know the relative order in which rows
were being added to the table and autonumber is convenient. The table
already had a natural key being a composite of the entity's attributes,
in the Dr Dodd tradition; I'd have been in a whole lot of trouble if I
didn't.

I've looked after databases where a natural key exists and an
autonumber is used as an 'alternate' key. I haven't sought to change
this because there is some justification for this approach e.g. a
single INTEGER column foreign key is more convenient to code than a
multi- text column key. There may be some storage and performance
benefits but I haven't investigated this myself, a correct data model
being my primary consideration.

Another school
subscribes to the notion that an autonumber primary key simply provides a
unique row/record identifier.


Using an autonumber as a key where no natural key exists is asking for
trouble. It is uniqueness merely for the sake of it (I'd rather have a
'heap' i.e. without a key and technically not a table at all). You must
expose the autonumber for it to be useful and almost everyone agrees
you should never expose an autonumber in this way.

We need to be clear about the meaning of PRIMARY KEY in Access/Jet.
Here is a pertinent newsgroup post from Joe Celko: "Dr. Codd started
with the concept of a PRIMARY KEY in his first writing; it was the old
sort key from sequential file processing and magnetic tape storage in
disguise. It was awhile before Dr. Codd changed his mind and said that
all keys are equally keys, and we don't need a special one in a
relational database, like you did in a tape system. Unfortunately, the
first SQL systems were build on existing file systems and the idea of
the PRIMARY KEY had become part of the language... The UNIQUE
constraint lets you have multiple keys on a table." So in relational
terms, there is no difference between a key defined as UNQIUE and a key
defined as PRIMARY KEY.

In the Access/Jet implementation, PRIMARY KEY has special meaning but
it has nothing to do with relational keys. Allow me to quote myself
g: "PRIMARY KEY has special meaning for Jet [Access]. In relational
terms, a key is a key and PRIMARY KEY has no special meaning. However,
PRIMARY KEY has traditionally taken on a meaning particular to each SQL
product and Jet is no exception. For Jet, the special meaning is that
PRIMARY KEY provides the only means of specifying the clustered index
for a table. The clustered index is non-maintained, meaning the index
is only 'physically' rebuilt when the file is compacted; it is,
however, still maintained 'logically' between compacts. Some people
have a problem with the term 'clustered index' as regards Jet (don't
make the mistake on thinking 'not supported' means 'does not exist') so
let me clarify: PRIMARY KEY determines the physical ordering on disk.
For best performance, a table should be physically ordered on the
columns of its 'primary' use i.e. those used in GROUP BY and BETWEEN
clauses. Think of a paper copy telephone directory, which can obviously
have only one physical order: if I'm using it primarily for search such
as 'get listings for people whose last name begins with the letter C'
then I'd want it ordered on last name because it would take me no time
at all to rip out the required pages. Similarly, it pays to choose your
PRIMARY KEY carefully and in accordance with its implications for
physical ordering on disk. If you are using PRIMARY KEY to mean a
UNIQUE constraint/index only then your database may be taking a
performance every time a table with a poorly chosen PRIMARY KEY is
queried (pay no attention to the message that tells you, 'A table must
have a primary key for you to define a relationship between this table
and other tables in the database,' it lies, it lies). And this is why
an autonumber is usually a poor choice for PRIMARY KEY: how useful
would you find a paper copy telephone directory physically ordered on
telephone number?"

Given that PRIMARY KEY affects physical ordering and nothing else, I
can see no justification for an autonumber *solely* being PRIMARY KEY.
There may, however, be some justification for including an autonumber
in a *composite* PRIMARY KEY assuming it was the last column specified
in the PK's definition e.g.
PRIMARY KEY (key_col1, key_col2, ..., autonumber_col).

Jamie.

--

  #16  
Old August 17th, 2005, 03:21 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Jeff Boyce wrote:
You have a lot of comments and
questions, but I don't have a sense of where you stand.


I'm still learning. I'm particularly poor when it comes to normal
forms. I took an online test on the subject recently (e.g. differences
between BCNF - any relation g? - and 5NF etc) and scored appallingly.


And it would seem I'm not the only one here. I did a search of postings
on the subject in the Access groups and here are the ten most recent
usages in the last 24 hours:

is a breech of normalization.
violates data normalization rules
if you had your database normalized
a less normalized table
isn't properly normalized
Its not 100% normalized
properly normalized
the normalization rules of relational databases say
one field violates data normalization rules
It is against relational database normalization rules

Nine of those I'd have extreme trouble defending to Fabian Pascal g.

In contrast, explicit references to 3NF (or Third Normal Form) and 1NF
(or First Normal Form) are seen fairly infrequently in these groups.

Me, I'm a casual bystander on matters of normal forms.

You state that you "know that the ISBN is an excellent example of a key" --
on what do you base this assertion?


In a nutshell, because Joe Celko tells me so g.

The ISBN is maintained by trusted source: useful when the encoding
system changes because the source authority manages the change on
behalf of everyone. It is unique (situations where duplicates arise are
rectified by the trusted source). It is an *international* encoding
system. It can be verified in the real world: get the book off the
shelf and look at the barcode, go to Amazon.com, etc). It is verifiable
within itself: can be tested for character length and syntax, has a
check digit. I'm also told that a key must come into existence by the
time the entity has come into existence and that a book may be
allocated an ISBN before the publisher has even seen the first draft.

The "one fact, one field" statement is a paraphrase of the
notion of atomicity, which seems to be at the heart of 1NF.


I would have though, 'one row, one fact' would be more like it.

My normal forms knowledge may not be up to much but I *can* google
vbg. Did you notice the group and author of these posts?

If I search using

"one field one fact" OR "one fact one field"
-group:microsoft.public.access.*

I get no results i.e. no one outside the Access ngs are using these
phrases.

If I search using just

"one field one fact" OR "one fact one field"

I get 21 results. Then I can reduce the 19 by excluding a certain g
author:

"one field one fact" OR "one fact one field" -"Jeff Boyce"

I get just 2 results.

Jamie.

--

  #17  
Old August 17th, 2005, 03:25 PM
Rick Brandt
external usenet poster
 
Posts: n/a
Default

Jamie Collins wrote:

You state that you "know that the ISBN is an excellent example of a
key" -- on what do you base this assertion?


In a nutshell, because Joe Celko tells me so g.


But Joe would also remind you that "a key" does not mean "a single field".
A key can be (and often is) made up of multiple fields. In fact a natural
key purist like Mr. Celko would likely have multiple column keys the vast
majority of the time because few entities have a single attribute that is
unique.

--
I don't check the Email account attached
to this message. Send instead to...
RBrandt at Hunter dot com


  #18  
Old August 17th, 2005, 04:28 PM
Jamie Collins
external usenet poster
 
Posts: n/a
Default


Rick Brandt wrote:

But Joe would also remind you that "a key" does not mean "a single field".
A key can be (and often is) made up of multiple fields. In fact a natural
key purist like Mr. Celko would likely have multiple column keys the vast
majority of the time because few entities have a single attribute that is
unique.


Indeed. Joe could also tell you a thing or two about 'attribute
splitting' i.e. when you take what should have been one table, find an
attribute and use the values of the attribute to make extra tables that
should not exist.

He'd also tell you fields are not columns and at great length too.

Jamie.

--

  #19  
Old August 19th, 2005, 12:54 PM
Katharine Jansen
external usenet poster
 
Posts: n/a
Default

Hi there,
I received some good advice relating to having an alphanumeric reference
number, which I have been able to create and it works well in a query. Thanks
to those who took the time to give me the advice. However, the data that I am
able to obtain from the query is not displayed in the underlying table or
form.

The Table has 3 fields relative to the query; "ClientID Number", a 3 digit
autonumber, "ClientID Name", a 3 digit manually created alpha figure, and
"ClientRef", which displays a combination of both these data to display the
alphanumeric figure that I need. The query draws on the information from the
"Corporate Client Details" Table, where all related fields are found.

In the underlying table I tried to use the lookup wizard to recall the data
from the query for display in the table/form, but when doing so got the
message "no valid fields can be found in 'Corporate Client Details Query'.
You may have selected a query that uses the table your adding the lookup
column to. Please select a new source".

I guess the error message is self-explanatory, but I'm wondering how I can
get around this problem. Off the top of my head it seemed appropriate to then
create another table which holds the alpha and the numeric fields, along with
the client name, then only keep the ClientRef, which is a combination of the
two fields in the "Corporate Client Details" Table. Upon doing so, I was
still unable to recall the data from the query using the lookup wizard. Is
there a way around this?

The other problem that I have is that I want the alpha figures in the
ClientRef to appear in uppercase. I have enabled this to happen in the table,
but it does not in the query when I combine the two fields together. Does
this matter that it doesn't appear in the query, or is it that once I manage
to combine the "ClientID Number" and "ClientID Name" in a single field I can
then set it to view in uppercase?

Thanks in advance
Katharine


"Jamie Collins" wrote:


Jeff Boyce wrote:
I have seen considerable debate about using autonumber primary keys. One
school appears to subscribe to the notion that a natural key can (and
perhaps even 'must') be found for every row/record.


Good timing: I put an autonumber (incrementing INTEGER) into a database
just one hour ago. I needed to know the relative order in which rows
were being added to the table and autonumber is convenient. The table
already had a natural key being a composite of the entity's attributes,
in the Dr Dodd tradition; I'd have been in a whole lot of trouble if I
didn't.

I've looked after databases where a natural key exists and an
autonumber is used as an 'alternate' key. I haven't sought to change
this because there is some justification for this approach e.g. a
single INTEGER column foreign key is more convenient to code than a
multi- text column key. There may be some storage and performance
benefits but I haven't investigated this myself, a correct data model
being my primary consideration.

Another school
subscribes to the notion that an autonumber primary key simply provides a
unique row/record identifier.


Using an autonumber as a key where no natural key exists is asking for
trouble. It is uniqueness merely for the sake of it (I'd rather have a
'heap' i.e. without a key and technically not a table at all). You must
expose the autonumber for it to be useful and almost everyone agrees
you should never expose an autonumber in this way.

We need to be clear about the meaning of PRIMARY KEY in Access/Jet.
Here is a pertinent newsgroup post from Joe Celko: "Dr. Codd started
with the concept of a PRIMARY KEY in his first writing; it was the old
sort key from sequential file processing and magnetic tape storage in
disguise. It was awhile before Dr. Codd changed his mind and said that
all keys are equally keys, and we don't need a special one in a
relational database, like you did in a tape system. Unfortunately, the
first SQL systems were build on existing file systems and the idea of
the PRIMARY KEY had become part of the language... The UNIQUE
constraint lets you have multiple keys on a table." So in relational
terms, there is no difference between a key defined as UNQIUE and a key
defined as PRIMARY KEY.

In the Access/Jet implementation, PRIMARY KEY has special meaning but
it has nothing to do with relational keys. Allow me to quote myself
g: "PRIMARY KEY has special meaning for Jet [Access]. In relational
terms, a key is a key and PRIMARY KEY has no special meaning. However,
PRIMARY KEY has traditionally taken on a meaning particular to each SQL
product and Jet is no exception. For Jet, the special meaning is that
PRIMARY KEY provides the only means of specifying the clustered index
for a table. The clustered index is non-maintained, meaning the index
is only 'physically' rebuilt when the file is compacted; it is,
however, still maintained 'logically' between compacts. Some people
have a problem with the term 'clustered index' as regards Jet (don't
make the mistake on thinking 'not supported' means 'does not exist') so
let me clarify: PRIMARY KEY determines the physical ordering on disk.
For best performance, a table should be physically ordered on the
columns of its 'primary' use i.e. those used in GROUP BY and BETWEEN
clauses. Think of a paper copy telephone directory, which can obviously
have only one physical order: if I'm using it primarily for search such
as 'get listings for people whose last name begins with the letter C'
then I'd want it ordered on last name because it would take me no time
at all to rip out the required pages. Similarly, it pays to choose your
PRIMARY KEY carefully and in accordance with its implications for
physical ordering on disk. If you are using PRIMARY KEY to mean a
UNIQUE constraint/index only then your database may be taking a
performance every time a table with a poorly chosen PRIMARY KEY is
queried (pay no attention to the message that tells you, 'A table must
have a primary key for you to define a relationship between this table
and other tables in the database,' it lies, it lies). And this is why
an autonumber is usually a poor choice for PRIMARY KEY: how useful
would you find a paper copy telephone directory physically ordered on
telephone number?"

Given that PRIMARY KEY affects physical ordering and nothing else, I
can see no justification for an autonumber *solely* being PRIMARY KEY.
There may, however, be some justification for including an autonumber
in a *composite* PRIMARY KEY assuming it was the last column specified
in the PK's definition e.g.
PRIMARY KEY (key_col1, key_col2, ..., autonumber_col).

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

Similar Threads
Thread Thread Starter Forum Replies Last Post
REPOST - I need to make the IDNumber an Autonumber - HOW?? melwester General Discussion 1 August 4th, 2005 09:17 PM
Import data into a table with an autonumber field Martin Watts Database Design 2 March 8th, 2005 08:08 PM
Access 2000, autonumber fields Zyberg74 General Discussion 3 November 17th, 2004 05:24 PM
Problem with AutoNumber accessmonk Database Design 2 September 30th, 2004 08:57 PM
Autonumber Ally H. General Discussion 7 August 27th, 2004 04:51 PM


All times are GMT +1. The time now is 06:46 PM.


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