Restart Autonumber

Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

--
Good luck

Jeff Boyce
<Access MVP>

Nov 13 '05 #5

James

I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<O8**************@TK2MSFTNGP09.phx.gbl>...

Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #6

Mike Preston

On 16 Jul 2004 21:31:03 -0700, dr*********@hotmail.com (James) wrote:

I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
There have been many threads over the years on this issue. People
line up on one side or the other and have strong feelings about the
mental acuity of those that feel differently. ;-)

But either system works as long as there are enough safeguards built
into the programming. It just takes fewer safeguards if an autonumber
is used, IMO.

mike

"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<O8**************@TK2MSFTNGP09.phx.gbl>...
Traci

In addition to the cautions noted by Doug in his response, be aware that if
your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #7

James

Not sure how this relates to what I interpreted as the original point -- how
can I restart my numbering system? My caution was that any time you
re-initiate a primary key sequence (whether an Autonumber or a "hand-rolled"
custom ID function), you risk orphaning "child" table rows (at best), or
causing totally unrelated child table rows to be connected to the wrong
"parent".

And if there are "child" records, neither an Autonumber or a "hand rolled"
ID function should resequence to "fill in" for missing values in the
sequence. Unless, of course, you have the additional procedures to modify
ALL the child records!

The topic of "natural" keys vs. arbitrary, unique IDs does show up a lot --
and I suspect the discussions border on "religious beliefs". But I'm not
sure that a custom-built procedure that generates a sequential, unique
identifier could be consider a "natural" key.

--
Thanks for the thoughts

Jeff Boyce
<Access MVP>

Nov 13 '05 #8

In message <63*************************@posting.google.com> , James
<dr*********@hotmail.com> writes

I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field.
What you have to bear in mind is that there's a difference between a
table and an entity. Entities are in the logical data structure and
tables are in the physical data structure.

Whenever there's a difference between the logical and physical data
structures there is a risk of data corruption.

Joe Celko says that autonumbers don't exist in the logical data
structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. The problem
is that that 1:1 relationship isn't inherent in the data, it has to be
enforced by the programmer. If that isn't done then you get situations
where, for instance, a customer's order is entered twice and gets two
different autonumber "Order Numbers."

For that reason it's safest to use a natural key if there is one
available. Adding an autonumber doesn't create a natural key of there
isn't already one in the real data. If there is one already then the
autonumber is probably redundant.

There are times when you need to denormalise the table for performance
reasons, and that may involve adding an autonumber. With current
database systems you are less likely to need this, storage is so cheap
that it's often better to duplicate key fields (even compound keys) than
to add an autonumber.

The most difficult situations arise when you deal with data about
people. There is no simple and easy to use natural key for people. Over
in comp.databases.theory I once suggested that the precise latitude,
longitude, altitude and time of birth would make a good natural key if
we could persuade people to record it.

I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.

That's called an update anomaly, and it's expected when you work with
denormalised data. It's up to the programmers to build system where that
type of anomaly can never occur.

There's also an insert anomaly when the same order gets entered twice.
Again, it's up to the designers to build systems where that can't
happen.

So my advice is take a good long look at the logical data structure and
try very hard to find a natural key. Use that if you can, and only use
autonumbers if you are forced to. There are two reasons for doing that.

The first is performance, to avoid having to match long key fields or to
avoid storing long fields both as primary key and as foreign keys.
Modern systems mean that these cause less problems than they once did.

The seconds is that sometimes you don't have a natural key available,
such as when you are dealing with data about people. At these times you
just have to accept that your data is likely to have errors in it, and
there are limits to what you can do about it. You can choose to use
someone else's autonumber field (for instance a social security number)
or create your own. When you create your own then you need to put
procedures in place to make sure that there is always an exact 1:1
relationship between your autonumber field and the people it is supposed
to identify. You can't do that in software.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #9

John Baker

James wrote:

I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone.

There's nothing wrong and most things right with Autonumber as a primary
key. The mistake is when people try to do the thing you're talking
about. Once a primary key has been set, it should NEVER change, short
of some sort of massive system-wide upgrade (and then rarely).

Nov 13 '05 #10

James

> Because there is absolutely no way that you can define a natural key

that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.

Is there really anything in life that is guaranteed to NOT change? I
supposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)

Nov 13 '05 #11

--CELKO--

>> Joe Celko says that autonumbers don't exist in the logical data
structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)

The problem is that that 1:1 relationship isn't inherent in the data, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order is entered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.
I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!

Nov 13 '05 #12

