Surrogate-key in ViewModel instead of primary-key

Question

Is there a best practice how to prevent showing autoincrement primary-key's (ID's) from database in the ViewModel, to not make them visible for end-user?

I know there can be other unique fields on the table, which could be used. However, what if it's not sure if there is (or will stay) a unique one?

I was thinking to create a hash and save it in a column named like ViewKey in the Database. For example in the table address.

Answer 1

Best practice is that you need keys that are visible to users and that are meaningful and usable in the business domain (normally called business keys or natural keys). If you don't implement such keys and make them visible to users then how can you expect users to identify information in the database and use it correctly in the real world?

It doesn't make much difference whether keys are primary or not. What matters is that they are non-nullable and enforced with uniqueness constraints in your database.

If you haven't identified suitable keys already as part of your analysis and design then put in the time to do so. Business requirements must be the determining factor. There is obviously no single one-size-fits-all solution.

Answer 2

Maybe I'm misunderstanding the question. If you don't want to show a DB field to the end user, then ... don't. Are you using some kind of tool that lets end users access your DB tables directly, and you can't control what fields they see?

If you mean, "How can the user identify a record without using such an autonumber field?" If the table has some natural key, i.e. an identifier that exists in the world outside of the computer, like a social security number or a standard country code or whatever, than you can use that as the primary key and you don't need an auto-number. If there is no real-world value that is guaranteed to be unique, then you have to create an identifier, such as an autonumber field or a guid. If you create an identifier for internal use, then you might as well show it to the user and allow the user to use it to identify the record. Why create an "internal identifier" and an "external identifier" unless you need to? If there's some reason why an auto-number field isn't acceptable -- identifiers have to have check digits or some such -- then create an acceptable identifier and use it both internally and externally.

Sometimes the natural key is a poor choice for an internal key, the most common reason being because it is too long and it would make indexes and lookups big and slow. That's the only reason I see to have an "internal key" and an "external key".