1

Why do many applications replace the primary key of a database with a seemingly random alternative id when revealing the record to the user?

My guess is that it prevents users from guessing other rows in the table. If so, isn't that just false sense of security?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
getWeberForStackExchange
  • 758
  • 1
  • 13
  • 25

2 Answers2

4

I guess you are talking about surrogate keys here. One of the desired or supposed advantages of surrogate keys is that they aren't burdened by any external meaning or dependency on anything outside the database. So for example the surrogate key values can safely be reassigned or the key can be refactored or discarded without any consequences for users of the system.

Generally surrogate keys are kept hidden from users so that they don't acquire any such external dependencies. Being hidden from users was in fact part of the original definition of a surrogate key as proposed by E.F.Codd. If key values reside in the user's browser cache or favourites list then they aren't much use as "surrogates" any more. So that's one common reason why you will see one key used only inside the database and a different key for the same table made visible in the application.

nvogel
  • 24,981
  • 1
  • 44
  • 82
1

I think it may depend on the type of application you are working with. I work with Enterprise software that is only used by the company I work for and is not generally available to the outside world. In this case, it is often critical to let the user see the surrogate key for people-related records because the information in the person table has no uniqueness. There can be two John Smiths (we actually have over 1000 of them) who are genuinely different people. They may even have the same business address and be different people (Sons are often named for fathers and work in the same medical practice for instance). So they need to refer to the surrogate key on forms and in reporting to ensure they are using the record they thought they wanted. OItherwise if they wanted to research further details about the John Smith that they saw in a report, how would they look it up in the aaplication without having to go through all 1000 to find the right one? Creating a fake id as well as the real one would be time consuming (we import millions of records at a time) and for no real gain since the data would not be visible outside our comapny application.

For a web app that is open to the general public, I can see where you might not want to show this information.

HLGEM
  • 94,695
  • 15
  • 113
  • 186
  • 1
    An employee number used in forms and reports to identify people and looked-up by HR is certainly not a surrogate key. It's a business key (AKA "natural key" - although I much prefer "business key" because it says what it does: it identifies something within the business process). – nvogel Aug 05 '11 at 05:01
  • @dportas, yes it's a business key but it is also a surrogate key generated by the database itself with no inherent meaning except to identify the record. – HLGEM Aug 05 '11 at 13:55
  • 3
    that's an interesting take on what a surrogate key is. If a key is being written on forms and used in the business process then surely it *is* being used to identify something other than a record in the database - it's being used to identify an employee. The employee number therefore has just as much "meaning" as any other identifier in the business process, e.g.: invoice numbers, order numbers, login names, SKUs, etc. *Surrogate* key means an attribute which is not part of the Universe of Discourse at all and is therefore mutually exclusive with the term "business/natural key". – nvogel Aug 05 '11 at 14:27