John Nurick

On 17 Jul 2004 10:27:05 -0700, dr*********@hotmail.com (James) wrote:

Is there really anything in life that is guaranteed to NOT change? I
supposed there are a few things, like the race of a person.

In practice "race" is an administrative classification and therefore
subject to change.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Nov 13 '05 #13

Mike Preston

On 17 Jul 2004 10:27:05 -0700, dr*********@hotmail.com (James) wrote:

Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
Is there really anything in life that is guaranteed to NOT change?

No, there isn't. Thanks for making the point for me. ;-)

Isupposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)

And you did so on what grounds again?

mike

Nov 13 '05 #14

In message <18**************************@posting.google.com >, --CELKO--
<jc*******@earthlink.net> writes

Joe Celko says that autonumbers don't exist in the logical datastructure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)
I did think of that when I wrote it.
The problem is that that 1:1 relationship isn't inherent in thedata, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order isentered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.
It's quite possible for there to be two natural keys in an entity, but
neither the Dewey nor LoC codes are natural. They are both examples of
autonumbers that have become visible outside the systems that created
them.

In an entity where there are two or more natural keys it is unlikely
that either could change independently of the other, because either key
uniquely identifies an object. If a key changes then the object it
identifies changes.

I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!

They are part of the data that is used to generate the British
equivalent of the US social security number (SSN), it's called the
National Insurance number or NINO. There have been a few incidents where
people with the same name have been born in the same place on the same
day, and have been given identical NINOs.

That illustrates a point about autonumbers. If you trust the autonumber
issued by some third party you can use them as if they were a natural
key. Some American organisations have used the SSN as a primary key in a
table and discovered that it's not quite good enough to be trusted as if
it really were a natural key.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #15

John Nurick

Bernard, we're in Humpty-Dumpty country here. You're using the term
"autonumber" in a quite different sense from other people.

For the rest of us, an "autonumber" is a unique value _that the DBMS
itself assigns_ to a _record_ when the record is created, typically
though not necessarily sequential or random, but in any event unrelated
to other attributes of the entity represented by the record.

Attributes such as SSN, ISBN, Dewey or LoC "numbers" are _not_
autonumbers in this usual sense. First, the mechanism by which they are
assigned is outside the DBMS. Second - and in consequence - they are
assigned to the _entity_ (the person, book, subject or whatever), not to
the record representing it. IOW for the purposes of the DBMS they are
natural attributes.

Third, Dewey, LoC and ISBN - I don't know about SSNs - are meaningful
"smart keys" which can be parsed to obtain values of various attributes
of the entity. Generating or assigning subject classifications such as
Dewey and LoC is ultimately a matter of librarians' judgment.

On Sun, 18 Jul 2004 15:16:06 GMT, Bernard Peek <ba*@shrdlu.com> wrote:

In message <18**************************@posting.google.com >, --CELKO--
<jc*******@earthlink.net> writes
Joe Celko says that autonumbers don't exist in the logical data

structure, I disagree. I think that autonumbers appear in the LDS as
separate entities with a 1:1 relationship to another entity. >>

I am living in Salt Lake City, so LDS has another meaning to me :)

I did think of that when I wrote it.
The problem is that that 1:1 relationship isn't inherent in the

data, it has to be enforced by the programmer. <<

Exactly right: autonumberings is not in the data. It is created
outside the reality of the data model in the PHYSICAL storage;
unverifiable, random, irreproducible and proprietary. Would you take
a drug that uses that kind of "science"? No. So why do you run
databases like that?
you get situations where, for instance, a customer's order is

entered twice and gets two different autonumber "Order Numbers." <<

It is worse if there is a real key; one UPDATE uses the autonumber and
another uses the real key. If I were using two reald keys -- say,
Dewey Decimal and Library of Congress in a library -- I can
co-ordinate them. But with a phyasical locator, I am screwed.

It's quite possible for there to be two natural keys in an entity, but
neither the Dewey nor LoC codes are natural. They are both examples of
autonumbers that have become visible outside the systems that created
them.

In an entity where there are two or more natural keys it is unlikely
that either could change independently of the other, because either key
uniquely identifies an object. If a key changes then the object it
identifies changes.

I once suggested that the precise latitude, longitude, altitude and

time of birth would make a good natural key if we could persuade
people to record it.<<

That (location and birthdate) is the Swedish nation id number!

They are part of the data that is used to generate the British
equivalent of the US social security number (SSN), it's called the
National Insurance number or NINO. There have been a few incidents where
people with the same name have been born in the same place on the same
day, and have been given identical NINOs.

