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 exampleA 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.
How to avoid mixing surrogate key with the rest of the data
design;object oriented;architecture;sql;surrogate keys