Jump to content

alpha-prefixed serial numbers; leading zeroes


This topic is 5647 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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 by Guest
Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5647 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.