Alternative to AutoNumber

[RainLover]

Curious, as you're the one who said it:

Paul I did not answer your question because I felt you asked it just to tickle my Ivories. It added nothing to the conversation at hand.You may feel that I should be able to justify my statement, and indeed I can. But in order to do that I would need to supply a big bunch of code together with a story that explains.Once you have that information you most likely would look for another fault. Like if I spelt the word Cat with a K.Paul If you want to ask a serious question then please do. I would think that you should accept what I say on face value. We do this most of the time, so give a guy a break. If you have a serious question then ask. If not then try to think more along the line of being helpful rather than destructive.

 

[Solo712]

You need to be careful with DMax+1. It should be generated immediately before the record is saved otherwise it is possible that another user will get the same result. If there are many users I would hesitate to use it.

If you write the DMax + 1 as the last statement in the Before_Update Form event, the chances of duplicates are miniscule, even on slow networks. And if that happens, an error-trapping routine can reissue the ID without any fuss. I am not aware of any other substantive issues.

Best,
Jiri

 

[Solo712]

Here's my €0.01

In addition to previous comments:

When you consolidate two databases with tables having a autonumber key field as a PK in both databases might prove difficult....

Bingo ! One of the reasons why I stopped using autonumbers in most cases. If eg. in a distributed database a record being inserted has a duplicate PK, the Access engine will assign a new autonumber and your referential integrity goes down the drain.

Best,
Jiri

 

[RainLover]

If you write the DMax + 1 as the last statement in the Before_Update Form event, the chances of duplicates are miniscule, even on slow networks. And if that happens, an error-trapping routine can ID reissue the ID without any fuss. I am not aware of any other substantive issues.

Best,
Jiri

The chances are indeed miniscule. But it is still possible. Just for the fun of it I wrote a Procedure where it is not possible to duplicate or miss a number. It works under my testing which was a couple of loops requesting a number over a long period.

I remember that I ran it over night but I can't remember the exact number of hits. I would think that it was a few 100,000 times.

 

[Solo712]

The chances are indeed miniscule. But it is still possible. Just for the fun of it I wrote a Procedure where it is not possible to duplicate or miss a number. It works under my testing which was a couple of loops requesting a number over a long period.

I remember that I ran it over night but I can't remember the exact number of hits. I would think that it was a few 100,000 times.

Ok, Rain, then you understand the probability of getting a duplicate hit on a second try through the error-trapper would be max one in ten billion. I still don't see a problem.

Best,
Jiri

 

[gemma-the-husky]

another interesting thread about autonumbers, and numeric keys.

I think the issues are these

1. Why use a numeric key at all?
- because numeric keys are more efficient than strings

2. Why use an autonumber, rather than just a number?
- because in most cases all you want/need is a number/any number, and access can add the autonumber automatically without the need for any programming at all. In some cases (eg, a status code, or a VAT rate, or even a Branch Reference in a sales system), however, you may feel it better to control the numeric key values, rather than let the dbmanager allocate randomly. That's just a matter of system design,, and user interface.

3. Why use an auto sequence other than that provided by an autonumber?
- where more control is required, or where you need/want an intact sequence - or where you want to be able to create a sequence starting from a specific value. Personally I prefer to use a separate next value table, rather than a dmax+1, because you get more flexibility.

4. Visibility of numeric keys.
- absolutely no reason to hide them. It's useful in some cases to see them. But equally, there is no reason to design a system to expect users to need to know them.

5. "Real/Natural Keys".
I think numeric keys are there to make the dbs work efficiently, and make the programmer's task easier. For example, a single field numeric PK is much easier to handle than a multi-field key consisting of text fields.

A second great benefit of using a numeric keys as a PK, is that you do not need to propagate/cascade changes in real-world keys through the database.

However, where you use numeric keys, you almost certainly need a second natural-key to assure data integrity. For example, having a numeric key for a US State table does not in itself prevent you from having two rows for Florida, say. So you ALSO need a unique index on the "StateName" field.

 

[FuzMic]

Great insight from our dog Lover

 

[FuzMic]

After rereading man on the moon I have to say special word of thanks for another good insight

 

[RainLover]

4. Visibility of numeric keys.
- absolutely no reason to hide them. It's useful in some cases to see them. But equally, there is no reason to design a system to expect users to need to know them.

