Text as Primary Key vs Auto Number

[Galaxiom]

The only thing I would add (regarding tehNellie's comments) is that if the PK is going to be used elsewhere as an FK, then DEFINITELY I would make the PK a surrogate in order to simplify the implementation of Access requirements for parent/child table relationships. I.e. you must have a PK on a parent in order to link a child to its parent.

I don't see why this condition should make the slightest difference whether a natural of surrogate key is used. If a natuaral key is available then the process of linking is exactly the same.

Indeed it is also quite simple to link tables on a compund key and there can be reasons to use them too.

Moreover I have already resoundingly debunked the claims for adding superfluous surrogate keys in post #46. My points stand unopposed because none of those promoting such use has addressed any of them.

 

[gemma-the-husky]

I don't see why this condition should make the slightest difference whether a natural of surrogate key is used. If a natuaral key is available then the process of linking is exactly the same.

Indeed it is also quite simple to link tables on a compund key and there can be reasons to use them too.

Moreover I have already resoundingly debunked the claims for adding superfluous surrogate keys in post #46. My points stand unopposed because none of those promoting such use has addressed any of them.

not debunk, just preference. I like the idea of a surrogate key for managing relationships, as it never needs changing, thereby avoiding any possibility of cascading updates. I like the efficiency of single field numeric keys. But the nature of the keys does not add anything to the integrity of the dbs structure at all.

 

[The_Doc_Man]

From tehNellie's post:

If there is a need for some other candidate data within that table to be functionally unique, and in my experience it tends to be a compound series of columns, then I constrain it with a unique index.

From Galaxiom's comments on my comments:

I don't see why this condition should make the slightest difference whether a natural of surrogate key is used. If a natuaral key is available then the process of linking is exactly the same.

tehNellie's post implied compound natural keys. I use a surrogate SINGLE key and just constrain the compound keys for disallowing duplicates as needed. Perhaps I should have emphasized that point. That is why my method is simpler for linking. Otherwise, if the parent table has a 3-field compound key, who wants to duplicate that key through every table? It sort of defeats the purpose of normalization.

 

[Galaxiom]

I use a surrogate SINGLE key and just constrain the compound keys for disallowing duplicates as needed. Perhaps I should have emphasized that point. That is why my method is simpler for linking. Otherwise, if the parent table has a 3-field compound key, who wants to duplicate that key through every table? It sort of defeats the purpose of normalization.

I do not disagree with surrogate keys where the alternative is a wide compound key. I notice you jumped to three fields in your comment which virtually shows you acknowledge that a two field compound key is justifiable.

That of course can be true as I showed in my earlier post.

What I object to are claims that every table should be keyed with a surrogate PK. I simply isn't true.

Most design decisions have both costs and benefits and adding unnecessary keys costs processor time and storage space. That can be a reason to avoid adding a surrogate key that serve no important purpose.

 

[gemma-the-husky]

I do not disagree with surrogate keys where the alternative is a wide compound key. I notice you jumped to three fields in your comment which virtually shows you acknowledge that a two field compound key is justifiable.

That of course can be true as I showed in my earlier post.

What I object to are claims that every table should be keyed with a surrogate PK. I simply isn't true.

Most design decisions have both costs and benefits and adding unnecessary keys costs processor time and storage space. That can be a reason to avoid adding a surrogate key that serve no important purpose.

Probably a good example would be a us state table. Do you add a numeric code to each stare, or do you stick with their standard 2 letter codes.

Daft as it seems, I am so used to using numerics, i would probably add an autonumber!

 

[Galaxiom]

Probably a good example would be a us state table. Do you add a numeric code to each stare, or do you stick with their standard 2 letter codes.

Daft as it seems, I am so used to using numerics, i would probably add an autonumber!

What do you gain by doing that?

 

[RainLover]

Simply repeating your unsubstantiated assertion in no way lends the slightest support to that assertion. Indeed it further calls into question its validity since its claimant, when pressed, has been unable to offer a single advancement on what is essentially, "I said so".

A waste of space so far.

The PK, by definition, is indexed. I also indexed fields that need to be searched. There are often several.

Same rule applies for either method.

In the case of my work environment (a small financial institution), the Member field (Long) is pervasive in almost every transaction. It is an excellent natural key that uniquely identifies the client.

That is nice to hear, but adds nothing to the discussion.

If I included a surrogate key as the PK of the Member table the Member field in the Members table would still have to be indexed to provide rapid search facilities by Member. Moreover I would have to translate the the Member field from the text reports that are parsed to obtain the original data and convert them to the utterly pointless surrogate key.

I am not sure what you are saying here but it sort of sounds like you are doing all this work instead of the computer which these days is more than capable of doing all this in a fraction. If you could explain exactly what you are talking about and that you are willing to work with me I would put this on a test bench that would record the times over a long period of time. Say overnight.

All queries by Member would have to include a join to the Members table.

I don’t see a problem with this.

There is no advantage whatsoever joining to the member table by an artificial key. Hence a redundant field and its index is being maintained for no purpose. I believe your case is shot to pieces.

I still stand by using AutoNumber as a preferred Primary Key. I have never seen this fault. I have seen on many occasions difficulties with a Natural Key.

I will point out for others. Not you Galaxiom as I am sure you do understand this. A KEY is used to uniquely identify a record. If Table A had a record using an Autonumber and that number was 12345654321 then that record would be related to other table where the Foreign Key is also 12345654321. This could happen with a variety of Tables.

I do not expect the AutoNumber to be sequential. I do not see a need for the AutoNumber to be seen and therefore knowingly used by the user.

I can and do use the Number behind the scenes.

 

[RainLover]

So how do you expect your end users to identify the information in such a table? Your auto-number key won't help them uniquely identify anything because you've already told us you intend to hide it from them. Yet you don't appear to be suggesting any alternative. That's why I'm saying your point is irrelevant to the discussion. By introducing an arbitrary and therefore irrelevant surrogate key you aren't addressing the critically important issue of unique identification.

If a table lacks any natural key at all then users are left in the abysmal situation of working with duplicate data. Many of us are familiar with the messes created by database systems that don't support data integrity. Even when you use a surrogate key a natural key is still just as important, so introducing a surrogate doesn't in any way relieve the database designer from the responsibility of identifying and implementing the natural key for that table - i.e. you would still need to create key constraints and indexes for the natural key as well as the surrogate.

Button

You ask a good question.

"So how do you expect your end users to identify the information in such a table?"

Can you explain for the exact same situation how do you expect your end users to identify the information in such a table.

 

[ButtonMoon]

Button

You ask a good question.

"So how do you expect your end users to identify the information in such a table?"

Can you explain for the exact same situation how do you expect your end users to identify the information in such a table.

With a Natural Key of course. I asked the question only because you seemed to be implying that there was no need to bother with natural keys because one could always use a surrogate instead.

Natural keys are essential in good database design. They are no less essential just because you use surrogate keys. So it's wrong to imply (if that was what you meant) that using a surrogate somehow relieves the database designer from the work of selecting or designing or managing the use of natural keys.

 

[RainLover]

With a Natural Key of course. I asked the question only because you seemed to be implying that there was no need to bother with natural keys because one could always use a surrogate instead.

Natural keys are essential in good database design. They are no less essential just because you use surrogate keys. So it's wrong to imply (if that was what you meant) that using a surrogate somehow relieves the database designer from the work of selecting or designing or managing the use of natural keys.

You appear to be suggesting that I am proposing something that I am not and then you refer to your own Database in an incorrect manner.

Just because you somehow use a field in your searching you give it the status of Primary. I am sure if you were asked to describe what a Primary key is then your answer would have something more to do with the defining of each Record within the table.

A primary Key is a unique identifier.

If you were to use a person's name then this would not be good. John Smith. You could not use either one on its' own and even when you combine them they are still not unique. So maybe we could add the DOB, their Mother's maiden name. So if this was a simple table you would have the following Fields. FirstName, LastName, DOB, AddressLOne, AddressLTwo, City, Spouse and PostalCode.

A difficult table to deal with. Best solution is to add an Autonumber. Without the Autonumber you most likely would need the entire field that form the primary. This could be four fields. Now this is messy. Setting up the Indexes could bring the computer to a halt, according to some.

 

[RainLover]

What I would like to know is in a situation where multiple Fields are required to locate/create something unique about a record do you actually use this in your searching. That is, do you use a combination of say 4 fields or whatever the count has to be to make a search.

And how would you attach a sub form. Again would you use the multiple fields to find a parent child situation.

It has been so long since I have done this that I honestly have forgotten how to.

 

[ButtonMoon]

What I would like to know is in a situation where multiple Fields are required to locate/create something unique about a record do you actually use this in your searching. That is, do you use a combination of say 4 fields or whatever the count has to be to make a search.

Yes of course. Users initiate searches and they will presumably want to search on a variety of things and not just the key attributes. Users will not generally need to search for things based on a surrogate key, so if the surrogate key is used to support users' queries at all then it requires an extra lookup to be performed based on other attributes (in technical terms it is sometimes called a "bookmark lookup"). I expect you know this but I'm not sure where your question is leading.

In response to your previous post, it's correct that of course any candidate key (not just the "primary" key) is a unique identifier for data in a table. Users usually need to be able to identify the information in a table and use unique identifying attributes to relate that information in the table to real things that are of interest to them in the world outside the database. To do that they need unique identifiers that work in reality - i.e. natural key attributes that are recognisable and useable by human beings and are guaranteed by key constraints within the database. Normally the natural key will be indexed because it is typically used in searches and because unique indexing is usually the best (or only) way to guarantee that uniqueness is enforced.

Surrogate keys are another thing altogether. They don't replace natural keys. They simply solve certain technical issues for database developers while being of no special relevance to users of the database.

 

[RainLover]

Yes of course. Users initiate searches and they will presumably want to search on a variety of things and not just the key attributes. Users will not generally need to search for things based on a surrogate key, so if the surrogate key is used to support users' queries at all then it requires an extra lookup to be performed based on other attributes (in technical terms it is sometimes called a "bookmark lookup"). I expect you know this but I'm not sure where your question is leading. In response to your previous post, it's correct that of course any candidate key (not just the "primary" key) is a unique identifier for data in a table. Users usually need to be able to identify the information in a table and use unique identifying attributes to relate that information in the table to real things that are of interest to them in the world outside the database. To do that they need unique identifiers that work in reality - i.e. natural key attributes that are recognisable and useable by human beings and are guaranteed by key constraints within the database. Normally the natural key will be indexed because it is typically used in searches and because unique indexing is usually the best (or only) way to guarantee that uniqueness is enforced. Surrogate keys are another thing altogether. They don't replace natural keys. They simply solve certain technical issues for database developers while being of no special relevance to users of the database.

Do you think that there is anything stopping me with my AutoNumber from doing the exact same search as you can. Of course there is nothing whatsoever. So why do I create something different with the AutoNumber. Take all those Fields that you use to create something unique. We could have any number of these for each Table. In fact Table_B may have four fields to create a reference to Table_A. Then if we look at the other side there may be Five fields to create a unique reference to Table_C. Instead of using all of these for our join we use the AutoNumber that has been assigned to that record.

You may be thinking that I am being ridiculous with my joins and I would have to agree. I would go further to say that it would be unlikely that you would create such a messy design. But I have seen this in many situations. Apply this design to 20 tables. View this in the Relationships window. You have a mess where I have a tidy setup one only join between two tables.

Teaching someone how to use AutoNumber is relatively easy. Creating a Unique key with a combination of fields is a little difficult.

The follow up from here is to explain how you would go about creating / selecting a Primary Key or as in many cases designing one by joining fields together.

 

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