Design Tables for a National ID Registry
Design the tables and relationships for a registry that gives each resident one ID number, then keep a lookup for one person fast as the registry grows past a million people.
Problem statement
Design the database tables for a national ID registry and the relationships between them. Then explain how to keep a search for one person fast once the registry holds more than a million people.
For this exercise, assume a government registry gives every enrolled resident one permanent 12-digit ID number. An operator at an enrollment center records the person's full name, date of birth, gender, address, mobile number and an optional email, and captures a photo and fingerprints.
Image files are kept in a separate file store, and the tables hold only references to them. Before a number is issued, a provided matching service compares the fingerprints with those of existing residents. If it reports a likely match, the enrollment waits for a staff review instead of creating a second number.
Also assume residents can later change their address, mobile number or email, and the registry keeps every earlier value with the dates it applied, so staff can see what a record said on any past date.
Other government agencies verify a person by sending an ID number, a name and a date of birth, and get back only yes or no. Registry staff look up a person by ID number, or by mobile number when the person has forgotten their ID number.
Several residents, such as members of one family, may share a mobile number or an email address. You may use any database. Staff login and permissions, how the matching service works inside, and printing ID cards are out of scope.
What to build
- An operator enrolls a resident, and the registry issues one new 12-digit ID number unless the matching service reports a likely existing match, in which case the enrollment waits for staff review.
- A resident's address, mobile number or email can be changed, and every earlier value stays on record with the dates it applied.
- An agency sends an ID number, a name and a date of birth and gets back only yes or no.
- Registry staff find a person by ID number, or find everyone with a given mobile number, and see current details.
At scale
- The registry already holds more than 1 million people. For this exercise, assume it must be ready to grow to about 100 million over several years, with about 50,000 enrollments and detail changes a day.
- For this exercise, assume agencies send up to about 2,000 verification requests a second at peak, each answered within 100 ms, a newly issued number must verify within 1 minute, and a staff lookup returns within 1 second.
- For this exercise, assume each person's text data, history included, takes about 2 KB, and their photo and fingerprint files take about 1 MB.