Text as Primary Key vs Auto Number

[Galaxiom]

Rainlover, you keep banging on about using Autonumber as an alternative to a composite key. I agree that a single number field is generally better than a composite key.

However, earlier in the thread you stated:

There is one other rule I apply with Primary Keys and that is that it is never seen by the end user.

That statement completely rules out the use of any natural key as a PK even if it is a single field.

I have spelt out solid arguments against that position, none of which you have adequately responded to.

You simply claim that modern systems have the power to carry and process redundent information so the load doesn't matter. However, no matter how powerful a system, adding extra unnecessary work will bring it sooner to overload than need be as the data and number of users expand.

 

[ButtonMoon]

Do you think that there is anything stopping me with my AutoNumber from doing the exact same search as you can.

Rain, clearly we aren't speaking the same language. As an end user I don't see the autonumber column and therefore I can't and don't use it to identify things in the table - I need a natural key to do that. I can't and don't base my searches on it - I will use a natural key for that.

 

[RainLover]

Rain, clearly we aren't speaking the same language. As an end user I don't see the autonumber column and therefore I can't and don't use it to identify things in the table - I need a natural key to do that. I can't and don't base my searches on it - I will use a natural key for that.

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.

 

[RainLover]

Rainlover, you keep banging on about using Autonumber as an alternative to a composite key. I agree that a single number field is generally better than a composite key.

However, earlier in the thread you stated:

There is one other rule I apply with Primary Keys and that is that it is never seen by the end user.

That statement completely rules out the use of any natural key as a PK even if it is a single field.

I have spelt out solid arguments against that position, none of which you have adequately responded to.

You simply claim that modern systems have the power to carry and process redundent information so the load doesn't matter. However, no matter how powerful a system, adding extra unnecessary work will bring it sooner to overload than need be as the data and number of users expand.

I am mortally wounded. What I said was and still is incorrect.

I said

There is one other rule I apply with Primary Keys and that is that it is never seen by the end user.

What I meant to say was

There is one other rule I apply with AutoNumbers as Primary Keys and that is that it is never seen by the end user.

I failed to make that a distinguishable difference.

Galaxiom have you ever seen this happen.

You simply claim that modern systems have the power to carry and process redundent information so the load doesn't matter. However, no matter how powerful a system, adding extra unnecessary work will bring it sooner to overload than need be as the data and number of users expand.

And were you able to rescue the Database by removing the AutoNumbers.

I have seen some databases slow but never have I seen one that has been overloaded by the use of a Autonumber. After all isn't this indexing done at time of entry or when a record changes not when it is used to search as the indexing has already been done at this stage.

 

[Galaxiom]

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)

In all situations I find the AutoNumber which is the Primary Key and search for that.

Searching a database for a man called Bob who drives a pink Rolls is normal use. It begins with the information I have, his sex, name and car description.

Search by the autonumber is a rather odd notion since the autonumber is one piece of information that the user would never have, especially under your scheme where the user must never ever see the autonumber.

This is Microsoft's Default or Recommendation. Just build a Search Combo Box for your Form using the wizard where you are prompted to hide the Primary Key.

Microsoft is not the ultimate arbiter on the use of its products. They are well known for configuring to the lowest common denominator, especially in Access. The Wizard offers to hide the primary key because the table wizard defaults to adding an autonumber if you don't define a PK.

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

Anything that is guaranteed unique in the scope of the system can be used. The point I make is that any natural key needs to indexed to ensure uniqueness so adding the autonumber is just costing resources unnecessarily.

If the original key came from an autonumber on another system then all the more reason not to translate it to a localised autonumber in a downstream application.

Where I work the Unix system assigns a number to new members. I don't care how it does it really. That number is ubiquitous throughout our organisation. Data is translated into several formats including SQL Server and archived text based reports.

The member number is a perfect natural key for our client databases. It would be ridiculous to assign another key to represent that number in related tables.

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.

How many more times do I have to say this? I have not disputed that the autonumber is a convenient key where no natural key is safe. You have claimed that a suitable natural key should never be used as a primary key. I strongly disagree with this point alone.

I think you can see why I have said that the end user never gets to see the AutoNumber.

Because it is meaningless?

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.

In a Client database, clients can change their names. Knowing the PK of the client record provides a permanent link to old records. Printing that in a small font in the corner of a box takes up negligible space on an invoice.

 

[RainLover]

Galaxiom

While I do agree that Microsoft do things that are configured to the lowest common denominator their code still works,

Using Microsoft's way of searching I came up with the following.

Private Sub CboSearch_AfterUpdate()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[AutoNumberPK] = " & Str(Nz(Me![CboSearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    
End Sub

The Combo box used in this selects the Autonumber that is associated with the record that has the Pink Rolls Royce. The code behind the box then commits the search. The AutoNumber has not been seen by anyone including myself, although it would be easy for me to do so.

Once the Database is compiled into a MDE and Tables etc are hidden, (Going overboard here) No one ever sees the Primary Key which happens to be AutoNumber.

To tidy up one point. Where I have said that a Natural Key must never be used or words to that effect. I should say that there is nothing wrong with using a quality Natural Key.

Finding a quality Natural Key is often difficult. I would immediately rule out anything that requires the Help of AutoNumber to make it unique. Any field that cannot stand on it's own without the aid of another field is out.

Privacy issues is something to be considered separate to this debate.

 

[ButtonMoon]

The Combo box used in this selects the Autonumber that is associated with the record that has the Pink Rolls Royce. The code behind the box then commits the search. The AutoNumber has not been seen by anyone including myself, although it would be easy for me to do so.

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.

Finding a quality Natural Key is often difficult. I would immediately rule out anything that requires the Help of AutoNumber to make it unique. Any field that cannot stand on it's own without the aid of another field is out.

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.

 

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