That illustrates a point about autonumbers. If you trust the autonumber
issued by some third party you can use them as if they were a natural
key. Some American organisations have used the SSN as a primary key in a
table and discovered that it's not quite good enough to be trusted as if
it really were a natural key.

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.

Nov 13 '05 #16

"James" <dr*********@hotmail.com> wrote in message
news:63**************************@posting.google.c om...

Because there is absolutely no way that you can define a natural key
that will never change, nor one that is guaranteed to be unique
throughout all of time. Autonumbers are both as long as the
appropriate RI have been established. At least they are supposed to
be - there was a bug in Access 2000 I believe, that has since been
fixed, where autonumbers were duplicated, but that was a bug.
Is there really anything in life that is guaranteed to NOT change?

PKs are supposed to be stable. That isn't the same as saying they won't ever
change.
I supposed there are a few things, like the race of a person.
Whhhaaattttt? Go on, Mr Anthropologist, define human race for us. And lets
see what the mess we end up in there. Presumably that was your idea of a
joke.
You might
think social security #'s, but people even receive new of those from
time to time. ;-)
Well, if you live in a country with social security numbers. And even in the
USA they have been duplicated, apparently.
My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)

Ha ha.

Primary keys are a problem. We're 'supposed' to take them from 'natural'
data. Something 'about' the instance of the entity type uniquely identifies
it. Something we could describe in words will make it possible to
distinguish it from another instance of the entity type. You're right, once
you start looking for an absolute guarranteed unique PK it gets a bit
difficult. Especially when we remember that in the relational model PKs
can't include nulls.

So whaddya do? The best you can within the constraints of the fact that the
relational model is an attempt to organise a world of things which isn't
relational.

Is it persons? You could put a unique (maybe even no nulls) index on first
name, last name, DOB (those 3 alone would be pretty strong, depending on
number of records), SSN, 1st Line of address etc. Then use an autonumber as
your 'PK', actually surrogate key.

It's always going to be a compromise.

Mike

Nov 13 '05 #17

James

-
No, there isn't. Thanks for making the point for me. ;-)

Then why do we worry about establishing a PK that will never change,
especially when the database provided the ability to cascade updates
to all related records?

I
supposed there are a few things, like the race of a person. You might
think social security #'s, but people even receive new of those from
time to time. ;-)

My IT manager at work even goes so far as to say that PrimaryKeys
should be totally meaningless in a table; in other words, if your PK
is made up of any part of your data, then you are not using a correct
PK. I had to kindly disagree with him ;)

And you did so on what grounds again?

The grounds that I used was the opposite opinion of his; that a PK
should be a representation of the existing data in a table. For
another, if a query a foreign reference table and the PK is one that
makes no sense, then I have to go and join 1 or more tables just to
determine what the data is related to.

Nov 13 '05 #18

James

> And if there are "child" records, neither an Autonumber or a "hand rolled"

ID function should resequence to "fill in" for missing values in the
sequence. Unless, of course, you have the additional procedures to modify
ALL the child records!

My wording was wrong; I didn't mean resequencing on a hand rolled
method, but if the tables have to be recreated, then the relationship
is easily re-established because there are values to related to in
each table, unlike an autonumber field which will contain all new
values.

Nov 13 '05 #19

In message <ko********************************@4ax.com>, John Nurick
<j.*************@dial.pipex.com> writes

Bernard, we're in Humpty-Dumpty country here. You're using the term
"autonumber" in a quite different sense from other people.

For the rest of us, an "autonumber" is a unique value _that the DBMS
itself assigns_ to a _record_ when the record is created, typically
though not necessarily sequential or random, but in any event unrelated
to other attributes of the entity represented by the record.
An autonumber is created in the database, but what is its counterpart in
the logical data structure which is independent of the database?.

Attributes such as SSN, ISBN, Dewey or LoC "numbers" are _not_
autonumbers in this usual sense.
But they are, the difference is that they are created in someone else's
database.
First, the mechanism by which they are
assigned is outside the DBMS.
It's outside the database which we control. It's in someone else's
database. That is irrelevant when considering the logical data
structure.
Second - and in consequence - they are
assigned to the _entity_ (the person, book, subject or whatever), not to
the record representing it. IOW for the purposes of the DBMS they are
natural attributes.
Natural keys are ones where the 1:1 mapping between the entity and the
database representation of the entity is guaranteed. If you trust the
agencies who generate keys like the SSN then you can treat them as if
they were natural keys, even though they are only autonumber fields in
those agency's own databases.

