Most bioinformatics databases fail for social reasons, not technical ones: no one updates them, the schema cannot absorb a new assay, and the interface assumes the user already knows the answer. If you are building one, the data model and the submission path deserve more thought than the front page. This post covers the database and user-interface side — the workflow tooling that feeds such a system is a separate topic.
Start from the questions, not the tables
A schema is a commitment, and the cheapest way to get it wrong is to start from the files you happen to have. Write down the queries the database must answer — "all variants in gene X across tissues", "which samples came from this cohort", "what changed in this record on date Y" — and design entities that make those queries simple and fast. If a query needs three joins across optional tables, your model is probably wrong.
Three decisions dominate everything downstream:
- Grain: what is one row? One sample, one measurement, one call? Mixed grain produces ambiguous joins.
- Reference versus derived: separate raw submissions from computed annotations so re-annotation does not destroy provenance.
- Identity and versioning: every record needs a stable identifier and a history, or you cannot cite it.
Relational or not
Biological data is mostly relational: samples belong to projects, measurements belong to samples, annotations belong to variants. PostgreSQL with a normalised schema handles this well, and JSONB columns absorb the assay-specific fields that would otherwise spawn a new table every six months. Reach for a document store only for genuinely schema-free payloads, and a graph database only when traversal — not lookup — is the dominant access pattern. Choosing a document store because the input was JSON is a common and expensive mistake.
Django versus Flask
| Dimension | Django | Flask |
|---|---|---|
| Data model | Batteries-included ORM + migrations | SQLAlchemy + Alembic, your assembly |
| Admin UI | Built-in, immediately useful | Third-party, more work |
| API layer | DRF, batteries included | Minimal, compose your own |
| Best fit | Full data portals with curation | Small services and APIs |
For a database that curators must edit daily, Django's admin and migration system save weeks of work. For a lean API wrapping one analysis, Flask stays out of the way. Both are fine; what is not fine is choosing a framework and then hand-rolling its missing half.
Submission and update workflows
The submission path determines whether your database is alive in five years. Design it around four stages:
- Ingest: accept a documented format, validate it against a schema, and reject early with actionable errors.
- Stage: land submissions in a quarantine table, not in the live database. Nothing user-submitted touches production directly.
- Review: a curator approves, edits, or returns submissions, with the decision recorded.
- Publish and version: the approved record becomes live with an immutable identifier; the previous version is retained.
Provenance is the point. Every record should record who submitted it, when, from what source, and what pipeline version produced the derived fields. Without that, users cannot judge trustworthiness, and you cannot debug a bad annotation after the fact. Pair identifier minting with a stable version scheme; a DOI-style accession that changes silently is worse than no accession.
User interfaces that respect the user
Three interface principles cover most of the failure modes:
- Search must be forgiving: accept gene symbols, accessions, and common synonyms; be explicit when a query matched nothing and suggest alternatives.
- Show provenance inline: source, version, and last-updated on the record page, not buried in a documentation tab.
- Expose bulk access: a documented REST API and a downloadable flat file alongside the web form. Power users will script against you regardless; make it supported.
Every page should have a stable URL that can be cited, and every table should be downloadable. If your interface requires a tutorial, the interface is the problem.
Operations and the long tail
Run migrations before deploys and keep them reversible. Back up the database and test the restore — an untested backup is a belief, not a backup. Monitor slow queries and add indexes against real query plans. Most importantly, plan for the data volume you will have in three years, not the volume you have today: re-partitioning a live database is far worse than designing for growth.
Closing
A bioinformatics database is a product with a maintenance contract. Budget for curation and operations, keep the schema honest, version everything, and never let a submission bypass review. The science will change; a clean data model and a real submission workflow are what let the system change with it.
References
- Berman HM, Westbrook J, Feng Z, et al. The Protein Data Bank. Nucleic Acids Res. 2000;28(1):235–242.
- Barrett T, Wilhite SE, Ledoux P, et al. NCBI GEO: archive for functional genomics data sets — update. Nucleic Acids Res. 2013;41(D1):D991–D995.
- Mailman MD, Feolo M, Jin Y, et al. The NCBI dbGaP database of genotypes and phenotypes. Nat Genet. 2007;39(10):1181–1186.
- Perez-Riverol Y, Csordas A, Bai J, et al. The PRIDE database and related tools and resources in 2019: improving support for quantification data. Nucleic Acids Res. 2019;47(D1):D442–D450.
- Wilkinson MD, Dumontier M, Aalbersberg IJ, et al. The FAIR Guiding Principles for scientific data management and stewardship. Sci Data. 2016;3:160018.
- Deutsch EW, Bandeira N, Sharma V, et al. The ProteomeXchange consortium in 2020: enabling 'big data' approaches in proteomics. Nucleic Acids Res. 2020;48(D1):D1145–D1152.
- Cherry JM, Hong EL, Amundsen C, et al. Saccharomyces Genome Database: the genomics resource of budding yeast. Nucleic Acids Res. 2012;40(D1):D700–D705.
- Cerami EG, Gross BE, Demir E, et al. Pathway Commons, a web resource for biological pathway data. Nucleic Acids Res. 2011;39(Suppl 1):D685–D690.