<p>The <a href="http://ga4gh.org/" rel="noreferrer">Global Alliance for Genomics and Health</a> 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 <a href="https://github.com/ga4gh/ga4gh-schemas" rel="noreferrer">GitHub repository</a>, 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.</p>

<p>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.</p>

CC BY-SA 3.0
2017-05-22T17:01:00.507Z
2017-05-22T18:08:04.757Z
2017-05-22T18:08:04.757Z
woemler
<p>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 <a href="https://www.mongodb.com/" rel="noreferrer">MongoDB</a>) 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.</p>

CC BY-SA 3.0
2017-05-22T18:04:31.807Z
2017-05-22T18:04:31.807Z
Daniel Standage
<p>For <em>metadata</em>, I would use a SQL schema something like the following:</p>

<pre><code>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)
);
</code></pre>

<p>In the schema, you have <code>Project</code> and biological <code>Sample</code> tables, which are independent of each other at the high level. An <code>AnalysisSample</code> describes a sample used in BAM or VCF and connects <code>Project</code> and biological <code>Sample</code>. Importantly, each <code>AnalysisSample</code> 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 <code>Collection</code> 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 <code>ReadGroup</code> table. Finally, a <code>VariantSet</code> is a VCF file. <code>VariantSample</code> tells you which samples are included in each VCF file.</p>

<p>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 <code>Collection</code>, read length to <code>ReadGroup</code> and family ID to <code>Sample</code>). You also need indices for efficient table joining and perhaps more tables for complex structures (e.g. pedigree).</p>

<p>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.</p>

CC BY-SA 3.0
2017-05-22T19:31:41.353Z
2017-05-22T19:31:41.353Z
user172818
<p>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).</p>

<p>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: <strong>Does there exist a schema or schema guidelines for this type of database?</strong></p>

<p>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: <a href="http://biorxiv.org/content/early/2017/05/10/136358" rel="noreferrer" title="Managing The Analysis Of High-Throughput Sequencing Data">http://biorxiv.org/content/early/2017/05/10/136358</a></p>

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

CC BY-SA 3.0
2017-05-22T16:43:29.150Z
Are you looking to store processed or unprocessed data?  What would an example file format be that you would be trying to capture?
woemler
CC BY-SA 3.0
2017-05-22T16:59:57.300Z
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
CC BY-SA 4.0
2018-11-21T20:25:40.797Z
Hey Gus, we are also doing the same research and created this question in Biostars, let us know if you found something! https://www.biostars.org/p/350514/
jsmedmar
<ngs><database>
Designing a lab NGS file database schema
# Designing a lab NGS file database schema

(Asked by: username_0 on 2017-05-22)

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](http://biorxiv.org/content/early/2017/05/10/136358 "Managing The Analysis Of High-Throughput Sequencing Data")

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

**Tags:** ngs, database
> **Comments:**
> **username_1**: Are you looking to store processed or unprocessed data?  What would an example file format be that you would be trying to capture?  
> **username_0**: 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.  
> **username_2**: Hey Gus, we are also doing the same research and created this question in Biostars, let us know if you found something! https://www.biostars.org/p/350514/  

---

## 3 answers

### Answer by username_4 on 2017-05-22. Score: 5 | Accepted answer

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.


---

### Answer by username_1 on 2017-05-22. Score: 9

The [Global Alliance for Genomics and Health](http://ga4gh.org/) 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](https://github.com/ga4gh/ga4gh-schemas), 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.


---

### Answer by username_3 on 2017-05-22. Score: 5

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](https://www.mongodb.com/)) 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.


---