Third, Dewey, LoC and ISBN - I don't know about SSNs - are meaningful
"smart keys" which can be parsed to obtain values of various attributes
of the entity. Generating or assigning subject classifications such as
Dewey and LoC is ultimately a matter of librarians' judgment.

I agree, and this was sloppy thinking on my part. The Dewey decimal code
isn't actually a primary key in its usual sense. The Dewey code doesn't
uniquely identify a book, it only identifies a category into which books
can be classified. The LoC classification is similar.

The ISBN identifies a title and not a book. To be even more specific it
identifies one or more editions of a specific title. To identify an
individual book a library will use another code, typically an
autonumber, called the Accession Number.

The SSN is supposed to identify a unique individual, although there have
been mistakes in that mapping. In the UK the equivalent code is
generated from the time and location of the individual's birth, and if
more than one person is born in that place and time a serial number
(essentially an autonumber) is also used.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #20

Albert D. Kallal

Why would you EVER care what id ms-access uses for the relation?

Do you care what memory segment word gets loaded into? Do you
care if it is memory segment 32, or 8192?

Who cares about a number you, and your users will NEVER see?

An autonumber is some mechanistic to generate a number. To you and me, all
we care about is that we have a relation from customers to customers invoice
table. Do we really care, or have to know what number is used?

Really, when word loads into memory, we don't care about the number used for
the memory location. Really, when ms-access has a relation between customers
and the invoice file...again we don't give a hoot about what number is used.
Me, or you never sees the segment number when word loads, and we as users
will never see the autonumber either.

These numbers are NOT for humans to see.....

There is a ZILLION kinds of internal numbers that your computer uses all day
to function. Why do you care what memory segment numbers the computer used
to load ms-access, or ms-word? Why care?

If your folks can see, or use those autonumbers...then that is your problem
with autonumbers.

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number? Even if you change
the order number, again..why should your database not work? Maybe you need
to delete the order number? (again, what on planet earth does deleing some
arbitrary number like the order number HAVE ANYTHING to do with building a
functional relation between two tables? How possibility does these two
separate concepts have anything in common?).

You users should NEVER EVER see a autonumber.

You mistake here is to try and let humans see, or even refer to, or use the
autonumber in any way. Autonumbers are NOT to be given meaning by
humans...but ONLY to your software.

Who cars if you have a order number, or not? What does the fact of having a
order number have to do with your database to functionally correctly? If you
want to require that a order number HAS to be entered, then make the order
number a required field, but that simple stupid order number HAS NOTING to
do with setting up a relation between two tables.

Setup your relation between tables with internal numbers, and your database
will JUST WORK REGARDLESS of what fields, and things you decide to store as
data. Do not go and attached some number out of the blue like a stupid order
number to build relations between your tables. Can you imagine if products
like QuickBooks, or even products like ms-word exposed internal numbers used
for relations and other internal numbers as to how the software will
function? Software uses ZILLIONS AND ZILLIONS of internal numbers and
pointers to function.

Now that YOU ARE the software developer, it is up to you to hide these
numbers. You can expose these internal numbers (like autonumbers), but that
is just rude, and just services to torture your users. Hide all the internal
number stuff....every other developer before you did this....
--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.attcanada.net/~kallal.msn

Nov 13 '05 #21

Alan Webb

James,
Before the turn of the milennia I managed to kick off a long winded and
passionate thread complete with troll posts and flames on the issue of
whether arbitrary or significant keys are better. This tends to be a bit of
a sore subject for some. I maintained then that in all cases arbitrary keys
are better. Since then I've had consulting contracts where I inherited a
schema that used significant keys and imposing my own arbitrary keys would
have unleashed a great deal of havoc for the client. I did the smart thing
and left things as they were. These days I tend to include both an
arbitrary key maintained by the rdbms that is the primary key and does
uniquely identify that row in a table as well as any significant keys the
client needs to maintain compatibility with their existing systems.
Processor, disk space and memory is cheap these days so any overhead
generated by the additional columns is probably cheaper than an overhaul of
the schema to shift to or from an arbitrary or significant key naming
convention.

"James" <dr*********@hotmail.com> wrote in message
news:63*************************@posting.google.co m...

