Suppose you have a very simple CRUD where you are just storing a simple tuple-like type of data, but its natural primary key is cumbersome for the user to remember (very long, difficult to write, etc.). Further, suppose I want to let users refer to the records using a handle such as an autoincremented integer value (which will then act as the primary key, but is surrogate, rather than natural).

They create the records without specifying the handle, but, when they request a list of all records, I want to give them the records with the handle attached to them, so that they can later easily refer to them.

If, in my application, the record is represented as a simple value object, (which is its own DTO), which is created and passed to the database gateway, and returned from the database gateway... how could I properly do the returning of the handle as well?

I don't want to return a different object (something like a record decorated with the handle) when reading from the database, because I believe the handle is just a presentation detail demanded by the requirements of this specific user interface, and may be useless for different user interfaces. Sticking the handle to the existing record object makes no sense because it is not really part of the data type.

One thing I could do is have the presentation separately ask for the handle of each record. That would mean two trips to the database but at least it would be clear that the handle is not part of the record.

But I'm positive there must be a better way. Any ideas?

For example

A very simple book catalog. The data is Book, which is represented by tuples of the form (author, title, year). We suppose (author, title) is a natural primary key (in all rigor, this may not be the case but just take this as an assumption of the system). I only have an UI, which is a command-line interface to my system. Here, requiring the users to refer to books by providing the natural primary key (author, title) is cumbersome, hence I decide to link each record to a dumb autoincremented integer which would act as a surrogate primary key. That way, users of the command-line interface can refer to records by their associated integer handle. This link may be implemented as a sparate association table mapping natural PKs to surrogate PKs.

The whole implementation of the system consists of a data type, which may be a tuple/struct/hash/custom plain old object, and a single CRUD repository where I can persist these objects, and read them by their natural primary key. I don't want to return an object which also has a 'handle' member containing its associated surrogate primary key because that data has nothing to do with the rest of the data and is there just as a requirement of the particular UI.

So I'd propose having a method on the repository (or a different repository altogether) which gives me handles of records, providing the natural primary key. With this, this specific UI would use the repository (or those two repositories) to first retrieve the record and then retrieve the handle, in two steps.

  • 2
    If it acts as a primary key, then it is part of the record and the DTO. I think the best way is to just include it in the DTO. That you don't know the value before the first time a record is written to the database can be handled by defining a value that means 'not written to database' eg. -1. – Bent Aug 25 '16 at 18:18
  • @Bent - I don't think being the primary key is always the case. "... which may well act as a primary key" – JeffO Aug 25 '16 at 18:31
  • @JeffO: sorry mate, I think I misled you with the wording of my question. If the autoincremented integer is there, it will always be the primary key. I've updated the question. – Randy Eels Sep 3 '16 at 13:28
  • Once you implement a table to relate table's A entities with its handle (user-friendly id), such relation become part of your data model. So you can not decouple it in two differenciated concerns. Its part of the concern. I think that what is stoping to you to let system bypass the handle is that you see it as a UI thing and looks like other ppl is explaining how util can be in another contexts beside UI – Laiv Sep 3 '16 at 14:34
  • How many rows of data are you dealing with? Maybe you could create a 32-bit hash value? – Prasanna K Rao Sep 5 '16 at 7:24

I don't see why you feel this way.

"Sticking the handle to the existing record object makes no sense because it is not really part of the data type."

