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.

11
  • 1
    Did you read the article @gnat linked? Dec 14 '17 at 20:44
  • 1
    See also Exposing database IDs - security risk? 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. Dec 14 '17 at 20:50
  • 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. Dec 14 '17 at 22:17
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.

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.

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.