Autonumber using alpanumerics

[Guest]

I have created a client database for individual clients, which is working
well. My next assignment is to create a corporate database on Access 2000,
which understandably will be more complex in nature. There are two elements
of the database that I would like advice on; I hope I'm not asking for too
much in this thread. Basically I need a unique reference for each client.
Initially I had planned to use an autonumber, but I see the drawbacks of this
system in my initial database. I am wondering if it is still possible to have
some type of autonumber format as a ClientRef, using alphanumerics. The
format I want the ClientRef to follow is
000AAA, whereby the numeric figures are random numbers and the alpha figures
are relative to the client name, or if the client has many branches within
the group, then the alpha figures would represent the client name and the
branch name/location. I want this facility to be flexible enough that I am
able to manually create the alpha element of the reference . I am asking this
because (although there have been many references to the irrelevance of an
autonumber format if the client is to see/refer/have access to it) I want to
use it as a reference where the alpha section of the ClientRef will form part
of the InvoiceRef, therefore appearing in this format: AAA/00/00/0 whereby
the alpha figures are the client name/location, followed by the month, then
the year, and finally the invoice number for the month in question. If this
format were not possible by using the autonumber function, then I would
welcome any long-term feasible suggestions.

Thanks in advance
Katharine

 

[Jeff Boyce]

Katharine

While the Access Autonumber data type is not, as you've learned, suitable
for what you are trying to do, there would be no reason you couldn't create
your own automatic numbering procedure (search for "custom autonumber" -- a
misnomer, but a strong search term).

By the way, your design may benefit from a bit more normalization. Your
"000AAA"-patterned "ID" includes more than one fact in the field, not a good
idea. Instead, use two fields in the table, and use a query to connect
(concatenate) them together for display.

Regards

Jeff Boyce
<Access MVP>

 

[Guest]

Hi Jeff

Thanks for the advice. I had just created another field in order to fulfil
part of the dilemma prior to reading your post. Your suggestion to
concatenate the two using a query should help to solve the remainder of my
problem.

Thanks for the prompt response.

Katharine

 

[Jamie Collins]

Your "000AAA"-patterned "ID" includes more than
one fact in the field, not a good idea.

A single ISBN contains many facts and is called an 'intelligent key'.
Why do you think this is not a good idea?

a bit more normalization

Please be more specific e.g. which normal form are you recommending?

Jamie.

--

 

[Jeff Boyce]

Jamie

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, since this represents a
violation of First Normal Form design. The underlying notion is "one fact,
one field".

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

My comment on normalization was a "may help" not a recommendation, as I did
not have a full understanding of the current design.

Regards

Jeff Boyce
<Access MVP>

 

[Jamie Collins]

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

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

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

http://groups.google.com/groups?q="one+fact+one+field"+-group:microsoft.public.access.*&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.

--

 

[Rick Brandt]

Is this the same consensus that recommends autonumber primary keys, by


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

http://groups.google.com/groups?q="one+fact+one+field"+-group:microsoft.public.access.*&qt_s=Search

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


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?

You're confusing how the data is stored with how it is "consumed". The fact
that ISBN numbers are published as a monolithic value containing multiple
pieces of information doesn't mean that this would be the proper way to
store them in a relational database. In fact it would not be.

 

[Jamie Collins]

You're confusing how the data is stored with how it is "consumed". The fact
that ISBN numbers are published as a monolithic value containing multiple
pieces of information doesn't mean that this would be the proper way to
store them in a relational database. In fact it would not be.

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

Jamie.

--

 

[Jamie Collins]

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

.... which would end up being split across multiple tables: RegionCodes,
PublisherCodes, etc. Then you'd have to do multiple JOINs and
concatenate the data, just to be able to apply the ISBN's check digit?

 

[Rick Brandt]

So are you saying that in your hypothetical 'books' DBMS you would
parse out the elements of the ISBN and persist them in separate
columns?

Jamie.

If the way those bits are combined back together and the data they represent
is consistent I would yes.

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.

 

[Jamie Collins]

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.

--

 

[Rick Brandt]

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.

 

[Jeff Boyce]

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>

 

[Jeff Boyce]

Jamie

I'm replying in a separate thread to your "aside" comment re: 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]

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.

--

 

[Jamie Collins]

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.

--

 

[Rick Brandt]

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.

 

[Jamie Collins]

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.

--

 

[Guest]

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

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.