Most bioinformatics projects do not fail on analysis; they fail on bookkeeping. Samples get renamed, batches are mislabelled, and three versions of the same metadata table drift apart until nobody knows which is canonical. A database is the fix, and the first decision is whether you want a relational engine or a document store. The right answer depends almost entirely on how regular your records are, not on which technology is fashionable.
Start with the shape of the data
Ask one question first: does every record of this kind have the same fields? Clinical sample metadata and cohort tables do — every subject has an ID, age, sex, diagnosis, collection date. That regularity is exactly what a relational schema is for. Now consider the counterexample: a heterogeneous collection of experimental records, where a proteomics run, a microscopy session, and a sequencing run share only a few fields and each carries a different nested payload. Forcing those into one wide table produces hundreds of mostly-null columns, which is the classic signal that a document model fits better. The design principle is to let the data's own regularity choose the engine, not the other way around.
Relational modelling for omics metadata
A relational database stores data in tables with declared types, primary keys, and foreign keys that enforce referential integrity 1. MySQL is the pragmatic default for research groups: free, widely deployed, well documented, and easy to host 2. The value of the model is that constraints are enforced by the engine, not by your script. If a sample references a subject that does not exist, the insert fails; you cannot silently log metadata against a phantom patient.
The standard bioinformatics layout is a small star of tables: a study table, a subject table, a sample table linking subject to study, an assay table recording what was measured, and a file table pointing to the actual data on disk. A minimal schema in the usual form:
- study(study_id PK, title, pi, created_at)
- subject(subject_id PK, study_id FK, sex, birth_year)
- sample(sample_id PK, subject_id FK, tissue, collection_date, batch)
- assay(assay_id PK, sample_id FK, platform, protocol_version)
- file(file_id PK, assay_id FK, path, checksum, bytes)
This normalised structure makes the analytical queries you actually need straightforward: join sample to subject and group by batch to find confounded designs, or join assay to file to locate the raw data for one platform. Indexes on the foreign keys and on batch turn these from full scans into lookups.
Enforcing quality at the schema level
The deeper benefit of a typed relational schema is that it encodes your data standards. Controlled vocabularies for tissue and platform become lookup tables instead of free-text strings that fragment into liver, Liver, and hepatic. Constraints reject impossible values at write time. And transactions guarantee that a multi-table update — marking a sample as failed while deprecating its assays and files — either fully succeeds or fully rolls back, which is exactly the consistency a study's metadata needs. In practice the schema is the single most valuable artifact a lab can maintain, because it is the contract every pipeline reads from.
When a document store is the right tool
MongoDB stores schemaless JSON-like documents, each of which can carry its own structure, and models data as a set of collections rather than joined tables 3. That flexibility is a genuine advantage when the schema is unknown or evolving. Consider a metadata catalogue that must ingest submissions from many groups with different metadata conventions, or an experiment registry where each record embeds an arbitrarily nested configuration of instruments, parameters, and derived metrics. Wrapping those into documents means one read returns the whole record, with no joins and no schema migration when a new field appears.
- Good fit: heterogeneous experiment records, nested provenance and configuration, rapidly evolving fields, documents fetched whole.
- Poor fit: highly relational data with many-to-many joins, strict referential integrity, or heavy ad hoc aggregate queries across entity types.
- Watch out: schemaless does not mean standard-free — enforce validation rules in the application layer, or you simply move the inconsistency problem somewhere harder to inspect.
Choosing between them
The choice is not a matter of maturity or performance in the abstract; both engines are fast and battle-tested. It is a choice between enforced regularity and permitted variation. If your records are uniform and your questions are relational — joins, aggregates, integrity across entities — use a relational database. If your records are heterogeneous, nested, and fetched as whole units, use a document store. A hybrid is also perfectly reasonable: MySQL for the canonical study, subject, and sample tables that must be correct, and MongoDB for the flexible, per-experiment payloads that sit alongside them, linked by a shared study identifier.
Practical notes for a research group
Whichever you choose, the same operational habits determine success. Back up automatically and test restores — an unverified backup is a belief, not a safeguard. Use a versioned schema-migration tool so that the database structure is reproducible from source control rather than existing only on the server that happens to be running it. Give each analyst a read-only account and reserve writes for the ingestion path, so a stray query cannot corrupt a study. And keep large raw files on the filesystem or object store, storing only paths and checksums in the database: databases are for metadata and relationships, not for terabytes of BAMs. These disciplines are the data-management face of reproducible research 4, and they pay for themselves the first time a reviewer asks exactly which samples entered an analysis.
References
- Codd EF. A relational model of data for large shared data banks. Commun ACM. 1970;13(6):377-387.
- Chodorow K. MongoDB: The Definitive Guide. 3rd ed. Sebastopol, CA: O'Reilly Media; 2019.
- Silberschatz A, Korth HF, Sudarshan S. Database System Concepts. 7th ed. New York: McGraw-Hill; 2019.
- Sandve GK, Nekrutenko A, Taylor J, Hovig E. Ten simple rules for reproducible computational research. PLoS Comput Biol. 2013;9(10):e1003285.
- Wilkinson MD, Dumontier M, Aalbersberg IJ, et al. The FAIR Guiding Principles for scientific data management and stewardship. Sci Data. 2016;3:160018.
- Roche DG, Kruuk LEB, Lanfear R, Binning SA. Public data archiving in ecology and evolution: how well are we doing? PLoS Biol. 2015;13(11):e1002295.
- Amann RI, Binder BJ, Olson RJ, et al. Combination of 16S rRNA-targeted oligonucleotide probes with flow cytometry for analyzing mixed microbial populations. Appl Environ Microbiol. 1990;56(6):1919-1925.
- Bourne PE, Lorsch JR, Green ED. Perspective: a call for a single, international, interoperable system for reporting bioscience research. Nature. 2015;527(7576):S16-S17.