I have been reading many posts by users that claim to have years of
experience with Access that promote the use of Autonumber generated
values as primary keys. Don't get me wrong; I use Autonumber values as
primary keys for standalone tables, such as those that really do not
have a primary key unless you use multiple key fields. But I keep
asking myself why anyone would create an architecture of two related
tables upon which the relationship is based on an Autonumber key
field. I'm currently working on one right now where I'm converting an
"Orders" table that uses Autonumber to generate Order numbers. This
Autogenerated value is then stored in the OrderDetails records. If the
scenario that you describe below occurs, guess what? You've just lost
your relationship between the two fields, because you cannot enter
your own values in an Autonumber field, and your OrderDetails table is
useless because the reference to the Orders table is gone. The
OrderDetails table will use an Autonumber key to basically serve as a
surrogate primary key, but I use a "natural" key that I control -
which in this case is my own "rolled" invoice #. The database I'm
converting is going to use my own "rolled" method, upon which a
function will return an incremented value from a table. With the
experience I've had with tables related based on Autonumbers, I will
never willingly design one to be as such.
"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message

news:<O8**************@TK2MSFTNGP09.phx.gbl>...

Traci

In addition to the cautions noted by Doug in his response, be aware that if your table's primary key (?your Autonumber) is used as a foreign key in
one/more child tables, "restarting" your Autonumbers could hose the
relationship between parent and child records.

Nov 13 '05 #22

James

> PKs are supposed to be stable. That isn't the same as saying they won't ever

change.
I supposed there are a few things, like the race of a person.

Whhhaaattttt? Go on, Mr Anthropologist, define human race for us. And lets
see what the mess we end up in there. Presumably that was your idea of a
joke.

HAHA! Ok - I was trying to give a basic example, but apparently this
wasn't a good choice. Point taken! :-)

Nov 13 '05 #23

James

Albert,

Your passion to the subject at hand is duly noted :-)

However, I have had to work on several MS Access databases where the
original developer decided to use autonumbers to relate all the tables
to each other. The ones that I address were corrupted and the only way
to recover the data was to basically recreate the database and import
the data. When your autonumber values are all regenerated at this
point, it is a nightmare to recover the relationship - at least it is
for me. If you have an easier way, then I'd sure like to know what it
is.

I recently built an application (ASP.NET and SQL) that created an
entry ID that the user did care about - and when the user saved the
record, an ID was generated (not autonumber either :-)) but the user
was not able to see it until an approved process was followed. In your
example of where an invoice number might not be created until later,
but an entry was still allowed, I think a better solution would be to
store this in a temp table, and then move it to an actual "Invoice"
table after an invoice number is assigned. If my table already has a
unique identifier that has meaning, I just see no need to add another
field for an identifier that has absolutely no relation to my data.

Here's another example I'm considering:

I'm mulling over constructing a membership database for my church.
Since a natural key would be hard to come with (most people would not
be willing to give up SSN, if I even wanted to use that), my thoughts
are to assign a membership ID from a custom rolled function that
basically increments a integer value in a single row, single column
table. This basically will be my "autonumber", only that I am
controlling it, and it will have an "artifical" relational meaning
because I can look up users - as well as anyone else - via their
membership ID. It will then be upon this membership ID that relations
to other tables will be established. If the database crashes - then
fine, I can deal with that, but I don't want to worry about my
database reassigning membership ID's if I were to base it on an
internally controlled, incremented value when the database is
recreated, or the data has to be repaired by importing into another
table of the same structure.
Thoughts?
James

Nov 13 '05 #24

In message <3iDKc.53536$Mr4.10789@pd7tw1no>, Albert D. Kallal
<Pl*******************@msn.com> writes

The real wrong being done here is that users can see, or use the
autonumbers. I mean, do you want ms-word to start showing you the memory
segment numbers it uses to load a document into memory? It would be crazy to
force users to deal with memory segment numbers when using word.
That isn't really a problem at all. The fact that the US government
allows people to know their own SSN doesn't really present a problem at
all.

With ms-access, YOU NOW are the software developer. So, just like those
developers who creased word, they don't show users what memory location the
documents load into. You as a developer has a responsibly to NOT LET USERS
see the autonumber.
No, that's not true. The limitation is that once you have published the
autonumber you can't change it.

If you need some number for your users, then you need to write your own
custom code that generates those numbers for human consumption (say, things
like invoice number etc.). You do NOT want to use the invoice number for
relations etc (you still use a internal autonumber, and that way you don't
even care if the invoice has a invoice number, or perhaps you wait a
specified time until a invoice number is given. Either way, you can still
have your relational database function...but behind the scenes it is using a
autonumber).
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.

