Q: It depends a lot on how the users will interact with the rename, and why is it happening so often. Some more details on that would help.
A: Hi David, this happens often when:
- The primary key is a composite key derived from values of other fields in the same record
- There is no way to know what the value of the key before runtime
- Lookup is required from other tables (meaning the primary key must also be a foreign key in other tables)
One realworld example would be the way Serial Shipping Container Code (SSCC) is implemented, the industry standard tracking number for use in supply chain management. This18-digit number has to be the primary key for tracking and consists of the following parts:
- Packaging Code (1 digit)
- EAN.UCC Company Prefix (7 digits)
- Serial Reference (9 digits)
- Checksum (1 digit)
When user runs a form and start a packing task, he always needs to select a Packaging Code (known only at runtime). The warehouse staff would not know whether he will use a Carton, Case, or Bag to pack the order until he sees the items. Serial Reference is not necessarily sequential like a number we could just grab from No. Series because the Serial Reference has to consist of an Sales Order No. and the count of each container. Finally checksum is always calculated and its value is unknown until you provide 1, 2, and 3. In our implementation, we also have a Job No. field as part of the primary key in order to allow a new Rec to be created & initialized first so the user has a chance to fill in the other 2 fields.
A second example would be a manifest (we have an SCM solution that uses 3 custom tables: Manifest, Shipment, and Package). A manifest is a way of grouping shipments for pickup/dispatch and is to be sent to the Shipping Agent (again it is only known at run time). The Manifest No. (primary key) is defined by a Ship-from Code (Pickup Location), Shipping Agent Code, and Shipment Date. A company could have 10 different shipments but wish to put them on 2 different manifests (5 shipments on each). Or they could also choose to put 10 shipments on one manifest. Why this undetermined? Beacause this is often a very last-minute decision depending on available Shipping Agents we could book at the time. If we don’t use Manifest No. as the key what else could we have used? Line No.? Entry No.? The last two could never provide meaningful lookup.