quick question regarding 0

[Cereldine]

Hello,

one of tables requires me to store sickness details, the sickness codes are all unique, they do however start with at least one 0 for example code - 000003 Description Stress. When you enter a code access automatically trims of the 0's to leave a 3.

Is there a way to override this im unaware of?

 

[Cereldine]

Just thought, i could override this problem by changing the field type from number to text! it is my primary key tho

 

[ScottGem]

Just thought, i could override this problem by changing the field type from number to text! it is my primary key tho

Fields should only have a number format if they will be used in mathematical calculations. Otherwise they should be text.

Also there is some debate over the use of natural vs surrogate keys. A natural key is data that has some meaning to the user. Like a telephone number. The problem is that natural keys may not satisfy the function of a primary key to be unique and unchanging. You can find more on this issue by searching the web on natural vs surrogate keys. I recommend using an autonumber field as the PK for all tables (a surrogate key).

That way you can use your code for display but the key field for more efficient joins.

 

[The Stoat]

I'd add a word of caution. Autonumbers are not strictly speaking PK's. In the sense that if you have this;

TBL_ADDRESS

HOUSENUMBER
LINE1
LINE2
LINE3
POSTCODE

In the UK at least HOUSENUMBER and POSTCODE are the natural PK's
If you set an Autonumber to PK in the tables design view then a user could add the same address more than once. I'd always set the natural key to be the PK and add an autonumber set to (no duplicates). The autonumber is then a surrogate key -i.e. it is always unique - but the PK is still in place and prevents referential integrity errors.

In your case if you know that the code is never going to change then that is a perfect PK and needs no autonumber.


TS

 

[neileg]

Actually, Stoat, it is possible to have the house number repeated within a street. There is no rule that prevents this, just comon sense. As we all know, common sense does not always work.

Plus, a building can have two valid postcodes, one which relates to the building itself and one for the street in which it lies.

 

[ScottGem]

I'd add a word of caution. Autonumbers are not strictly speaking PK's. In the sense that if you have this;

TBL_ADDRESS

HOUSENUMBER
LINE1
LINE2
LINE3
POSTCODE

In the UK at least HOUSENUMBER and POSTCODE are the natural PK's
If you set an Autonumber to PK in the tables design view then a user could add the same address more than once. I'd always set the natural key to be the PK and add an autonumber set to (no duplicates). The autonumber is then a surrogate key -i.e. it is always unique - but the PK is still in place and prevents referential integrity errors.

In your case if you know that the code is never going to change then that is a perfect PK and needs no autonumber.


TS

I strongly disagree here. The sole purpose of an autonumber datatype is to provide a unique identifier to a record. If you aren't going to use it for a PK then it has no value.

It is true that using an autonumber doesn't prevent duplicate data. That's the function of your indexes. In the example given you would create a unique index on the combination of Housenumber and PostalCode to prevent duplicate data . The purpose of a PK is NOT to prevent duplicate data it is simply to uniquely identify a record. Composite keys make it more cumbersome to do joins.

Referentialy integrity is not an issue between natural vs surrogate keys. The purpose of referential integrity is to prevent orphan records. It doesn't matter what the key is. Again, however, using composite keys makes joins more cumbersome, the same goes for enforcing referential integrity.

I strongly suggest doing some research on the pros and cons of this issue. I highly recommend using a surrogate key in the vast majority of cases based on my research and discussions of this issue.

One last point. Since your code should be a text field (to deal with the leading zeros), its more efficient to use a number field for indexes and key fields. That's why I continue to recommend using an autonumber for your PK instead of the code. These codes come from a third party (I believe) and there is a possibility they might change, you have no control over that.

 

[The Stoat]

Actually, Stoat, it is possible to have the house number repeated within a street. There is no rule that prevents this, just comon sense. As we all know, common sense does not always work.

Plus, a building can have two valid postcodes, one which relates to the building itself and one for the street in which it lies.

Ok ok it's a not perfect example but that's not the point. Where a natural PK exists it should be used. If you always use an Autonumber then there is nothing to prevent duplicate data being entered. If Cereldine sets an Autonumber to PK then she needs to restrict the sickness codes in some other way else you could have someone enter duplicates.

 

[The Stoat]

I strongly disagree here. The sole purpose of an autonumber datatype is to provide a unique identifier to a record. If you aren't going to use it for a PK then it has no value.

It is true that using an autonumber doesn't prevent duplicate data. That's the function of your indexes. In the example given you would create a unique index on the combination of Housenumber and PostalCode to prevent duplicate data . The purpose of a PK is NOT to prevent duplicate data it is simply to uniquely identify a record. Composite keys make it more cumbersome to do joins.

Referentialy integrity is not an issue between natural vs surrogate keys. The purpose of referential integrity is to prevent orphan records. It doesn't matter what the key is. Again, however, using composite keys makes joins more cumbersome, the same goes for enforcing referential integrity.

I strongly suggest doing some research on the pros and cons of this issue. I highly recommend using a surrogate key in the vast majority of cases based on my research and discussions of this issue.

One last point. Since your code should be a text field (to deal with the leading zeros), its more efficient to use a number field for indexes and key fields. That's why I continue to recommend using an autonumber for your PK instead of the code. These codes come from a third party (I believe) and there is a possibility they might change, you have no control over that.

You made no mention of indexes to Cereldine. I've seen numerous db's with autonumbers set as a PK and no indicies and consequently either a load of code to try and prevent multiple entries or a mess. You cannot assume that everyone understands this to the same degree as you.

TS

 

[ScottGem]

Ok ok it's a not perfect example but that's not the point. Where a natural PK exists it should be used. If you always use an Autonumber then there is nothing to prevent duplicate data being entered. If Cereldine sets an Autonumber to PK then she needs to restrict the sickness codes in some other way else you could have someone enter duplicates.

