Design a unique referral code system

WHAT THIS TESTS This evaluates concurrency-safe uniqueness, awareness of a finite code space, and whether you let the database enforce invariants rather than application-level checks.

A GOOD ANSWER COVERS Schema: a referral_codes or users table with a code column carrying a unique constraint and index, plus the owning user id. The unique constraint is the authority. Generation, two viable approaches. Random: sample 6 characters from an alphabet, attempt insert, and on a unique-violation error retry with a new code; with a sufficiently large space the retry rate is negligible. Deterministic: take a monotonic counter or id and encode it into the base-N alphabet, optionally with an obfuscating permutation so codes are not sequential, guaranteeing uniqueness without retries. Validation is a single indexed lookup. Note the space: an alphabet of about 36 characters over 6 positions is on the order of two billion codes, ample for most products but worth sizing. Exclude ambiguous characters like O and 0 for shareability.

COMMON WRONG ANSWERS SELECT to check existence then INSERT, a classic race where two requests both see free and both insert. No unique constraint, trusting application logic. Ignoring the finite space and birthday-collision growth. Allowing confusing characters that users mistype.

LIKELY FOLLOW-UPS Why does the check-then-insert race, and how does the unique constraint fix it? How does collision probability grow as you issue more codes? Random versus counter-encoded, what are the tradeoffs?

ONE CONCRETE EXAMPLE The code column has a unique index. On signup the service generates a random 6-character code from a 32-character unambiguous alphabet and inserts it; if the database returns a unique violation, it generates a new code and retries. A redemption simply looks up the code via the index and credits the owner, rejecting unknown codes.