If the user needs to identify a record by an arbitrary or incremental number, then it is data. You can even give it some generic name like "ID Number." Many purchase order (PO Number) and billing systems (Invoice ID) have these numbers. In some places, keeping track of these sequential numbers is very important in an audit. You may not rely on this for an update or delete (It's not the Primary Key), that's fine. Don't send it back to the db if you don't want to.

  • Agreed, with the caveat that PO Number and Invoice ID are real data and not surrogates (though it is possible for a surrogate to stand in for a real number if you can accept the default qualities of surrogates). – Robert Harvey Aug 25 '16 at 18:40
  • @RobertHarvey - I think once the user sees the number, it becomes real. I have one system where we display ID numbers all the time to help with support since many records are difficult to identify without factoring in many fields. – JeffO Sep 2 '16 at 15:15
  • @JeffO: The fact that the user needs to identify a record by this number is just a requirement of the chosen UI. Such number is completely meaningless in the domain model. And it may well disappear if the UI changes. That's why I don't want to couple it to the record, because the domain and the UI change at different speeds. And that's why I was thinking about making two trips to the database, in order to explicitly reflect this decoupling in the code. But I still feel there must be a better way. – Randy Eels Sep 3 '16 at 13:28
  • @RandyEels: I don't get it. I have designed some systems with surrogates, and never stumbled into that problem. The way your DB client queries the database is one thing (often the PK included), what the UI actually does with it a different one (UIs seldom need to show the surrogate to the user, but keep that key invisibly in the background, associated with the related UI elements, to allow later identification). Why should your program query the same information it already has got before for a second time? I fail to see any "decoupling" in this. – Doc Brown Sep 3 '16 at 13:37
  • @DocBrown: It should query that information because it didn't get it in the first query. The first query retrieves the record, without the surrogate key. The second query retrieves the surrogate key. Those two pieces of data are unrelated, and only used by that specific UI. Imagine there was an additional UI which didn't require that surrogate key (e.g. because it could do with the long, cumbersome natural primary key). That UI should be completely oblivious to the fact that there is a surrogate primary key in place for the first UI to work. – Randy Eels Sep 3 '16 at 13:49

Been there, done that.

I've programmed CLI apps that interact with database items where the natural key is cumbersome to type.

What I did is that I designed my app so that the use case is always as follows:

  • User types the command followed by a string.
  • That string is something the user remembers from the description of the item represented in the database row ( examples: a movie, a person, a book, etc )
  • The app filters the database with LIKE
  • Other parameters and switches can make the query narrow down the number of rows the LIKE command has to act on.
  • If a manageable (for a CLI) number of rows, say 15 at most, is retrieved, a menu is presented. The menu consists in printing each row (a selected group of columns) preceded by a number, but that number is not the surrogate, it's the line number of a temp file created after the search with the surrogates of the rows. The user will always select from a menu and the numbers will always be 1~15 at most.
  • If the number of rows more than 15 I print a message stating so and that they should refine the search.
  • Notice that here, like in GUIs you are offering the user a way to chose a record without knowing the surrogate (in GUIs the user clicks on the item o selects it from a drop down).

In another CLI app, I had to show the surrogate so users could use it as a parameter for the program. In that case I didn't use the menu approach because the app was a maintenance CLI tools for the maintainers of the system, doing a set of very complex operations on the data, and they could and should get the surrogate to pass it as as parameter to the program.


I don't want to return an object which also has a 'handle' member containing its associated surrogate primary key because that data has nothing to do with the rest of the data and is there just as a requirement of the particular UI.

Surrogate keys are not a requirements of UIs, they are used because the natural key is non-existing, bad (changes often) or cumbersome (too many columns)

Try the menu approach, if it doesn't suit you, (like in the second example I gave you) then show the surrogate. If the natural key is so cumbersome to type then the surrogate is part of the data.

  • Thanks for the menu approach, although I don't think I want that for my application. I feel you truly understood my problem (I know it was hard to get what I meant before I gave the example). What I want is to show the surrogate, but my view is that it is not part of the domain model, hence the Book object should not have an 'id' or 'handle' attribute. – Randy Eels Sep 27 '16 at 13:48

I don't want to return an object which also has a 'handle' member containing its associated surrogate primary key because that data has nothing to do with the rest of the data and is there just as a requirement of the particular UI.

The design of your system becomes much simpler if you just change your mind on this. Having data objects with a 1:1 correspondence between the object's attributes and the database columns is a straightforward approach which serves most systems I have seen in the past very well. It makes it very easy to generate the whole CRUD code and the code for the data objects themselves from a meta description or from the database, instead of writing it manually.

And yes, these objects might have a dozen attributes, of which a particular UI form, dialog or list will only show a six, seven or ten, and another form will show or use three different ones. The fact the one attribute you do not want to show in one form is a surrogate key, or some kind of business data, or another technical item which has nothing to do with the UI, is irrelevant.

Of course, I have also seen systems where one could omit some columns and allow the retrieval of "partial" objects, where some of the attributes are not initialized after the first object creation, and might be loaded later by a second query. AFAIK some ORMs provide some automatic "lazy loading" mechanisms which work exactly that way. But for most situations, this is an unnecessary optimization, because database query performance is mainly dominated by the number of roundtrips of a SELECT. It makes often no measurable difference if you select 3, 6 or 12 columns from a table, except one of the left-out columns is a BLOB field or huge text field.

So do yourself a favor and try not to overengineer your system by tailoring your data objects to each and every requirement of a specific part of the UI. Start with one data object per table, containing all columns, and one set of CRUD operations, even if that means to include a surrogate key attribute you don't need everywhere. If you really run into trouble with that, optimize later when you arrive at that situation.

  • Im in agreement with Doc. However If this matter worries too much to Randy, What do you think about to implement an specific data model for UI? View Objects that models each UI data structure. Ofc, there's extra engineery to pass from VO to DTO but then, UI would be totally decoupled. A shortcomming would be that this surogate Id would be UI 'concern', so UI would be responsable of its generation and managrment. Its life time would be tied to the UI life time. – Laiv Sep 4 '16 at 9:23
  • @Laiv: that is well known as MVVM architecture. However, in this architecture the "View Model" classes reflect the UI side and are not the ones for which the CRUD code is needed. Answer is related to the "Model" classes in that architecture. – Doc Brown Sep 4 '16 at 20:36

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.