Replacing sequence with random number

Question

I would like to replace some of the sequences I use for id's in my postgresql db with my own custom made id generator. The generator would produce a random number with a checkdigit at the end. So this:

SELECT nextval('customers')

would be replaced by something like this:

SELECT get_new_rand_id('customer')

The function would then return a numerical value such as: [1-9][0-9]{9} where the last digit is a checksum.

The concerns I have is:

1. How do I make the thing atomic
2. How do I avoid returning the same id twice (this would be caught by trying to insert it into a column with unique constraint but then its to late to I think)
3. Is this a good idea at all?

Note1: I do not want to use uuid since it is to be communicated with customers and 10 digits is far simpler to communicate than the 36 character uuid.

Note2: The function would rarely be called with SELECT get_new_rand_id() but would be assigned as default value on the id-column instead of nextval().

EDIT: Ok, good discussusion below! Here are some explanation for why:

1. So why would I over-comlicate things this way? The purpouse is to hide the primary key from the customers.

   I give each new customer a unique customerId (generated serial number in the db). Since I communicate that number with the customer it is a fairly simple task for my competitors to monitor my business (there are other numbers such as invoice nr and order nr that have the same properties). It is this monitoring I would like to make a little bit harder (note: not impossible but harder).

2. Why the check digit?

   Before there was any talk of hiding the serial nr I added a checkdigit to ordernr since there were klumbsy fingers at some points in the production, and my thought was that this would be a good practice to keep in the future.

After reading the discussion I can certainly see that my approach is not the best way to solve my problem, but I have no other good idea of how to solve it, so please help me out here.

1. Should I add an extra column where I put the id I expose to the customer and keep the serial as primary key?
2. How can I generate the id to expose in a sane and efficient way?
3. Is the checkdigit necessary?

Answer 1

Your best bet would probably be some form of hash function, and then a checksum added to the end.

Answer 2

I added my comment to your question and then realized that I should have explained myself better... My apologies.

You could have a second key - not the primary key - that is visible to the user. That key could use the primary as the seed for the hash function you describe and be the one that you use to do lookups. That key would be generated by a trigger after insert (which is much simpler than trying to ensure atomicity of the operation) and

That is the key that you share with your clients, never the PK. I know there is debate (albeit, I can't understand why) if PKs are to be invisible to the user applications or not. The modern database design practices, and my personal experience, all seem to suggest that PKs should NOT be visible to users. They tend to attach meaning to them and, over time, that is a very bad thing - regardless if they have a check digit in the key or not.

Your joins will still be done using the PK. This other generated key is just supposed to be used for client lookups. They are the face, the PK is the guts.

Hope that helps.

Edit: FWIW, there is little to be said about "right" or "wrong" in database design. Sometimes it boils down to a choice. I think the choice you face will be better served by leaving the PK alone and creating a secondary key - just that.

Answer 3

I think you are way over-complicating this. Why not let the database do what it does best and let it take care of atomicity and ensuring that the same id is not used twice? Why not use a postgresql SERIAL type and get an autogenerated surrogate primary key, just like an integer IDENTITY column in SQL Server or DB2? Use that on the column instead. Plus it will be faster than your user-defined function.

I concur regarding hiding this surrogate primary key and using an exposed secondary key (with a unique constraint on it) to lookup clients in your interface.

Are you using a sequence because you need a unique identifier across several tables? This is usually an indication that you need to rethink your table design, and those several tables should perhaps be combined into one, with an autogenerated surrogate primary key.

Also see here

Answer 4

If you're not using this too often (you do not have a new customer every second, do you?) then it is feasible to just get a random number and then try to insert the record. Just be prepared to retry inserting with another number when it fails with unique constraint violation.

I'd use numbers 1000000 to 999999 (900000 possible numbers of the same length) and check digit using UPC or ISBN 10 algorithm. 2 check digits would be better though as they'll eliminate 99% of human errors instead of 9%.

Answer 5

If you don't want a "truly random" number, but only need to derive a random-looking identifier from a serial, using ciphers might be a good idea. Since their output is bijective (there is a one-to-one mapping between input and output values) -- you will not have any collisions.

One problem though, is that most cryptographic block ciphers work on 64-bit or larger blocks.

However, the PostgreSQL wiki has an example PL/pgSQL procedure for a "non-cryptographic" cipher function that does exactly this. Disclaimer: I have not tried using this function myself.