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.
