Q: I use a disconnected architecture in which sales agents take their systems to the field and add new customers. How can I create unique primary keys that are easy for customers to use? We have a few thousand customers, so an integer (rather than a GUID) would be best and easiest for the agents. However, I want to leave a printed invoice with customers that shows their real customer numbers.
A: I suggest using the “sales book” technique for this architecture. That is, the salesperson is given a preprinted set of invoices that include the unique primary key value. Back in the office these values can be scanned into the system. Because no two sales books have the same sequence, you won’t have any collisions. You could accomplish the same result with a paperless approach that preassigns a block of rows to a specific salesperson. When the data is entered, the application uses one of the prepopulated data rows so there are no INSERT operations, just UPDATES. Collision avoidance is easy. Because the salesperson “owns” the row, no other applications can change the data until populated on the server. This occurs when the salesperson gets back in touch with the home office and posts updates.
—William Vaughn
