alpha-prefixed serial numbers; leading zeroes

[Bob7]

For primary key values, using auto-entered (and incremented) serial numbers, where the numbers utilize an alpha prefix, I'm wondering what the pros and cons are of using the form of: "abc_1" vs. "abc_000001" are. One thing I like about the latter is that the numbers are of fixed length. Just a visual preference really on my part (while scanning through multiple records, comparing them, etc.). The disadvantage seems to be that you need to make an arbitrary decision as to the most numbers the system would ever need, both for actual records, and also accounting for record deletions. I believe that the latter number example, even if exceeded in terms of leading zeros, would still increment; it would just make the length longer (and therefore no longer a fixed length across all record). Correct me if I'm wrong on that. Anyway, any feedback on a preferred method is appreciated.

BTW, I'm aware of more advanced methods of creating primary keys (using time and date stamps in combination with serial numbers, etc). Thanks.

Bob

[comment]

I think the "abc_000001" type will sort correctly, while "abc_1" will not.

However, you should ask what is the advantage of an alpha prefix. It is very rarely required, and IMHO it is best (and fastest) to make the primary key a simple auto-entered serial number. To satisfy your visual preferences, you can keep it hidden from users' view.

BTW, I'm aware of more advanced methods of creating primary keys (using time and date stamps in combination with serial numbers, etc).

I am not.

Edited April 4, 2006 by Guest

[Vaughan]

Things can always be made more complicated, or more expensive.

[Genx]

Sure they'd be "more advanced" but they'd be a pain to search, and likely be a waste of storage space.

~Genx

[xochi]

Here's my take:

Plain numeric serial numbers seem more simple, probably take a few bytes each less to store, should (in theory) be easier for FM to index (perhaps increasing performance in large tables), and are easy to do certain calculations (e.g. subtract the last serial from the first and add 1 to get the # of records). They always sort properly.

The major downside I see is when you are using them as foreign keys in related tables in a complicated solution, especially during the development & debugging stages. It's very hard to look at a number like "42517" and be absolutely sure that this is indeed the right foreign key from the right table. It's much easier to see a number like "RXT0042517" and be reminded that this is indeed the PK from the "RXT" table.

I've encountered at least one bug (due to my own sloppiness) where I mixed up two foreign keys. An additional advantage to using unique alpha prefixes is that when you do make this mistake, none of the keys will match. It's much easier to detect a bug when you have zero related records, instead of a number of (incorrectly) related records.