3

Example:

in the database I have a record such as this:

{
    id: 53,  
    part_number: 'MOTOR-OIL',
    description: 'Motor Oil'
}

I wish to use a link that displays this record. I could refer to the record like so, using the surrogate key:

http://localhost/show_record/53

or I could use an domain-specific identifier like so, provided it's unique:

http://localhost/show_record/MOTOR-OIL

Is there a best practice of a framework relating to deciding which one to use? Is it acceptable to use the surrogate key?

Notes:

In my case, the surrogate/artificial key is a PRIMARY KEY and quite unlikely to change. The part number however might change, while retaining the same surrogate key.

In my case the domain entity's part_number is unique, but in the general case, it may not be the case, and more parameters may be required to uniquely identify a record, while id being a surrogate key will typically remain unique per various database conventions.

Dennis
  • 8,157
  • 5
  • 36
  • 68
  • [Should a surrogate key ever be exposed to a user?](https://softwareengineering.stackexchange.com/q/204394/31260) – gnat Dec 14 '17 at 20:04
  • Why `too broad` flag? – Dennis Dec 14 '17 at 20:17
  • 1
    Did you read the article @gnat linked? – Robert Harvey Dec 14 '17 at 20:44
  • 1
    See also [Exposing database IDs - security risk?](https://stackoverflow.com/q/396164) – Robert Harvey Dec 14 '17 at 20:49
  • the general consensus of answers in gnat's link seems to be "do not expose surrogate key to the user as it is likely to be requested to change per business needs". I am not sure that "ability to change" is relevant, because if I use domain-specific key it is surely more likely to change compared to the surrogate key, but I can see that too broad flag could be due to the possibility that the actual answer is specific-situation-dependent. – Dennis Dec 14 '17 at 20:49
  • 1
    Yep, if you read several of the comments I posted below his answer, you noticed that I agree with you. Surrogate keys will never change. Ever. – Robert Harvey Dec 14 '17 at 20:50
  • See also [Should I obscure database primary keys (IDs) in application front end?](https://security.stackexchange.com/questions/56357) – Robert Harvey Dec 14 '17 at 20:50
  • and [Stop putting auto-increment IDs in URLs](http://blogs.perl.org/users/ovid/2014/11/stop-putting-auto-increment-ids-in-urls.html) – Robert Harvey Dec 14 '17 at 20:51
  • Interesting.. my own reason to not expose surrogate key would be more SRP-dependent. That is, do not show information in the view when that information lives and belongs specifically in the database layer. Showing it is having a database leak into your view. Security-related reasons to not show it are mentioned in the articles. I suppose if domain-specific key is not unique, it may be good to create a domain-specific unique identifier and not use the sequential primary key where it is accessible to the user..... – Dennis Dec 14 '17 at 21:03
  • I am still not 100% on relevance of "this or that identifier can change". I suppose that if it never changes like the surrogate key, you could hard-link to it and it may stay the same for years, or forever. While domain-specific identifier may change at some point, breaking existing links.. But how or where it matters I am still not entirely sure. It looks like using domain-specific key is *more flexible* (as I gathered from reading comments on the linked answer) – Dennis Dec 14 '17 at 21:04
  • 2
    You're misusing the term "SRP." SRP doesn't mean "information should only reside in one place," it means "a class should have only one reason for modification." It is a guideline for making classes easier to maintain, not a method of data decoupling. – Robert Harvey Dec 14 '17 at 22:17

2 Answers2

5

It is ok, when

  • you do not bother that someone might use that surrogate value to deduce the number of records in the related table

  • you do not bother that someone might guess other valid URLs from that number

These are minor issues, and if required, one can easily overcome them by using something like a GUID or some salted hashed value as surrogate key.

Doc Brown
  • 199,015
  • 33
  • 367
  • 565
5

Doc Brown's answer covers security issues. Another angle is if this is facing the public.

http://example.com/show_record/motor-oil

is more likely to fare better in SEO than

http://example.com/show_record/53

If the world isn't seeing it, it doesn't matter (if they get into your network this is not the biggest attack vector), if you want search engines to find it then ids (guid or integer) are not that good.

Bent
  • 2,566
  • 1
  • 14
  • 18