[jetpackjoe]

Rather than use an extra column, I’ve taken to hashing the internal key (with a salt based on the entity type and some secret) to create the external facing ID.

[aeyes]

If it's hashed, how do you get back to the internal ID if you only have the external ID?

I used encryption instead because I can reverse it.

[arp242]

One thing I've done is using the customer name codepoints of every character + object ID formatted as base-36.

So with a customer email of '[e-mail]' and an object ID of 52 you end up with 1563 + 52 = 1615, or 18v in base-36. You can add a "base number" to make it a but larger, e.g. 50,000 so it becomes "13tr".

I'm sure people can figure this scheme out with enough effort; it's certainly not cryptographically secure, but it's "hidden enough" for many purposes, not much longer than numeric IDs (shorter in many cases), doesn't require any special DB-fu, and is reversible if you know the customer (which you usually do).

[xaferel]

That's a good idea but doesn't it need 2 round trips if you use an auto-increment primary key? First insert and then update by hashing the new id.

[grncdr]

Not the poster you’re replying to, but with this approach you generally don’t store the hashed identifier. Just encode/decode at the application boundaries.

[ThePhysicist]

Interesting approach, will consider that in the future! Though not sure if it's a good idea as that couples internal & external IDs, i.e. it's not possible to change one without also changing the other (but also not sure if that's really an issue).

[hot_gril]

This seems expensive and requires you to really know what you're doing with the cryptography. Why not just use a random external facing ID?

[filleokus]

That's really clever. Have you encountered any problems with it in practice?

[Ankhers]

Not the person you replied to, but I have had some issues with this in the past. Though it is more with how it was done than the approach itself.

In this project I do not believe the IDs were always encrypted when being sent to the user. So we sometimes had to guess whether we received an encrypted ID vs a regular integer ID because it is possible for the encryption algo that was used to return a sequence of numbers.