Design a unique referral code system
uniqueness under concurrency and code-space sizing.
a unique DB constraint as the source of truth, generation via random retry or an encoded counter, and collision handling.
What's really being asked
This evaluates concurrency-safe uniqueness, awareness of a finite code space, and whether you let the database enforce invariants rather than application-level checks.
The full answer
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.
The mistakes people make
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.
What usually comes next
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?
A 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.
Interview question
Why is a SELECT-then-INSERT existence check insufficient to guarantee unique referral codes?
- a.The alphabet is too small for any uniqueness scheme to work
- b.SELECT statements cannot read newly generated codes
- c.INSERT is slower than SELECT so codes expire
- d.Two concurrent requests can both pass the check before either inserts, creating a duplicateCorrect
Why? this is the answer
The gap between the check and the insert is a race two transactions can both win; only a database unique constraint reliably enforces it. The other options misstate how the operations behave.
Just read this? Test yourself on what you have been reading.
- #referral-system
- #database-design
- #uniqueness
- #concurrency
- #schema
Put your scrolling time to good use
Learn one idea, try a quiz and save useful cards for revision. Tezvyn makes it easy to learn and stay current in your tech field, a few minutes at a time.
The iPhone app is on the way
We are building it. Until it lands, nothing here is held back from you: every interview card, your saved cards, streaks and the job board all work in Safari, plus hundreds of free practice quizzes of thirty questions each. Sign in and it all carries over to the app the day it arrives.
Want it as an icon? Tap Share at the bottom of Safari, then Add to Home Screen. It opens full screen and the cards you have read stay available offline.
We are hiring for this. Every open role lists the topics its interview covers, so you can prepare for the real thing rather than guessing.
See open roles