SQL and NoSQL for Biological Data Management

MySQL · relational modelling · MongoDB · document stores · omics metadata · schema design

Published 2026 · Last updated 2026-09-27 · Hailu Zhou

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:

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.

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

  1. Codd EF. A relational model of data for large shared data banks. Commun ACM. 1970;13(6):377-387.
  2. Chodorow K. MongoDB: The Definitive Guide. 3rd ed. Sebastopol, CA: O'Reilly Media; 2019.
  3. Silberschatz A, Korth HF, Sudarshan S. Database System Concepts. 7th ed. New York: McGraw-Hill; 2019.
  4. Sandve GK, Nekrutenko A, Taylor J, Hovig E. Ten simple rules for reproducible computational research. PLoS Comput Biol. 2013;9(10):e1003285.
  5. Wilkinson MD, Dumontier M, Aalbersberg IJ, et al. The FAIR Guiding Principles for scientific data management and stewardship. Sci Data. 2016;3:160018.
  6. 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.
  7. 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.
  8. 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.