Best practice for exposing record IDs to API users

Question

I am extending an API to include the ability to retrieve and update a specific record after the record is created by the user.

Currently, these records are created with an auto generated integer key, for example 49300.

Users need a way to access a specific record but I don't like the idea of exposing this ID to end users, because it will reveal information about my schema, for example I can assume there is also a record of ID 493299.

I'm considering a GUID alongside the auto generated integer, but I have concerns about performance as the table size is ~5 million records, growing by 1 million/year.

Is there reason for concern adding a table this size, or the subsequent SELECT query on the GUID when retrieving/updating the record?

I'm using AWS RDS MySQL.

Thanks!

Answer 1

Rows are created with a Surrogate, numeric key that keeps the database nice and tidy but, for each record, is there a Natural key that would be meaningful to the Users?

For example, a User might log into your Application using an email address.

Each User record would have both a Surrogate, numeric, UserId that the User never sees plus their email address (and other stuff). When joining tables within the database, you use the internal, Surrogate key. For anything coming from the Outside World, you use the Natural keys that make sense to Them Out There:

create table Users 
( id            int            auto_increment not null 
, email_address varchar( 100 ) not null 
, primary key ( id ) 
, key ( email_address )
); 

select * 
from Users ; 
+----+----------------------------+ 
| id | email_address              | 
+----+----------------------------+ 
|  1 | [email protected] |
+----+----------------------------+ 

It might be that one field is not enough to do this. The Natural, Primary Key of a record might be a handful of fields - if that what it takes for Users to get to that record, then that's what they have to enter.

As you quite rightly suspect, you should never leak those internal identifiers outside of the database. Always take Real World value[s] and map them back into their internal equivalents. If nothing else, it gives you an degree of input data cleansing en route.