Your database should not crap out just because you don't have a order number
handy. Who even cares if you enter a order number, or not? Why should your
database stop function if you don't enter a order number?
Because your users need a key to retrieve information from the database.
If you don't have a good natural key then you have to use an autonumber
instead.
Even if you change
the order number, again..why should your database not work?

Your database will work. Your data processing system will not, because
it includes the users who want to get data out of the database. Changing
the number inside the database does not change the numbers in their
head, or on existing documents.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #25

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...

So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.

You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoice
number?

Nov 13 '05 #26

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:wI**************@shrdlu.com...

In message <63*************************@posting.google.com> , James What you have to bear in mind is that there's a difference between a
table and an entity. Entities are in the logical data structure and
tables are in the physical data structure.

Nope. Entities are a way of describing things in the conceptual model,
relations are used in the logical model. The conceptual model and the
logical model are not the same thing.

Nov 13 '05 #27

James

Actually, it isn't all that uncommon to need to rebuild a "autonumbered"
table. One of the ways to do this is to create a new table definition, with
an autonumber field, then append from a source table/query, and append in a
new autonumber.

Regards

Jeff Boyce
<Access MVP>

Nov 13 '05 #28

James

Yes, I agree - but my point is that everything will be completely
renumbered, and relationships built upon the autonumber will very
likely no longer be valid.

In this "original" state:

The values might be:

1,My #1
2,My #2
3,My #3
4,My #4

If # 2 is deleted, for example, then rebuilding the autonumber will
result in:

1, My #1
2, My #3
3, My #4

etc.....

"Jeff Boyce" <Je**********@msn.com-DISCARD_HYPHEN_TO_END> wrote in message news:<#Y**************@TK2MSFTNGP10.phx.gbl>...

James

Actually, it isn't all that uncommon to need to rebuild a "autonumbered"
table. One of the ways to do this is to create a new table definition, with
an autonumber field, then append from a source table/query, and append in a
new autonumber.

Regards

Jeff Boyce
<Access MVP>

Nov 13 '05 #29

James

Alan -

I can see your point. I didn't just decide in my case to stop the
presses and undertake this mission; I was in the process of converting
the backend portion to SQL Server and when I saw the mess that the
developer (in this case, a manager of the company who had just
completed a level 1 MS Access class.......) had made. That is why I
chose to do this in this scenario.

Nov 13 '05 #30

In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.

You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoice
number?

I wouldn't use the autonumber function in Access for that job, but
something similar is the usual way of creating invoice numbers.
Typically it's wrapped up in a transaction.

I've already pointed out that one of the disadvantages of autonumbers is
that people try to read some subtle meaning into them, which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #31

"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:eo**************@shrdlu.com...

In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes
"Bernard Peek" <ba*@shrdlu.com> wrote in message
news:ay**************@shrdlu.com...
So now you want to create two autonumbers and hide one of them. Don't
forget that the invoice number is itself an autonumber.
You use autonumbers for invoice numbers? What happens if you cancel the
insert of a record? Won't the auditors be interested in the missing invoicenumber?

I wouldn't use the autonumber function in Access for that job, but
something similar is the usual way of creating invoice numbers.
Typically it's wrapped up in a transaction.

You've confused the issue by using the term autonumber out of context. The
OP clearly was talking about the Access things called autonumbers.
I've already pointed out that one of the disadvantages of autonumbers is
that people try to read some subtle meaning into them,
Well, if you're talking about your system generated numbers being used as
invoice numbers, then I'm sorry, invoice numbers do have meaning.
which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.

Why?

Nov 13 '05 #32

In message <40***********************@news.aaisp.net.uk>, Mike MacSween
<mi******************@btinternet.com> writes

which is why it's
often desirable to deliberately obfuscate the way the sequence runs. For
some purposes I would prefer a sequence that is predictable but not
obvious.

Why?

The last time I needed to do this we were issuing a new identity code
for about 150,000 items owned by around 20 different companies. I didn't
want there to be any obvious correlation between the numbers issued and
the company they were issued to. So I issued numbers in blocks of
between 1,000 and 5,000 and rotating between companies. I also made sure
that there were numbers issued internally so that there would be gaps in
the range.

If there appears to be a pattern in the numbers then some people will
build business processes on the basis of this perceived pattern. Then
when you come to change the system you may find that you are constrained
by other people's misinterpretations. Worse still the predictability of
the numbers may be a security risk, I've already quoted an example where
a predictable number sequence cost millions in fraud.

--
Bernard Peek
London, UK. DBA, Manager, Trainer & Author. Will work for money.

Nov 13 '05 #33