0

I'm building a REST API, backed by MySQL. Normally for every table I will have at least one: id UNSIGNED INT NOT NULL PRIMARY KEY AUTO_INCREMENT field as a default, but the issue with this is that it makes identifiers predictable, and tells end-users how many entries of a given resource we have, and potentially the rate of change.

I'd like my ID's to not have that issue. Instead, I would rather use something random. I've decided on using a 64bit random number, represented as a base64url string.

The way I've implemented this is adding a external_id VARCHAR(11) NOT NULL UNIQUE to every table, keeping the id and using the id for all foreign keys.

As I'm doing this, I'm starting to wonder if it isn't better to just fully ditch the AUTO_INCREMENT field and just convert my external_id to a 64 bit INT and make this the main key used throughout my data model.

One disadvantage is that it's an extra step to convert this number to the string variant.

I'm not interested in academic purity of my data model, but I'm curious how people generally solve this issue. Do you use 2 ids?

Improve this question
7
  • Use a SHA-1 hashing function on your autoincrement! Or, better, use a SHA-1 of some natural key?
    – Vérace
    Commented Jul 15 at 9:58
  • I don't consider it an issue really. You should be storing IDs like that in session variables anyway so they aren't visible.
    – Dave
    Commented Jul 15 at 13:35
  • @Vérace if I'd use a sha, I would either not be able to use indexes, or I need to store the sha separately which makes this no better than a random secondary key.
    – Evert
    Commented Jul 15 at 19:42
  • @Dave these id's are used in URLs. Session variables make no sense in this context.
    – Evert
    Commented Jul 15 at 19:42
  • And why can't you index a hash? Or this - nice monotonic key from which you can extract timestamps?
    – Vérace
    Commented Jul 15 at 20:58

1 Answer 1

Reset to default
0

The closest thing to a common solution you're looking for is to use a UUID for the primary key.

This isn't a universal solution, because UUID's have pros and cons. But it's a pretty common solution.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.