Design a database schema for a research participant panel

WHAT THIS TESTS: The interviewer wants to see if you can model real-world entities as relational tables without over-engineering. At a beginner level, they care that you recognize which data is static versus transactional, that you know how to use primary and foreign keys, and that you think about data integrity and basic privacy concerns like consent tracking. They are not expecting a data warehouse, but they do expect you to avoid obvious duplication.

A GOOD ANSWER COVERS: A good answer covers five things in order: first, a participants table containing one row per person with fields like participant_id as primary key, email, phone, preferred_name, birth_year, gender, location, and created_at; second, a participation_history table with its own primary key, a participant_id foreign key, plus study_id, study_name, session_date, completion_status, and incentive_amount; third, a consent_log table tracking consent_type, granted_date, and withdrawn_date so you can handle GDPR or IRB requirements; fourth, mention that you would index email for lookups and participant_id for joins; fifth, note that sensitive PII might be encrypted at rest or stored in a separate schema if security requirements demand it.

COMMON WRONG ANSWERS: Common wrong answers include three patterns: a giant flat table that repeats contact information every time someone joins a study is the biggest red flag because it violates first normal form and makes updates error-prone; dumping all history into a single JSON column in the participants table to avoid joins sacrifices queryability and integrity; and forgetting consent tracking entirely, which signals you have not worked under research ethics or privacy regulations.

LIKELY FOLLOW-UPS: Likely follow-ups include three scenarios: the interviewer might ask how you would prevent the same person from being invited to two studies on the same day, which you can solve with a unique constraint or application-level check on participation_history; they might ask how you handle soft deletes if a participant wants their data removed, suggesting a deactivated_at timestamp rather than hard deletion; and they might ask how to track demographic changes over time, which could push you toward a slowly-changing-dimension pattern or a separate demographic_history table.

ONE CONCRETE EXAMPLE: One concrete example is a participant named Alex with email alex@example.com. In the participants table, Alex gets participant_id 1001. When Alex completes a usability test on March 1, a new row appears in participation_history with participant_id 1001, study_id 45, session_date 2024-03-01, and incentive_status paid. When Alex opts into a diary study on March 15, a consent_log row is added with consent_type diary_study and granted_date 2024-03-15. A query to find all unpaid incentives simply joins participants to participation_history on participant_id and filters where incentive_status equals pending.