Text as Primary Key vs Auto Number

[RainLover]

Great example! Your code doesn't do a search. The search is what you are saying the user has already done in a combo box. That's fine if your data set is small enough to eyeball in a combo box but no good at all if you need to search for one record among thousands or millions. To do that you would first execute a query based on the natural key (or whatever other attributes the user wants to search on). Note that your bookmark lookup based on the surrogate key is actually an additional operation - it's an extra overhead while doing absolutely nothing to improve the performance of the actual search.

Determining / designing natural keys certainly requires work but it's extremely important to do it. Failing to identify a natural key can ultimately be a lot more work because one day someone will probably have to clean up the data quality issues that result from misidentification/duplication of data. Some of us have had to deal with such clean-up operations a few times - especially in cases where the original designer just put in a surrogate key and clearly didn't bother to think about what natural keys were needed.

What do you mean by saying that the code does not do a search. And this bit about, it has to be for thousands of records is not really a search.

I can make it bigger if it was needed. I use much bigger on a daily basis. Or should I say my Database does. My object was to hide the Primary Key which you said was not possible. But as you can see with that one eye that is still open that I have completed the challenge without fail.

The code itself proves that you do not need to see the Primary. I have done that but you appear not to be able to see the wood from the trees.

I know what you are going to say. "This is only finding a Record not Searching for a Record"

 

[RainLover]

Determining / designing natural keys certainly requires work but it's extremely important to do it. Failing to identify a natural key can ultimately be a lot more work because one day someone will probably have to clean up the data quality issues that result from misidentification/duplication of data. Some of us have had to deal with such clean-up operations a few times - especially in cases where the original designer just put in a surrogate key and clearly didn't bother to think about what natural keys were needed.

You are now pointing out the evils of Natural Keys.

This is what I have been saying for ages.

Are you now changing your position on this.

 

[ButtonMoon]

You are now pointing out the evils of Natural Keys.

This is what I have been saying for ages.

Are you now changing your position on this.

I was not and have not changed my position on anything. I was pointing the necessity and value of natural keys. But as I already said, we don't speak the same language. Enough.

 

[gemma-the-husky]

What do you gain by doing that?

with US states, not much.

Except that if the US were EVER to change the state designations .....

As I say, it's just an ingrained habit I have, to add a numeric PK - often autonumber, sometimes just number.

 

[gemma-the-husky]

Button,

I think there is just one real misunderstand.

When either you or I design a particular table it is most likely that we do exactly the same thing to a point. The difference is in the addition of an Autonumber on my part.

If you were to search a Table you might search for the Name of the Man who drives a Pink Rolls Royce. So you are searching in this case on two Fields. In other situations it could be three or four but let's hope no more. (Poetry at its best) Let's call them all Natural.

In all situations I find the AutoNumber / Primary Key which represents the same Pink Rolls Royce and search for that. This is Microsoft's Default or Recommendation. To demonstrate, build a Search Combo Box for your Form using the wizard where you will prompted to hide the Primary Key.

I elect to use AutoNumber because it is simple and always right a far as a unique key is concerned.

You preference is to use something that appears naturally or at least semi natural. Where it does matter is when you can't find a single key that is both natural and unique. In this situation you have to use a composite key where I do not.

I have said that the end user never gets to see the AutoNumber. For those who say that sometimes it helps them. I hope they only mean in the Construction and Development stages. I can see no reason for knowing that this record is number 453627.

Galaxiom,

At this stage I have to ask how can your combination of Fields joined together to create a Primary Key be faster than a single AutoNumber.

Using an AutoNumber allows me to do anything that you can do. I don't believe it is slower than a composite Key but then I have never tested it in a proper testing environment.

EDITED due to some bad grammar.

Bit in bold. I don't think there is any difference here. I am sure Button (all of us) are saying that in that case you still need to add a suitable key/index to your table to ensure that there is only one Pink Rolls Royce. Indeed we/you probably have a table for colour, and a table for car model, so the constraint is on 2 numeric fields. You still have to determine the value of the autonumber key using other information in the table, as the autonumber itself contains no useful information.

 

