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