Restart Autonumber

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

 

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

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

change.


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!

 

[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

 

[Mike MacSween]

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?

 

[Mike MacSween]

In message <[email protected]>, 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.

 

[Bernard Peek]

In message <3iDKc.53536$Mr4.10789@pd7tw1no>, Albert D. Kallal

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.

 

[Jeff Boyce]

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>

 

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

 

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

 

[Bernard Peek]

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.

 

[Mike MacSween]

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?

 

[Bernard Peek]

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.

 

[Jeff Boyce]

James

I believe you and I said the same thing.

It doesn't matter what the underlying mechanism is for generating the IDs.
What does matter is not "orphaning" child tables by resequencing a main
table's key.

Jeff

 

[Albert D. Kallal]

Albert,

Your passion to the subject at hand is duly noted

Yes..I was a bit too excited here!

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

The autonumbers should not be have been re-generated at this point.

The autonumbers do in fact remain the same if you export to another file. If
the database was so badly damaged that you could not read the autonubmers,
the likely hood of this is no greater, or less then your invoice number
field being damaged also.

If something happens to the actual numbers, then in both cases, the system
will fail. So, I don't see why you had to re-generate the numbers. In fact,
using update queries keeps the autonumbers the same. I am at a loss as to
why you had to re-generate the autonumbers here?

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.

Wow? start using some temp tables? Now, you developer team has to maintain
two sets of tables (and, if your database has ANY KIND of decent
normalizing, your simple invoice is likely to be 4 or 5 tables deep here.
Sorry, starting to use temp tables to solve a problem of NOT yet having a
invoice number is a rather huge expense and increase in developer time. Not
only do you now have to maintain two sets of tables, but now start writing
reams of code to append the data from one set of tables to another. Further,
if you add new fields, or even more tables to the design, then that append
code to move the data out will have to modified each time. Your idea is a
VERY VERY expensive idea in terms of labor and design cost. All of this
change to the system can simply be eliminated by hiding autonumbers from
your users!

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.

Yes, but business rules change all the time. This year, the boss might say
we are not going to use invoice numbers anymore, and just use some kind of
PO number. With your design, you could result in YEARS of development time
to make this simple change. In my example, we can change and forget about
invoice numbers and start using PO numbers tomorrow. This is just question
of freedom of design. There is no question that this number has no meaning,
and this is exactly WHY I am suggesting to use it.

Here's another example I'm considering:

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

As mentioned, I never had, or seen the problem of exporting data with the
autonumber. I seen NO evidence that restoring data from a crashed database
is any more difficult when auotnumbers are used as compared to some exposed
number. If the numbers and data can be read..then you can retrieve the data
in both cases.

Further, once again, but NOT using the membership ID number, you gain a LOT
more freedom. Some new people may not yet be members, but only visitors, but
you still want to track donations and attendance. Further, you obviously
will have some table structure that has Family->children.

Again, some families may attend, may donate money etc, but not yet have a
membership id. What do you do now? They are just visiting families.

Then you see all kinds of crazy stuff like well...ok memberships id's in the
8000 range are for visitors..and non members. Fact is, why even have to know
the future and worry about some problem(s) based on the fact that you MUST
have a member ID to function? And, if you have both visitors and
members..then you will have gaps in the membership id's. Fact is, why not
design your system to function with, or without membership id?

Further, some people in the church may have all kinds of things that change.
(divorce, have children, or children now become their own families etc etc).
Attaching a ID number to the relationship is going to reduce your ability to
move records around. If you need membership ID, then create a membership id.
However, once again, I don't see how a membership ID has anything to do with
your relational database functioning correctly just because you do, or do
not have a membership ID yet.

You don't care, or save the disk sector numbers on the disk drive. This is
just all interanal nuts and bolts stuff that the computer worries about...

 

[Albert D. Kallal]

No, that's not true. The limitation is that once you have published the
autonumber you can't change it.

Ah, but why can't I change the autonumber? That is exactly my point. Why
publish the autonumber? This is my WHOLE POINT!

Why and where did this rule come from? In fact, since users DO NOT care or
know about this number, I am in face FREE TO CHANGE the autonumber when I
want, and as often as I want, and how I want! This is complete freedom for
the developers of application, and they don't have to care, or worry, or
even be hamstrung by their users complaining that some internal number used
for relations changed on them.

The goal here is to give those software developers freedom to develop
software that simple works, and the user of the software should not care, or
haw to worry about this stuff.

In fact, my developers should be able to change that number as often as they
like. And, if we are smart and don't expose this number, then we DO HAVE
this complete freedom. Further, with modern database engines, cascade
updates of the child keys is a common thing, and even old systems like the
JET engine for ms-access has this feature.

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

Ah, perhaps some confusing here. We were/are talking about autonubmers (but,
really, the same applies to @indent fields in sql server).

So, no..., the invoice number SHOULD NOT be a autonumber. The real problem
here is that you have NO control of how autonubmers increment.

You also don't have control of what memory segment number ms-word loads
into. No one is suggesting to start tagging our word documents with memory
segment numbers, or perhaps the track and sector numbers on the disk drive.
These are just internal numbers stuff that the computer needs.

Really, the autonumber is the same thing, and there is NO control of how it
generates and creates numbers. It can even be set to random if you wish! A
invoice number for all practical purposes CAN NOT be a autonumber, as they
will and can change on you. So, for something like a invoice number, one
likely has to roll their own system to generate numbers..

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.

Sure, that makes sense.

However, for most searching of people, some customer id, or perhaps just
searching by name is MORE then sufficient. Maybe as the customer moves from
being a simple mailing contact to a full blown customer, then the a good
natural key will arise out of the data. However, in this modern fast paced
world, we OFTEN DO NOT on initial customer contact have a very good natural
key. Further, why should my dataprocessign system care about internal memory
segment numbers, or internal numbers used for relational between tables? In
fact, going all the way back to punched cards, as those cards are processed,
little care or need of some customer ID is needed for HUMANS to use.

Maybe all the results of the search will show pictures of the people, and by
how the person looks, I will pick that person!

Of course, at some point (perhaps while talking on the phone) a good natural
key may become available. I am all for using natural keys to search and find
the customer. But, what the heck does searching for customer have to do with
my application working, and what kind of disk drive I going to choose here?
Why should my customer id, or the kind of disk drive I use have anything to
do with me allowing relations between tables? Why such a HUGE restriction
here? You mean, I have to have a good natural key AND THEN my application
works? Or, if I choose IBM hard disk, the system will work, but with a
Fujitsu drive..it will not? These issues are that of the machine. I want
freedom here! Maybe I do have a good natural key, maybe I don't!

My software should work just fine with, or without a good natural key. I am
at a loss as to why identifying a customer has anything to do with ms-word
loading in memory segment FE1EE20000? (or some internal pointer number to
identify a relation between two tables?)

And, further, who cares is a natural key is, or is not available? Hum, I
often wanted to print a barcode on the forehead of each customer! ;-)

Good natural keys are great, and if you come up with a great natural key for
your system, then it certainly is useful. However, identifying customers by
some natural key should not effect the ability of my software to function.

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.

Exactly, and I should be free to change, or do whatever I want with those
internal numbers. Can you imagine if we restricting what part of memory
ms-word loads into based on some internal pointer number that some person
decided to use for customer id?

Why should your car have restrictions based on the name, and color of the
clothes of the person driving the car? The computer is a machine like a car,
and both should NOT expose their internal operating systems and numbers used
to function. Why use part of the machine design to identify customers?

The function of the computer should have little, or nothing to do with the
issues of users having some means to identify a customer. We certainly need
a way to identify a customer, but that has NOTHING to do with my accounting
system, or CRM system ability to function. (running the software that posts
the payroll at the end of the month does not care about a external, or
internal id used. It needs a number, but so does the fuel injection system
in your car).

And, my car should not care less about the license plate, or the color of my
skin, or the color of the paint, or my driver license number when I drive
it.

That car should just work!, and so should my software too!

There are certainly arguments and some advantages to using a natural key,
but most of the time that issue is one of identify a customer, not some
memory value or pointer used to connect (relate) two tables.

Why restrict the function and flexibility of the machine (or software) on
extra external stuff when we DO NOT have to?