Restart Autonumber

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

"James" wrote in message
om...
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 (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn

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.

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?

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada

http://www.attcanada.net/~kallal.msn