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/136358Alternatively, can anyone provide links to where I might find relevant guidelines or supply personal advice?
Designing a lab NGS file database schema
ngs;database;schema;sample database
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));CREATE TABLE ReadGroup (    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.