Alternative to AutoNumber

[The_Doc_Man]

1. In the USA, some "natural" keys are limited as data keys due to considerations of the Privacy Act and the HIPAA laws. Using an autonumber can solve this problem, though the DMax + 1 and a couple of other schemes would certainly work well on "non-natural" keys.

We cannot ever forget that a database has a PURPOSE (else who would pay us for it?) and we have to abide by ancillary business rules governing said database. The autonumber key is just one way to comply with a business rule that says what natural candidates you CAN'T use as a key.

2. Using a visible autonumber is no biggie. Let me ask this: When you do on-line banking and you get a long confirmation number, do you have ANY DOUBT that this number can or does contain an autonumber or DMax+1 component? Showing the number key as some sort of confirmation number doesn't violate anything if you want that particular number to be invariant for the lifetime of your system. (What do you think your banker would do if you asked him to edit the validation number on a transaction?)

As has been mentioned before, the problem with natural keys in this situation is that while they can perhaps stay unique, they might not be immutable. This would add a (small) layer of complexity to your design if you HAD to use mutable natural keys and therefore had to allow for key changes. Using the synthetic keys? No biggie. Those who don't like synthetic keys are confusing pure theory with the requirements of a database in the real world.

By now has anyone guessed that I'm primarily a pragmatist who has even been known to occasionally {gasp} denormalize tables for specific types of efficiency when dealing with slow networks? But seriously, I don't think I care what is theoretically correct. What I want to know is what will work to make my DB better. Usually, that "work better" question is where the "theoretically correct" stuff comes into play, but if "theoretically correct" leaves me wiggle room, I will wiggle! I also endeavor to avoid letting the cart go before the horse by seeing what I need FIRST and THEN deciding what aspects of pure theory help me get there.

 

[RainLover]

FuzMic the OP has asked a question that would tend to make one think that his experience is limited.

To answer his question one should take that into account. One way of doing that is to propose a solution that is not ambiguous.

By saying things like always use an Auto Number or hide the PK are simple statements which can be followed without any adverse affect. I believe that where possible one should maintain the same set of rules. Using Auto Number in a look up table is an over kill. However for the sake of uniformity I still use it.

Opposed to these rules is the use of Natural Keys. Privacy laws sometimes come into play. At least this is what I am told. Then there is the problem of finding something that is truly unique. If that can't be found the programmer looks at combining two or more fields to achieve a unique PK. When this becomes too messy or simply can't be found you are left with one choice. Use an Auto Number. So it would appear that Auto Number is the one thing that always works

This brings me back to the beginning. Always use Auto Number and you won't have a problem. true your system could do something nasty and destroy the harmony, but this can happen always.

Having said all that, I still feel comfortable saying ALWAYS use AutoNumber. Some have said here that always is too much. However if the use of an autonumber is correct then when would there be a situation where its use is unsuitable.

Paul, I would love to show you an example of a messy database because of its use of natural keys. What I have is something that has to do with Children. That makes it a no go situation.

I hope that I am getting close to justifying my position in a manner acceptable to you. Not for you to convert from the flat earth society. Simply acknowledge my contribution as valid.

I know I will never satisify Galixiom as he has had it in for me for a long time. But that is his problem to deal with not mine. I thing it was the way I said something.

 

[pbaldy]

Paul, I would love to show you an example of a messy database because of its use of natural keys. What I have is something that has to do with Children

Am I getting close to justifying my position in a manner acceptable to you.

Well, I'm not sure we're talking about the same thing. In the situation I'm talking about, I am using an autonumber as the primary key, thus really using a surrogate key. That said, I then expose the autonumber to the user, thus perhaps causing the confusion with a natural key. Since the program creates the number rather than the user, I still consider it a surrogate key. In all uses (joins, code, etc) it is used no differently than if it wasn't visible to the user, thus my question about what problems would be encountered resulting from it being visible.

I agree with you on natural keys. I manage a db that was created by someone else, and he used a combination of 3 natural keys as a composite key (driver/date/company). All the related tables have the same thing, so every query joins on the 3 fields. It is messy, and I would have used an autonumber field as the primary/foreign key.

 

[RainLover]

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

I am trying to get across the idea that the AutoNumber has but one purpose. That is to identify a record. That is its job and nothing more.

The harm it can do is confusion. User says hey what is this number for. Then they may try to use it for some purpose. There is not much more I can say. You should with your experience understand what could happen, no matter how insignificant that may seam to be. Then there is the newbie who hopefully follows the suggestion almost blindly until they in their own wisdom find something more to their liking. Can't think of what that may be.

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?