Again I disagree and suggest you do some research on this issue. I'm afraid you do not fully understand the purpose of a PK. As I explained its not the purpose of the autonumber (or a PK) to prevent duplication of data. Its up to the developer to build in verification to prevent that happening. It may take several fields to actually define a unique record. The purpose of the PK is to identify the record LATER for joins to other tables.

Also I think you misunderstood Cereldine's question. She is assigning a sickness code to a sickness event from an existing table of codes. There is no issue of duplication there, since different events can have the same sickness code. In the sickness code table she needs a unique index on the code to prevent duplicates. But because the field should be text, its more efficient to use an autonumber PK for linking.

 

[ScottGem]

You made no mention of indexes to Cereldine. I've seen numerous db's with autonumbers set as a PK and no indicies and consequently either a load of code to try and prevent multiple entries or a mess. You cannot assume that everyone understands this to the same degree as you.

TS

As I said in my last response, you misunderstood where the codes were being entered. I didn't mention indices in my initial response because it didn't apply. But indices are only one of the ways to prevent duplications. Since that wasn't the question, I didn't deal with that issue.

 

[The Stoat]

Again I disagree and suggest you do some research on this issue. I'm afraid you do not fully understand the purpose of a PK. As I explained its not the purpose of the autonumber (or a PK) to prevent duplication of data. Its up to the developer to build in verification to prevent that happening. It may take several fields to actually define a unique record. The purpose of the PK is to identify the record LATER for joins to other tables.

Also I think you misunderstood Cereldine's question. She is assigning a sickness code to a sickness event from an existing table of codes. There is no issue of duplication there, since different events can have the same sickness code. In the sickness code table she needs a unique index on the code to prevent duplicates. But because the field should be text, its more efficient to use an autonumber PK for linking.

Sorry could you be a bit more patronizing please


I often use autonumbers as unique identifiers i was trying to convey the fact that natural keys should not be dismissed and that there is a techincal issue with regards to how access implements keys. It is simply not enough to assign the PK in the table design view to an autonumber. You did not discuss indexes and people will take away a message and use it in other circumstances.

You have no way of knowing how Cereldine is implementing the system and who may or may not be required either now or in the future to update the lookup tables or how nor anyone else who reads your advice.

I think this is a good discourse on the subject - i'm sure you'll disagree.

http://www.sqlteam.com/item.asp?ItemID=2599

 

[The Stoat]

As I said in my last response, you misunderstood where the codes were being entered. I didn't mention indices in my initial response because it didn't apply. But indices are only one of the ways to prevent duplications. Since that wasn't the question, I didn't deal with that issue.

No i didn't. I made no assumptions about Cereldine's work other than that she had to enter data into a table. How that was to happen now or later was bye-the-bye. I also took the view that table level control of duplication is the most efficient, transferable, simplest and robust method to prevent duplication.

 

[ScottGem]

I'm not trying to be patronizing, but you are mixing apples and oranges. You equate the assignment of a primary key to the prevention of duplicate entries. The two have little to do with each other. I've been helping out on boards likes these for a long time, I've also been designing databases on various platforms for a long time. I've never run into a problem with mixing the issue of PKs and data duplication. I agree, assigning a PK is not enough to insure non duplication, but then that was never an issue that was part of my answer or this thread until you bought it up.

You have been making several assumptions about the original question. The whole point of your arguments here were based on the assumption that the code data was being entered in a lookup table. I maintain that wasn't the case.

I very strongly disagree with your view that table level control of duplication is the most efficient. In fact, its just the opposite. With table level control, the duplication isn't discovered until the records is entered into the table. Therefore its possible for the user to type in the whole record only to find its a duplicatation after they submit it. If duplication is built into the forms, you can eliminate duplicates before the user wastes time entering additional data. The best that can be said about it, its that its transferable.

As for the article you linked to, it is a good statement of the issues, but I do disagree with some of the points made. He gives 4 reasons they are bad, I agree with the first one. But the the second is dead wrong. One of the requirements for a PK is that it won't change. Therefore, the fact that it can't be updated is a positive not a negative. Again, the purpose of a PK is not to prevent duplicate data. As for his last 2 points, PKs should be used internally, not externally. The user never has to see what the PK is, so ponts 3 and 4 are not valid. Finally, I think the author is geared more towards the Identity column in SQL Server, not as much towards the Autonumber datatype in Access (though he does refer to it).

A more complete and compelling argument can be found here:
http://www.utteraccess.com/forums/showflat.php?&Number=637301

Going back to that article, he suggest that its impossible to identify each person's boss. Assuming that there is more data then he shows it should be very easy to do so. Simply create a query linking the table to an alias of itself joining BOSSID to ID. Nothing to it.

I did mention, in my initial answer, that there was some debate over the issue of natural vs surrogate keys and suggested more research about it.

Had you just noted that assigning a PK doesn't prevent duplication of data and additional steps need to be taken for that I wouldn't have objected. But your arguments have been based on the use of a PK to prevent duplications. That is not its purpose and never has been. if you are using PKs that way then you are using them incorrectly.

 

[neileg]

Ok ok it's a not perfect example but that's not the point. Where a natural PK exists it should be used. If you always use an Autonumber then there is nothing to prevent duplicate data being entered. If Cereldine sets an Autonumber to PK then she needs to restrict the sickness codes in some other way else you could have someone enter duplicates.

Sorry, Stoat, I'm not trying to do this do death, but the fact is that there are rarely any natural keys where you can be sure that there will never be a duplicate. Many ID references that people assume are unique are not, in fact. Things like post codes and street numbers, NI numbers, car registration numbers appear to be appropriate, but they're not. That's why I would always prefer a synthetic key over a natural key unless there was an overwhelming case for the natural key.