Guide / Best practices
Creating unique client IDs that stay unique
Generating an ID is easy; guaranteeing it stays unique as the system grows is the real job. Here is how production systems do it, and the rules that keep identifier schemes healthy over time.
The standard approaches
Auto-increment columns and sequences
The most common method: the database assigns the next integer automatically when a row is inserted (an IDENTITY column or a SEQUENCE object in SQL). Uniqueness is structural — the database hands out each number once. Sequences are slightly more flexible than identity columns when you need explicit control over numbering.
Watch for gaps: rolled-back transactions and failed inserts can leave holes in the sequence. That is normal and harmless — IDs are identifiers, not counters.
UUIDs (version 4)
When IDs must be created in application code, across services, or on devices that may be offline, randomly generated UUIDs are the standard answer. The 128-bit space makes accidental collision effectively impossible, so no coordination is needed. Store them in a native UUID column type rather than a string where your database supports it.
Random codes with a unique constraint
For customer-facing codes (like CLT-8X4K2Q), generate a random string and let the database's unique constraint be the backstop: attempt the insert, and on the rare collision, generate again. With 8+ characters from a 32-character alphabet, collisions are rare enough that retries are a non-event.
Rules for healthy ID schemes
- Assign IDs at creation, once. Set the identifier when the record is created and never change it afterwards.
- Never reuse IDs. A deleted client's ID stays retired forever — reuse invites ghost-record bugs.
- Enforce uniqueness in the database. Application-level checks race; a primary key or unique constraint does not.
- Keep IDs opaque. Do not encode names, dates of birth or other personal data into identifiers.
- Separate internal keys from public references. Use the compact internal key for joins and a friendlier prefixed code wherever humans see it.
- Use one ID per customer across systems. A persistent, universal customer ID that links records across channels is the foundation of a unified customer view.
Common mistakes
- Using email addresses as keys — emails change, get reused and are typed wrong.
- Letting the frontend invent IDs without a server-side uniqueness check.
- Exposing sequential IDs in public URLs without authorization checks, enabling enumeration.
- Changing ID format mid-life without a migration plan for every integration that stored the old format.