We go the extra mile because we get paid to do the job properly. The DMax you refer to is not guaranteed. You should have a copy of a multi user DMax written by myself and ChrisO. I believe I can guarantee this one. It has been tested on my private network but not used on a live database to the best of my knowledge. It is very complicated so I will leave it up to you if you want to try using it. It should be found amongst the pile of Databases that Chris gave you before he left us.

For something a little different. How often do you see a senior member say something like "The chances of that happening is so small you are safe to use it"

I think that we should write to cover all possibilities not the lack of probabilities. If there is the slightest chance that something might go wrong then compensate for it. Don't just say hey close enough is good enough.

I hope I have covered everything. If not speak up. I have to stop now. I have this condition that causes me pain when I do repetitive things. My right arm is killing me with pain from typing too much. I need a pill which is basically Morphine, so I will go down even further not being able to think totally clearly.

Trust you realise that I have edited my previous post. I hit Post rather than preview when I had not finished my reply.

Don't make too big of a deal about the DMax thing. It has not yet been tested live.

TTFN

 

[RainLover]

Auto Numbers.

Written by someone at UA.



What they are NOT

1. Row (record) sequence numbers.
2. An "order of entry into the table" number
3. A "gapless" series of numbers.
4. Editable numbers.
5. A series of (necessarily) always increasing numbers (see note 1 , below).
6. Intended to be viewed/used by end users of the application.
7. Predictable (as to what the previous or next one in the table is/or will be).
8. Reassigned, once deleted or discarded (exception: see note 2 , below).
9. A predictor/indicator of the number of rows in a table.
10. Intended to be used to "rank" or "sort" or "number" rows returned from the table (see note 5 , below).
11. Necessarily used to determine the default order the rows may be returned from the table (see note 6 , below).
12. Indicative of or related to any TimeStamp field that may also be in the table row (see note 7 , below).

What they ARE

1. Unique numbers used to identify individual rows in a table.
2. Automatically created by Access when a new row is "instanced" by Access (see note 3 , below).
3. Great/Outstanding/Essential for use as the Primary Key of a table.
4. Great/Outstanding/Essential for use as "link points" by Foreign Keys in other tables.
5. Unchanging, once assigned to a particular table row (exception: see note 4 , below).

1. Autonumbers are great, but you can not rely on the rows always having one contiguous series of numbers starting at 1 and incrementing by 1 to the number of rows in the table.
2. The primary use of autonumbers should be as the PK of a table, and then as the PK side of PK/FK relationship links.
3. Autonumbers should not be made visible to the users of the application.
4. Unless you specify an ORDER BY clause (either in the select or query or form or report order by property or via a GROUP BY clause), you truly have no idea what order the rows will be returned to you, and if they happen to be delivered in the desired sequence, then you are lucky (today, but there are no guarantees about the future).
5. If you want an ordered report, you must list the necessary fields to sort on, and autonumber fields are not considered to be good candidates for inclusion in this list.

 

[Minty]

I'm nowhere near as experienced as most of the people on this thread, but have been using Access, and MS SQL for a some time.

I personally have frequently (in fact mostly) used Autonumber / SQL Identity fields as the PK and on many occasions exposed it to end users.

When it's a used as a Job Number or Call number in the environment I use it in, it makes no difference if there are gaps in the sequence, it is exactly what it says on the tin - a unique reference that happens to be a number.

In my limited experience, Composite keys, even on look up tables, are a much harder things to manipulate and keep tidy. The DMax method is also more cumbersome if you do not need the unique unbroken sequence.

 

[Galaxiom]

I know I will never satisify Galixiom as he has had it in for me for a long time. But that is his problem to deal with not mine. I thing it was the way I said something.

A sure sign that someone has lost a debate is their choice to avoid addressing the points and attack the person instead.

I had thought better of you.

 

[Galaxiom]

In my limited experience, Composite keys, even on look up tables, are a much harder things to manipulate and keep tidy.

Now you have taken Rain's Composite Key bait as though Autonumber vs Composite Key is what this debate is about.

 

[Galaxiom]

I agree with you on natural keys. I manage a db that was created by someone else, and he used a combination of 3 natural keys as a composite key (driver/date/company). All the related tables have the same thing, so every query joins on the 3 fields. It is messy, and I would have used an autonumber field as the primary/foreign key.

Yes, Paul. You provide an example where the choice of composite keys may have been unwarranted. However that does not mean that using a Natural Key, even a Composite Key, is always a poor choice.

There are cases where a Composite Key does make sense for efficiency. For example, reporting on multiple entities that share a table. I'll use a simplistic example but it serves to demonstrate the concept.

Take a table where two or more entities hold Customers. You could choose a single field PK, CustomerID, and fields to record Entity and CustomerNumber within that entity.

