3 ms·

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.

xaferel 4y ago
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 4y ago
  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.
  - Too 4y ago
    How do you decode a hash?
    - grncdr 4y ago
      ah sorry, I was not very precise about "hashed". What I meant (and have done in the past) is to encrypt/decrypt the auto-incrementing ID at the application boundaries. If the OP is really using a one-way hash then yes, you would have to store both IDs.
filleokus 4y ago
That's really clever. Have you encountered any problems with it in practice?
- Ankhers 4y ago
  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.
ThePhysicist 4y ago
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).
aeyes 4y ago
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 4y ago
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 'martin@arp242.net' 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).
hot_gril 4y ago
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?

xaferel 4y ago

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 4y ago
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.
- Too 4y ago
  How do you decode a hash?
  - grncdr 4y ago
    ah sorry, I was not very precise about "hashed". What I meant (and have done in the past) is to encrypt/decrypt the auto-incrementing ID at the application boundaries. If the OP is really using a one-way hash then yes, you would have to store both IDs.

filleokus 4y ago

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

Ankhers 4y ago
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.

ThePhysicist 4y ago

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).

aeyes 4y ago

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 4y ago

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 'martin@arp242.net' 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).

hot_gril 4y ago

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?