Research
Organising a research database
From raw file to versioned database: disease-specific core datasets, a pseudonymised index, quality checks, and a query page that shows only aggregates.
The problem
Years of cohort spreadsheets, exam exports and thesis data collections, each in its own format. The question “how many patients do we have with this phenotype and this data item?” takes a week.
Step-by-step method
-
A written pipeline: original → raw → harmonised → database → analysis → export, each stage in its own folder; nothing is modified in place.
-
Core datasets by disease, each with its version (candidate, frozen) and a freeze procedure.
-
A pseudonymised pivot index to de-duplicate patients present in several cohorts, kept on the local workstation.
-
Quality-control rules and a decision log (why a given variable was recoded).
-
Distinguish “measurement in the database” from “file available”: you know what is actually usable.
-
A query page that handles only aggregates: population, criteria, required data, result as counts.
-
Identifiable extraction is done locally, by a script, on the basis of a validated “extraction request”.
-
A “locked drawer” for sensitive data: whatever must never enter AI tools is isolated and documented, and open questions about access to it are settled with the institution.
-
Exports for collaborators: aggregated figures and tables, never individual-level data.
Deliverable
A versioned, documented database, and an answer in minutes to “how many patients for this study?”.
Safeguards
Health data: the regulatory framework applicable to research (declaration, reference methodology, patient information), storage authorised by the institution, no identifying data in an unauthorised service. Consult your institution’s clinical research department and DPO (data protection officer).
Starter prompt
Here is the list (column names only, no data) of my 4 cohort files. Propose a harmonised data dictionary, quality-control rules, and the pipeline folder structure. Do not open any patient data.