Related tables, eg Invoices, have CustomerID as the FK. Reporting on Invoices by Entity requires a join with the Customer table to identify the invoices belonging to each Entity.

A Composite Key built from Entity and CustomerNo allows the Invoices for each entity to be reported directly from the Invoice table with no reference to the Customer table.

So we compare:

SELECT Invoices.EntityID, Sum(Invoices.Amount)
FROM Invoices
INNER JOIN Customers
ON Invoices.CustomerID = Customers.CustomerID
GROUP BY Customers.EntityID

vs

SELECT Invoices.Entity, Sum(Invoices.Amount)
FROM Invoices
GROUP BY Invoices.Entity

Which is simpler to code? Which is more efficient to process?

 

[Galaxiom]

But wait you may say. Other queries with the Composite Key will be so much less efficient as the engine must process two separate joins.

Well no. When a relationship is created, so is an index comprising the values of the two fields. When the engine is optimising a query it sees the join is comprised of those fields and use the index.

So to continue with the example above, the engine isn't actually processing:

ON Customers.Entity = Invoices.Entity
AND Customers.CustomerNo = Invoices.CustomerNo

but:

ON Customers.PKIndex = Invoices.FKIndex

However I digress to address Rain's Composite Index Phurphy. The real debate is about Surrogate Key vs Natural Key on which he is so far off the mark that the debate has become ridiculous.

I will address this tomorrow.

 

[RainLover]

Galaxiom

Now you are not only attacking me you have added Minty to your list simply because her opinion agrees with mine.

By reading the remarks of other people it would appear that some totally agree with me, some agree with reservation or agree up to the point where I use the word never.

On the other hand there are none so critical as you. How can I not feel that you have a personal Vendetta against me. When I propose something that is not to you taste you attack.

You should turn your energy to helping instead of looking for ways to interfere.

 

[Minty]

Now you have taken Rain's Composite Key bait as though Autonumber vs Composite Key is what this debate is about.

I'm afraid you may have read too much into my post - I was merely commenting that in most circumstances I personally have found composite keys to be more work where they have been required, and that for me the autonumber PK worked fine in almost every other case. I certainly haven't had a problem with a PK Identity field.

Forums are a lot like sending text messages - people often get the wrong end of the stick, or don't read the message properly or don't write down the way same they are thinking, and then you add in the language barrier...

 

[pbaldy]

I am trying to get across the idea that the AutoNumber has but one purpose. That is to identify a record. That is its job and nothing more.

The harm it can do is confusion. User says hey what is this number for. Then they may try to use it for some purpose. There is not much more I can say.

I would argue that I'm only using the autonumber to identify the record, nothing more. I'm just allowing it to identify the record for the user as well as for the database. There can't be any confusion, as it's presented to them as their reservation number.

Perhaps we'll just have to agree to disagree. I don't want you to be in any more pain than you already are. I hope your condition improves.

 

[The_Doc_Man]

I think that we should write to cover all possibilities not the lack of probabilities. If there is the slightest chance that something might go wrong then compensate for it. Don't just say hey close enough is good enough.

(From Rain Lover)

Yet the problem with this statement is that we are human and therefore fallible. We might not know all possibilities. In fact it might be theoretically impossible to know all possibilities if factoring in the amount of time it takes to elaborate the options of every branch of your code and determine what happens for each case.

There is also something called cost/benefit analysis. I ran into an example of this the other day when going through a system security review. The question came up about the risk of something that happens every 8 years (based my being on the job 27 years with the U.S. Dept. of Navy) and how much time/money you want to spend on programming a solution, when taking into account that regulations change faster than every 8 years and you can manually fix the problem in less time than you could possibly go through a program update cycle.

Rain Lover, I am not trying to making this personal, but I DO wish to point out that many times we have to strike a balance between excruciating precision and just coming as close as humanly possible. Given cost/benefit analysis, there actually IS a precedent for the phrase "good enough for government work." You have to cut yourself some slack sometimes because when trying to reach some programming goal, you literally might not be able to "get there from here."

Having said that, I would agree that if you KNOW that a particular situation exists and has a non-trivial probability, there is no excuse for ignoring it completely. The choice of keys (natural/synthetic) is a choice that can lead to awkward or confusing situations, which is why it is ALWAYS worthy of some brainpower ahead of time to determine if one of those choices is better than the other for your particular situation.

I happen to maintain the position that synthetic keys IN MY PERSONAL EXPERIENCE have performed better in the long run, but I must also acknowledge that some exceptional cases have occurred that made me choose natural keys.

 

[pbaldy]

Yes, Paul. You provide an example where the choice of composite keys may have been unwarranted. However that does not mean that using a Natural Key, even a Composite Key, is always a poor choice.

