# Designing a lab NGS file database schema

I am the resident Bioinfo Geek in a hospital academic lab that routinely employs NGS as well as CyTOF and other large volume data producing technologies. I am sick of our current "protocol" for metadata collection and association with the final products (miriad excel sheets and a couple poorly designed RedCap DBs).

I want to implement a central structured, controlled datastore that will take care of this. I know that the interface to the technicians how will be inputing the data is crucial to its adoption, but this is not the focus of THIS particular question: Does there exist a schema or schema guidelines for this type of database?

I would rather use a model that has been developed by people who know how to do this well. I know of BioSQL but it seems more geared towards full protein/nucleotide records like those found in uniprot or genbank. That is not what we have here. What I want is something similar to the system touched on in this preprint: http://biorxiv.org/content/early/2017/05/10/136358

Alternatively, can anyone provide links to where I might find relevant guidelines or supply personal advice?

• Are you looking to store processed or unprocessed data? What would an example file format be that you would be trying to capture? May 22 '17 at 16:43
• This is mostly for primary data organization: we get 800 BAMs of WES and I want the file location of each BAM associated with metadata like: PROJECT, READ_LENGTH, SAMPLE_NAME, FAMILY_ID, DATA_TYPE, DIAGNOSIS, etc.
– Gus
May 22 '17 at 16:59
• Hey Gus, we are also doing the same research and created this question in Biostars, let us know if you found something! biostars.org/p/350514 Nov 21 '18 at 20:25

For metadata, I would use a SQL schema something like the following:

CREATE TABLE Project (
ac TEXT, -- project/Study accession
PRIMARY KEY (ac)
);
CREATE TABLE Sample ( -- biological sample/biopsy
ac TEXT,
PRIMARY KEY (ac)
);
CREATE TABLE AnalysisSample (
prj_ac TEXT, -- project acccession (Project.ac)
symbol TEXT, -- a short name unique in the project
sample_ac TEXT, -- sample accession (Sample.ac)
PRIMARY KEY (prj_ac, symbol)
);
CREATE TABLE Collection ( -- a BAM file
ac TEXT, -- collection/alignment file accession
prj_ac TEXT, -- project accession (Project.ac)
PRIMARY KEY (ac)
);
cl_ac TEXT, -- collection accession (Collection.ac)
rg_id TEXT, -- @RG-ID
sample_sym TEXT, -- @RG-SM; matching AnalysisSample.symbol
PRIMARY KEY (cl_ac, rg_id)
);
CREATE TABLE VariantSet ( -- a VCF file
ac TEXT, -- VCF file accession
prj_ac TEXT, -- project accession (Project.ac)
PRIMARY KEY (ac)
);
CREATE TABLE VariantSample (
vs_ac TEXT, -- VCF file accession (VariantSet.ac)
sample_sym TEXT, -- sample symbol in the VCF file; matching AnalysisSample.symbol
PRIMARY KEY (vs_ac, sample_sym)
);


In the schema, you have Project and biological Sample tables, which are independent of each other at the high level. An AnalysisSample describes a sample used in BAM or VCF and connects Project and biological Sample. Importantly, each AnalysisSample has a symbol unique in a project (see the primary index). This is the symbol on a BAM read group line or on a VCF sample line. A Collection is in effect a BAM/CRAM file. In theory, a BAM file may contain more than one samples (though rare in practice), which is addressed by a separate ReadGroup table. Finally, a VariantSet is a VCF file. VariantSample tells you which samples are included in each VCF file.

This is the skeleton of a full schema. You can add extra fields to appropriate tables (e.g. file path and hg19/hg38/etc to Collection, read length to ReadGroup and family ID to Sample). You also need indices for efficient table joining and perhaps more tables for complex structures (e.g. pedigree).

For the projects I have participated, this schema should work most of time. It is inspired by GA4GH's JSON schema, but my version is in SQL, is simpler and also has a slightly different structure which I think is better.

The Global Alliance for Genomics and Health has been working on the issue of representing sequencing data and metadata for storage and sharing for quite some time, though with mixed results. They do offer a model and API for storing NGS data in their GitHub repository, but it can be a bit of a pain to get a high-level view. I am not sure if any better representation of this exists elsewhere.

I can say from personal experience (having built over a dozen genomic databases), there is no ideal data model and storage best practices. Genomic data comes in many shapes and sizes, and your needs are going to vary from every other organization, so what works for one bioinformatics group won't necessarily work for you. The best thing to do is design and implement a model that will cover all of the data types in your workflow and downstream analyses you might do with the data and metadata.

I agree that there is no ideal data model that is going to be stable for very long in a quick-moving field like genome informatics. Perhaps a schema-less (NoSQL or some other document-based system, such as MongoDB) database approach would work better? This gives you ultimate flexibility to attach whatever information is relevant to database entries you're adding to your database now, without the need to rebuild the database later if you want to attach more/different information to subsequent database entries.