Using Autonumber as identifier (1 Viewer)

[tmyers]

I know that you should not attach meaning to an auto-number field as its only role is to be unique but would it go against common practices to use it as a sort of "job number"?

What I mean is when say a project in the database is created, you would use the auto-number to say that it is project # (insert auto-number here) just to identify it within the database. Is this bad practice and should I create my own system for something like this? Bear in mind, I am not using this like say a quote number, just an identifier within the database itself.

 

[mike60smart]

I know that you should not attach meaning to an auto-number field as its only role is to be unique but would it go against common practices to use it as a sort of "job number"?

What I mean is when say a project in the database is created, you would use the auto-number to say that it is project # (insert auto-number here) just to identify it within the database. Is this bad practice and should I create my own system for something like this? Bear in mind, I am not using this like say a quote number, just an identifier within the database itself.

Hi
The Primary Key would not be used for anything other than a Unique Identifier for each Record in the Table.
It would be hidden from the user.
If you want to create a Unique Project Number then use VBA to generate automatically

 

[sonic8]

What I mean is when say a project in the database is created, you would use the auto-number to say that it is project # (insert auto-number here) just to identify it within the database.

This is perfectly fine!
You can also use this number to identify entities in a context outside of the database; e.g., print the project# on a letter to a client.

The only caveat is: The database generates this number. Period. There is no sugar-coating, tweaking, or meddling of any kind with these numbers.

 

[tmyers]

This is perfectly fine!
You can also use this number to identify entities in a context outside of the database; e.g., print the project# on a letter to a client.

The only caveat is: The database generates this number. Period. There is no sugar-coating, tweaking, or meddling of any kind with these numbers.

Which is fine as far as the theory for what I want to use it for goes. Projects wouldn't have to be sequential so if a number was skipped it wouldn't matter. The only real requirement would be that it is unchanging so once a project would be assigned a number, that would be its number always and could never repeat.

Hi
The Primary Key would not be used for anything other than a Unique Identifier for each Record in the Table.
It would be hidden from the user.
If you want to create a Unique Project Number then use VBA to generate automatically

I could come up with a system to do such a thing if using the auto-number is frowned upon and shouldn't be done.

 

[cheekybuddha]

You will only have a problem if you require contiguous identifiers (ie numbers with no gaps).

If you start creating a new record and cancel out of it, the autonumber will have been assigned and will be lost. The next created record will use the next autonumber and not the one that was discards.

If this is not an issue for you, then an autonumber is fine, otherwise you will need to use a different strategy

 

[tmyers]

You will only have a problem if you require contiguous identifiers (ie numbers with no gaps).

If you start creating a new record and cancel out of it, the autonumber will have been assigned and will be lost. The next created record will use the next autonumber and not the one that was discards.

If this is not an issue for you, then an autonumber is fine, otherwise you will need to use a different strategy

If a number were skipped it wouldn't matter as all I am after is a unique identifier. I just want to follow best practice while also not creating extra work for myself .

Also, that is a wonderful looking dog in your profile picture. My wife absolutely adores Pits.

 

[GPGeorge]

One application I supported used the AutoNumber CustomerID for mass mailings. It was printed on the return card in the mailer. It meant nothing to them since so many such mailers carry similar numbers. It was irrelevant whether they were sequential in that situation. All we really needed was a reliable way for the person handling returned cards to lookup the correct customer among several hundred thousand customer records.

Today, I imagine, we would go on to use a bar code with the Customer ID encoded, along with other pertinent information.

 

[plog]

Another vote for its fine to use them as identifiers. However...

People can't but help to attach meaning to them. They will see they are sequential and for the most part contigous which means they will think you can use them as a proxy for ordering instead of using whatever date you actually use for that. Worse, they will ask you to enforce a meaning to them--make them reset after each new period, have you assign a specific number to one that needs to be "inserted" elsewhere in the order, have each one in a specific group start/end with a specific number, etc.

If you are to do this make sure the users know this is solely for indentification and no other information can be assumed from them.

 

[Pat Hartman]

I always use autonumbers as things like invoiceID or jobID provided the users do not mind that they have no meaning other than that they are unique. Sometimes users don't like to give up their old naming schemes and they want to include year and maybe customer as part of the uniqueID or a type code. Invoices always start with I and jobs always start with J, etc. Once there is any special formatting or sequencing, then the autonumber becomes simply the hidden primary key which is used in ALL relationships. The only things the custom sequence number are used for is printing on documents and searching so the generated ID must be defined as unique and be appropriately indexed. If the users don't want the invoice numbers that the clients see to start from 1, then I might reset the autonumber seed to start at a larger number, usually something higher than any ID generated by their old application.

 

[The_Doc_Man]

I have advocated use of the autonumber record ID as a "transaction ID" which might as well be sanskrit to most users, but it is merely a searchable reference number that shows a particular transaction occurred. As long as you honor the fact that it cannot be changed and cannot be directly assigned a value, it is fair game to use it after-the-fact. Although we often say that an autonumber has no meaning, that is not true. It is just that its meaning is a bit obscure and cannot have a user-assigned meaning. Accountants who go berserk over contiguous numbering don't like them, but since most people don't see the OTHER records to compare their numbers, it is not harmful to expose that number to users in READ-ONLY formats.

 

[Cotswold]

One every table I will obviously have an autonumber key but I never use it for use as a system number, code or reference. For that I will have a unique number in a system table that is advanced when a new record is added. Then if the database suffers corruption and parent records end up being deleted, then all related child data in tables cannot be accessed. In that scenario I can easily recreate the missing numbers on new parent records which will recover/restore the isolated child data to the user. All I need to do is locate any orphaned records and re-create their missing parent record. That I find to be far easier than attempting to restore unique autonumber fields, which can be a pain. Particularly if there are several scattered up and down the parent table.

 

[Minty]

I also have no issue using an Identity field as a unique user-facing reference.
It saves a lot of messing about with rolling your own, concurrency issues, etc.

As long as the caveats mentioned above by almost everybody are allowed for. (Sequences etc.)

 

[Pat Hartman]

Unless a user is looking at something with a "number" on it, he is not likely to be looking up ANYTHING by using a custom ID or autonumber. More likely, he will be looking for "new" activity or "late" activity or "missing" payments or some other actionable value.