I don't disagree with you; my focus in this discussion was on the visibility of autonumbers. The comment you quoted was not intended to be an argument against natural or composite keys. I'm sorry if it came off that way.

 

[RainLover]

(From Rain Lover)


Rain Lover, I am not trying to making this personal, but I DO wish to point out that many times we have to strike a balance between excruciating precision and just coming as close as humanly possible. Given cost/benefit analysis, there actually IS a precedent for the phrase "good enough for government work." You have to cut yourself some slack sometimes because when trying to reach some programming goal, you literally might not be able to "get there from here."

Doc

I believe I am the better for reading your advice. I guess there are things that I have experienced that has help lead me to my way of thinking.

I need to change my thinking.

Thank You

 

[RainLover]

Is there any harm in hiding the PK if it were an Auto Number. I cannot think of a single problem. Can you?

Is there any harm in showing the PK if it were an Auto Number. I think there can be. If you were Audited and Invoice Number 4321 was completely missing would that not raise an issue with the auditor. So if you write a Database in this way then you could be unnecessarily confusing the situation.

I cannot see how the Auto Number Visible or not could be used as an Invoice number or any other similar situation unless the designer wrote the DB with the use of Auto Number in mind for Invoice number in the first place. The user should or would not have the type of flexibility needed to change things.

Just for the sake of the argument what would the user be thinking about this Number that stares at them from the form. If it does nothing other than look pretty, why show it in the first place.

Always hide the Auto Number, and just to make some of you happy, unless you have a purpose behind your decision.

Galaxiom. I know you are not a fool. You know exactly what you are doing when it comes to finding insignificant errors in my statements. It serves no purpose. So put your argument forward rather than take the aggressive approach against me.

 

[gemma-the-husky]

Is there any harm in hiding the PK if it were an Auto Number. I cannot think of a single problem. Can you?

Is there any harm in showing the PK if it were an Auto Number. I think there can be. If you were Audited and Invoice Number 4321 was completely missing would that not raise an issue with the auditor. So if you write a Database in this way then you could be unnecessarily confusing the situation.

I cannot see how the Auto Number Visible or not could be used as an Invoice number or any other similar situation unless the designer wrote the DB with the use of Auto Number in mind for Invoice number in the first place. The user should or would not have the type of flexibility needed to change things.

Just for the sake of the argument what would the user be thinking about this Number that stares at them from the form. If it does nothing other than look pretty, why show it in the first place.

Always hide the Auto Number, and just to make some of you happy, unless you have a purpose behind your decision.

Galaxiom. I know you are not a fool. You know exactly what you are doing when it comes to finding insignificant errors in my statements. It serves no purpose. So put your argument forward rather than take the aggressive approach against me.

bit in bold

In all likelihood, an auditor would try to confirm that all sales have been accounted for - therefore they would/ought to want to verify that the invoices number sequence is intact. (assuming that invoices numbers are supposed to be sequential)

in those circumstances a decent auditor would certainly enquire about "missing" invoices.

In point of fact, an autonumber would always be an AWFUL choice for an invoice number. (IMO).

I said this before, but when reviewing putative database errors notified by users, it is very useful to have the actual RecordID available. During development it is also useful to have the RecordID available.

In many, or even most cases I lean towards making a surrogate RecordID (whether autonumber or structured) visible but grayed, for precisely these reasons. Clearly a non-surrogate RecordID will be visible anyway.

 

[Galaxiom]

Galaxiom. I know you are not a fool. You know exactly what you are doing when it comes to finding insignificant errors in my statements. It serves no purpose. So put your argument forward rather than take the aggressive approach against me.

I can't work out what insignificant error you mean. I thought I had been putting my argument forward.

I did contemplate that perhaps the disagreement stems from what we define as a Natural Key.

The Social Security Number has been mentioned as a Natural Key (though not suitable through legal/privacy reasons). But is it really? It was allocated by another database.

However to me, anything unique in the data when it arrives has the potential to be a Natural Key. Most of my work is in secondary databases in a financial institution where I use the Member Number as the PK. It was allocated in the primary database so I guess it could be argued that it isn't a Natural Key either.

However this Member Number is pervasive in our systems and is always displayed. It is allocated sequentially by default but the user has the option to create any unused number.

I can see no reason why I should not use it as the PK.

 

[RainLover]

I can see no reason why I should not use it as the PK.

If you are completely satisfied that it is unique then there is no reason why you should not use it.

I would do something similar. I would use this as the Main identifier for all to see. However I would still have an Auto Number hidden away which is the real PK.

An example of why I would not totally rely on your PK is that it is possible to be wrong in the assumption that it is unique. This would be more likely to happen with a person who has little experience solving the problems in a commercial arena.