0

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!

  • Indexes on Integer are faster than char, so the querys aren't usually that fast as before. – nbk Mar 30 at 10:20
  • 1
    INT vs CHAR -- not that much difference. Huge tables with a unique on UUID has serious performance problems. More discussion: mysql.rjweb.org/doc.php/uuid – Rick James Apr 7 at 0:42
2

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 | fred.flintstone@rubble.com |
+----+----------------------------+ 

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.

| improve this answer | |
  • I don't think I have an option for a good natural key. This table is a record of API usage, containing the user ID and the ID of the record. The remaining columns are details for a given API call. – Half_Duplex Mar 31 at 13:47
  • 1
    Sounds like this would be used for diagnostic purposes? Would it, perhaps, be queried by date/timestamp? OK, that might return multiple rows but the User would not need to know what the ID's of the individual rows were (although your application might do). – Phill W. Apr 1 at 10:53
  • The real life scenario is that each event represents an action within a compliance plan that at some point in the future may require an update, to close any compliance gaps. Hence the need to ID the record. – Half_Duplex Apr 15 at 19:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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