My simple reply is.

absolutely no reason to show them.

 

[gemma-the-husky]

My simple reply is.

absolutely no reason to show them.

I would say - can be useful to see them when investigating problems for users. I tend to make them greyed, or occasionally just locked, as sometimes I also find it useful to sort/filter on an ID.

I do agree with the premise that users should not need to interact with them, because the actual values should not need to be known. I just think "never" is too strong.

 

[ButtonMoon]

My simple reply is.

absolutely no reason to show them.

No reason to show numeric keys? Well that certainly depends on the key and on whether there are alternative keys available for the user. For example if the numeric key is an invoice number then presumably that is the principal method used by the business and by customers to identify invoices. Very likely there is no other key for invoices. That is one very good and very common reason for using a numeric key. A numeric invoice number is a numeric key that generally needs to be visible to the user.

The problem with these kinds of discussion is that everyone may have different examples in mind when discussing keys. That's why it only makes sense to discuss them in very general terms rather than proposing dogmatic rules which, practically speaking, don't always make sense.

 

[pbaldy]

Paul I did not answer your question because I felt you asked it just to tickle my Ivories. It added nothing to the conversation at hand.You may feel that I should be able to justify my statement, and indeed I can. But in order to do that I would need to supply a big bunch of code together with a story that explains.Once you have that information you most likely would look for another fault. Like if I spelt the word Cat with a K.Paul If you want to ask a serious question then please do. I would think that you should accept what I say on face value. We do this most of the time, so give a guy a break. If you have a serious question then ask. If not then try to think more along the line of being helpful rather than destructive.

I've deleted a long response. I'll simply say I did ask a serious question, and have gotten nothing relevant in response.

 

[RainLover]

No reason to show numeric keys? Well that certainly depends on the key and on whether there are alternative keys available for the user. For example if the numeric key is an invoice number then presumably that is the principal method used by the business and by customers to identify invoices. Very likely there is no other key for invoices. That is one very good and very common reason for using a numeric key. A numeric invoice number is a numeric key that generally needs to be visible to the user.

The problem with these kinds of discussion is that everyone may have different examples in mind when discussing keys. That's why it only makes sense to discuss them in very general terms rather than proposing dogmatic rules which, practically speaking, don't always make sense.

Hi Button.

A good response. Thanks.

My rule is dogmatic, I will give you that. However if you looked again I do say "I" not "YOU MUST"

I am giving an example of what I do. As always if you want to do it differently then that is your business not mine.

I have seen examples where using a Natural has been difficult because of uniqueness. They end up with a composite Primary. I have seen a Database where the vast majority of the Tables had Composite keys.

This created a mess. I found it difficult to follow what was going in. Especially as they had layers of Sub Queries. The worst Database I have ever seen.

Compare this type of design to one where the Primary is Auto Number. By always using Auto Number things become much easier. Try writing a select statement with Composite keys as described. It is very easy to run into trouble.

My Tables queries etc are pretty well much the same as others. The only difference is that I add an additional field, namely a Auto Number. This Number is only used to identify records. Therefore the writing of select statements etc become a little easier when it comes to the join.

Most people do not realise that all I am proposing is that you create your Tables the way you normally would. No need to change anything you are currently doing so there should be no argument there.

Then ADD to your design One extra Field and that is the Auto Number as the Primary Key.

Now what could possibly be wrong with that. Then if you don't like it don't use it. As I said, I do not tell people that they must use Auto Number. I simply state what I do and what I will continue to do.

 

[RainLover]

I've deleted a long response. I'll simply say I did ask a serious question, and have gotten nothing relevant in response.

Paul

Read the next post of mine.

You have surprised me. I would have thought that you would have seen a vast array of databases and that you would have seen where a novice has run into troubles with their work.

I was wrong wasn't I.

 

[Galaxiom]

I have seen examples where using a Natural has been difficult because of uniqueness.

Then the field would not be even be a candidate key!

They end up with a composite Primary. I have seen a Database where the vast majority of the Tables had Composite keys.

This created a mess. I found it difficult to follow what was going in. Especially as they had layers of Sub Queries. The worst Database I have ever seen.

Compare this type of design to one where the Primary is Auto Number. By always using Auto Number things become much easier. Try writing a select statement with Composite keys as described. It is very easy to run into trouble.

