ID design and primary keys
Alexey Makhotkin published "ID design and primary keys, pt. 1" on anchorsandlinks.com, the first part of a systematic discussion of primary keys in database design and a bonus chapter from the "Database Design Book." The roughly 2,300-word post begins at the logical level, introducing external IDs: unique identifiers such as part numbers, taxpayer identification numbers like SSN in the United States or BSN in the Netherlands, content management URLs, and ticket strings like FOOBAR-123. The post states that external IDs uniquely identify an entity at any given moment, that an entity may have none, one, or several, and that they can change. The post then defines anchor IDs as required for reliable, unambiguous identification of anchor instances, and states they must be immutable. It notes integer numbers starting from 1, 2, 3 are the most common solution, with unique strings such as "fr" or "CHF", tuples, and UUIDs as other options. It discusses when anchor IDs can serve as external IDs, using an e-commerce orders table with an auto-incremented "orders.id" column as an example, then moves to the physical level, describing a primary key dissociated from business meaning, a tiny content management system with an integer primary key anchor table, and uniqueness constraints. Part 2 will cover composite primary keys.
The post argues developers should derive primary keys from business requirements, separating immutable anchor IDs from changeable external IDs.