Question 1

What data model would you use to store contacts that can have multiple phone numbers, emails, and addresses?

Accepted Answer

Use a normalized schema: a contacts table (id, owner_id, display_name, created_at, updated_at) with child tables — contact_emails (id, contact_id, email, label, is_primary), contact_phones (id, contact_id, e164_phone, label, is_primary), contact_addresses (id, contact_id, street, city, region, postal_code, country, label). Store phone numbers in E.164 format for consistent lookup and SMS/call integration. Index on (owner_id, email) and (owner_id, e164_phone) for fast search. Use a single contacts_fts table or a search engine index for full-text name search.

Question 2

How do you detect and merge duplicate contacts automatically?

Accepted Answer

Generate a set of deduplication signals for each contact: normalized E.164 phone numbers, lowercased email addresses, and a phonetic name key (e.g., Soundex or Double Metaphone of the display name). Store these signals in a contact_signals table. On insert or update, query for contacts with overlapping signals. Score candidate pairs: exact email or phone match = strong duplicate (merge automatically or flag for review); name phonetic match alone = weak signal (suggest to user). When merging, designate one record as the canonical contact, re-point all foreign key references (e.g., group memberships, interaction history) to the canonical ID, and soft-delete the duplicate.

Question 3

How would you design contact group management so a contact can belong to many groups and groups can be nested?

Accepted Answer

Use a contact_groups table (id, owner_id, name, parent_group_id nullable) for the group hierarchy and a contact_group_members junction table (contact_id, group_id) for membership. parent_group_id forms a tree; enforce no cycles at the application layer. To query all contacts in a group including descendants, use a recursive CTE (WITH RECURSIVE) to collect all descendant group IDs, then JOIN with contact_group_members. Cache the flattened membership list in Redis for frequently accessed large groups and invalidate on membership or hierarchy changes. Limit nesting depth (e.g., 5 levels) to bound recursive query cost.

Question 4

How do you implement fast full-text contact search across name, email, and phone for a user with 100,000 contacts?

Accepted Answer

Maintain a search index per user, either using PostgreSQL's tsvector full-text search or a dedicated search engine (Elasticsearch, Typesense). For PostgreSQL, store a generated tsvector column on the contact combining display_name tokens (weight A), email prefixes (weight B), and normalized phone digits (weight C). Create a GIN index on this column. Query with to_tsquery and prefix matching (:*) for autocomplete. For phone lookups, store digits-only strings and match with LIKE '1234%' against an indexed column. At 100K contacts per user, PostgreSQL handles this well; migrate to a dedicated search cluster only when query latency degrades or tenant counts make shared index size unmanageable.

Contact Book Service Low-Level Design: Contact Storage, Deduplication, and Group Management

Contact Schema

Multiple Values Per Field

Deduplication

Contact Merge

Contact Groups

Import from CSV

Import from vCard

Export to vCard

Search and Phone Normalization

Birthday Reminders