Once again, as you invariably do in discussions about autonumber, you compare autonumber with a composite key as though a composite key is required if one does not use an autonumber.

Moreover, how the number is allocated is utterly irrelevant. A Long field can be used as a key in exactly the same way. Many tables work perfectly well with a single field natural key.

My Tables queries etc are pretty well much the same as others. The only difference is that I add an additional field, namely a Auto Number. This Number is only used to identify records. Therefore the writing of select statements etc become a little easier when it comes to the join.

Using an autonumber PK makes absolutely zero difference to the querying compared to any other single field key.

Most people do not realise that all I am proposing is that you create your Tables the way you normally would. No need to change anything you are currently doing so there should be no argument there.

Then ADD to your design One extra Field and that is the Auto Number as the Primary Key.

Now what could possibly be wrong with that.

I understand entirely what you are doing. What you ardently refuse to acknowledge is that in many cases adding an autonumber to a table is a superfluous overhead, requiring an extra index when another indexed field could just as easily be used.

As I said, I do not tell people that they must use Auto Number. I simply state what I do and what I will continue to do.

In this very thread you actually asked that I make no comment about your advice to always use an autonumber in every table. You make a habit of telling people that this is what should be done then argue the case ad nauseum as you have done on this thread.

 

[pbaldy]

Paul

Read the next post of mine.

You have surprised me. I would have thought that you would have seen a vast array of databases and that you would have seen where a novice has run into troubles with their work.

I was wrong wasn't I.

We're both surprised then, because your antagonism on this thread surprised me. Yes, I've seen many databases and lots of novices running into trouble. What I haven't seen is problems when writing code due to a user-visible autonumber field. That was the statement you made to which my simple, and I thought civil, reply was:

What problems writing code would you run into with a user visible autonumber?

I was asking for my own education, as I've never pretended to know it all. I guess I can say I learned something, it just wasn't what I expected.

 

[RainLover]

What problems writing code would you run into with a user visible auto number?

I did not intend my statements to be read in the manner you are reading them. This is the fault of my poor English, not something you did.

Hiding the key has nothing to do with the overall design of the table.

What I was trying to say in general terms was that writing a statement is simpler with an auto number than natural keys.

I suggest that hiding the Auto number takes away from the user the opportunity to use it as an Invoice Number or some other set of consecutive numbers.

The Auto number’s prime use is to uniquely identify a record. Nothing more.

The use of a natural key has some limitations. There are times when you can't guarantee a natural key's uniqueness. This is when you create a composite key which complicates the code. The alternative is to use auto number. So why not use auto number all the time. It does in no way affect the way you would have preceded without it.

Paul,
Is there anything I have said here that you don't understand? I don't expect you to embrace it, rather accept it as a viable alternative to Natural Keys.

 

[gemma-the-husky]

Rain

Seriously - there is NO alternative to natural keys, or indexes/indices, if you will.The natural keys represent the underlying business rules.

All we are talking about is the identifier that is used to relate records in related tables, which needs to be unique on the one-side of the 1-to-many join.

A single-field numeric key (ie Primary Key) is much easier to manipulate than multi-field keys, and is likely to be more efficient, as well as never requiring cascading updates, as it never needs to change. But you do not have to use a single field numeric key.

That's all, surely?

Whether the actual value is visible or not is surely of no importance?

 

[RainLover]

Whether the actual value is visible or not is surely of no importance?

Only to the point that if it can't be seen then it is unlikely to be used for something like an Invoice Number, Employee Number etc

 

[pbaldy]

Maybe we've been focused on different things. I normally use autonumbers as you describe; a primary key not seen by the user. Sometimes however, I do expose it to the user, contrary to your "never". That's all I've been focused on, that "never". Thus my response to:

Only to the point that if it can't be seen then it is unlikely to be used for something like an Invoice Number, Employee Number etc

So what if it's used as an invoice number, employee number, etc? As long as the developer and user are aware of the potential "gotchas", like skipped numbers, what's the harm? Why go to the extra work of creating a second field and populating it, worrying about doing the DMax() + 1 at the last second so you don't get duplicates, etc? I already have a unique number with the autonumber, I don't care if it skips a number or whatever, I just need a number I can give to a customer so if he calls back we both know what record we're looking at.