[RainLover]

You can speak English as good as I can, if not better.

You said something as if you often spend time repairing a Database where an incorrect Natural Key was chosen.

Your choice you did not say that you have ever had a problem with AutoNumber.

In fact you have alluded to problems but have never backed them up. Can you do that or is this a case of never proven. Have you ever seen a case of AutoNumber problems.

 

[ButtonMoon]

You said something as if you often spend time repairing a Database where an incorrect Natural Key was chosen.

Your choice you did not say that you have ever had a problem with AutoNumber.

Read it again. I said I have spent time fixing data quality issues where natural keys were not implemented, i.e. where surrogates were used without bothering to identify and enforce any natural key.

 

[gemma-the-husky]

Whatever you do, some users manage to get a helluva lot of junk in their systems. Myriad ways of duplicating data by slight misspelling. How do they manage to do it? Beats me.

 

[RainLover]

Read it again. I said I have spent time fixing data quality issues where natural keys were not implemented, i.e. where surrogates were used without bothering to identify and enforce any natural key.

What is this about? I have not used anything other than Autonumber for more than the past 10 years or more. I have taught some young fellas and even taught my good friend ChrisO about table design.

My tables were never slow. I never had this strange problem of “Data Quality Issues” Notice the use of the word never.

I looked at Microsoft’s Northwind Database. It was a quick look and I intend to go back later.

There are 8 Tables.
There is one table Customers that uses a Natural Key. Well I think it is natural, I will let you decide while I continue. It failed on my first entry.
One table does not have a Primary Key but it does have two Foreign keys.
That leaves 6 Tables that use Auto Number.

But you said that AutoNumber causes problems. One would think that after 20 to 25 years that this database as per Button Moon’s Rules would have failed. But it hasn’t has it.

The fact is that you do not need to use a Natural Key in an Access Database. You can if it pleases you but it is not anything special. It too will have to be indexed.

Button you are concerned about the time it takes to Index a Field. Do you know when this indexing actually takes place? I think the answer to that makes a big difference in the speed of the Database.

Well that is enough for me for now. I will do something else while you find some examples of AutoNumber causing problems. Maybe you could send a quick note off to Microsoft informing them of their impending doom with regard to Northwind in particular and any other database that uses AutoNumber in general.

Just incase you forgot what to tell them, you were saying that AutoNumbers fail.

“I said I have spent time fixing data quality issues where natural keys were not implemented,”

 

[namliam]

Autonumbers are a god send, natural keys as primary keys?
Key part of a Primary key is "this value will NEVER change", natural keys do tend to have this stamp on them that they NEVER change... however...
What would be a natural person's natural key? Date of Birth + Last name?
What off twins? What of people who change their name at marriage?
Social security number? No because that can be changed (as a result of fraud or stolen identity and such) rare but it happens.

I have seen occations where a natural key was used for invoice numbers YYYYXXXX
Obviously Year and 4 sequential digits forced into a text field of 8 characters untill such time they needed more than 9999 invoices and they got stuck. Simple sample of a natural key gone bad.

Now a days not such a big issue, but databases in general perform better when using a single numeric key instead of a Text key or compound key. Not even taking into account storage capacity, but again not such an issue now a days.

 

[RainLover]

There are people here who really do understand.

Namliam and Gemma the Husky to name two. I could add Glaxiom as well because he fully understands also.

If you use an Autonumber as a first choice you can build your database with its relationships and it will run most efficently. This is what I do.

In some situations one may need to look further to achieve what is necessary. If that is the case then do it. I am sure we all have a special bit of code to do what we want.

The important thing is to have solid foundations. AutoNumber does that for you. It works 100% of the time if it is possible. If not then add something else. The same as for a Natural key only it reaches a point of failure much easier.

This discussion has now been done. If you have a simple question please ask, but this little ducky will not be debating any more.

Thanks Galaxiom. As usual we agree to disagree. But I think you are smarter than me because you have already bailed out.

Cheers all.