Design metadata and data structure for a searchable research repository

WHAT THIS TESTS: This question evaluates your information architecture instincts for qualitative research data. Interviewers want to see that you understand the difference between raw evidence and synthesized insights, that you can design for searchability over time, and that you know how to balance structure with flexibility. The best answers show awareness of stakeholder needs, not just researcher convenience.

A GOOD ANSWER COVERS: First, a relational schema. Propose separate but linked tables for Studies, Participants, Insights, and Evidence or Clips. Studies hold project context like date, method, and status. Participants store segment data and consent. Insights capture findings, tags, and severity. Evidence links back to raw files, timestamps, and transcripts. Second, metadata fields. Include study date, research method, product area, participant segment, theme tags using a controlled vocabulary, sentiment or severity, and URLs to source assets. Third, search strategy. Explain how linked records enable filtering across studies, how consistent tagging supports theme extraction, and how a unique ID system prevents duplicates. Fourth, scalability guardrails. Mention starting with a minimal viable taxonomy, documenting naming conventions, and avoiding deep hierarchies that slow entry.

COMMON WRONG ANSWERS: A flat single-table spreadsheet where every row mixes studies, participants, and insights. Over-engineering with dozens of mandatory fields that create friction and cause abandonment. Using ambiguous or personal tags instead of a shared controlled vocabulary. Ignoring the link between an insight and its raw evidence, which destroys trust and traceability. Proposing complex automations before the team has agreed on core categories.

LIKELY FOLLOW-UPS: How would you handle duplicate insights across multiple studies? How do you onboard stakeholders who are not researchers to search this? What is your migration plan if the team outgrows Airtable? How do you balance speed of tagging during analysis with long-term searchability?

ONE CONCRETE EXAMPLE: In Airtable, create a Studies table with fields for date, method, and product area. Create a Participants table with segment and consent status, linked to Studies. Create an Insights table with a long text field for the finding, a single-select for severity, and a linked field to a Themes table that enforces controlled vocabulary. Create an Evidence table with attachments for video clips, a duration field, and a linked field to both the Insight it supports and the Participant who said it. This lets a teammate filter by theme and immediately see the linked clip, transcript timestamp, and study context without scrolling through unrelated rows.