6

We have a public API. Users will call this API by passing in different Guids as the IDs for entities. In the API, the Guid will be translated to the internal ID (Int32) for that particular record in our DB.

The public API calls our internal API and the internal API receives this internal ID to fetch the record from the DB.

I understand why Guids are used, because malicious users can't guess a valid ID for some random record.

But why do we need to translate them to internal IDs? If our DB tables have ExternalId and InternalId, couldn't either one be used internally to fetch a record?

David Klempfner
  • 309

1 Answers1

14

In general, it seems not advised to inflate objects unnecessarily by giving them multiple equivalent unique identifiers, when one is enough. It would be a redundancy.

However, there are a number of reason that could justify the situation that you describe (I’ll illustrate the ideas with some general examples without guid but with a similar ratinale):

  • Legacy: the system was build with one id (e.g. an internal one); there are hundreds of systems that use it that can’t be updated at once (i.e. backward compatibility); there is the intent to use a better id; so for a given time you need to map both.
  • Ownership: the system does not own data it manages (or its external identity) and needs to keep track of the identities known elsewhere. A typical example is a personal id in a payroll system, that uses a unique social security number that is managed by a state.
  • Semantics and/or bounded contexts: although the identities look redundant, in reality they aren’t. One example are an internal vehicle id in a fleet management system that is mapped to a registration plate. Both uniquely identify the same car, but for different purpose (and one can change, for example when a vehicle is sold/transferred to another subsidiary). Another example is when multiple external identities may refer to the same internal identity or vice versa, because each identity is managed by a different system and it is preferred to keep the model of each system independent.
  • Distributed computing: where several subsystems may create entities independently, and at a later stage, some other subsystems merges/aggregates the data.
  • Performance?: GUID produce longer strings (in a web api), and use 2 to 4 times more space in binary. I personally don’t think that it would be a dramatic difference, but maybe some other people think it will (or have evidence it will?). This is why I mention it with a question mark.
    Edit: As Allon Guralnek pointed out in the comments, if the database uses clustered indexes or index organised tables that order rows based on the primary key, the use of a GUID could create some extra sorting overhead at every insertion due to the randomness of the GUID. But in this case, there must be another reason to justify keeping the GUID.
  • A combination of several factors.
Christophe
  • 77,139
  • If your database uses a clustering index (aka index-organized tables), then using a GUID as its key would be detrimental to performance. Sequential GUID allocation can alleviate this, but not completely, and it requires your database (or some other central authority) to generate all GUIDs, which removes one advantage of them. Therefore, the most common practice is to use an internal sequential ID as the clustering index and the GUID as the primary key. That ensures most database insertions happen at the end of the index rather than in the middle where it would cause page splits. – Allon Guralnek Aug 26 '22 at 11:08
  • @AllonGuralnek Thank you for this very relevant contribution! Indeed, many vendors provide clustered indexes that would create more overhead with GUID which can be in random order and require some reorganisation at each insert. So we'd definitively have a performance effect. The question remains nevertheless why an external key with the GUID would still be needed: the primary integer keyword be sufficient for the identity. So there's probably as well one of the other elements at play. – Christophe Aug 26 '22 at 12:21
  • @AllonGuralnek I updated the paragraph on performance to mention your argument (with credit ;-) ) , because not everybody goes through all the comments. – Christophe Aug 26 '22 at 12:33
  • @AllonGuralnek Surely if you have GUID as part of they key you do not design the db to have cluster indices beginning with the GUID, juts make it a plain index. Clustering is only useful if there is an order you want to query or sort by e.g. date of entry. – mmmmmm Aug 26 '22 at 21:37
  • @mmmmmm: If the GUID is the entirety of the primary key, then that's most database's default. And without know the performance implication, it seems like the logical thing to do. – Allon Guralnek Aug 27 '22 at 13:55
  • 1
    Not to me I only use a clustered index if there was a reason - so in general I would not set one - integer ids and GUIDS would never be on one – mmmmmm Aug 27 '22 at 14:36