Edit this page | Blame

MariaDB Database Architecture

The GeneNetwork database is running on MariaDB and the layout is almost carved in stone because so much code depends on it. We are increasingly moving material out into lmdb (genotypes and phenotypes) and virtuoso (all types of metadata), but this proves a lengthy and rather tedious process. We also run redis for cachine, sqlite for authentication, and xapian for search!

In this document we'll discuss where things are, where they ought to go, and how the nomenclature should change.

An SVG of the SQL layout can be found here

Nomenclature

These are the terms we use

More on naming

Naming convention-wise there is a confusing use of id and data-id in particular. We should stick to the table-id naming.

The small test database (2GB)

The default install comes with a smaller database which includes a number of the BXDs and the Human liver dataset (GSE9588).

GeneNetwork database

Estimated table sizes with metadata comment for the important tables

select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size in MB` from information_schema.TABLES where table_schema = "db_webqtl" order by data_length;

+-------------------------+------------+
| table_name              | Size in MB | Should be named:
+-------------------------+------------+
| PublishData             |      22.54 | ClassicTraitValues  <- data-id, strain-id, value (3M traits)
| PublishSE               |       4.71 | ClassicTraitValueError (300K traits) <- data-id, strain-id, value
| PublishXRef             |       2.18 | List of publications <- id, data-id, inbred-id, pheno-id, pub-id
| ProbeSetData            |   59358.80 | BulkTraitValues     <- id, strain, value
| ProbeSetSE              |   14551.02 | BulkTraitValueError <- SE values aligns with ProbeSetData
| ProbeSetXRef            |    4532.89 | PrecomputedLRS      <- precomputed LRS values, pointing to dataset+trait
| ProbeSet                |    2880.21 | ProbeSetInfo        <- over utilized mRNA probeset description, e.g. 100001_at comes with sequence info
| ProbeSetFreeze          |       0.22 | DatasetInfo         <- dataset description, e.g. "Hippocampus_BXD_Jun06" - probesetfreezeid points to dataset, shortname, public?
| Probe                   |    2150.30 | ProbeInfo           <- Probe trait info incl sequence, id, probeset-id
| ProbeFreeze             |       0.06 | Dataset names       <- Similar to ProbesetFreeze, id, chip-id, inbredset-id, tissue-id
| Phenotype               |       6.50 | PhenotypeMeta       <- "Hippocampus weight", id, prepublish short-name, postpublish short-name
| ProbeXRef               |     743.38 | ProbeFreezeDataIDs  <- link ProbeFreeze-Id,Probe-Id with Data-Id
| Datasets                |       2.31 | DatasetMeta         <- "Data generated by...", investigator-id, publication title
| NStrain                 |       4.80 | StrainCountDataId   <- Strains used in dataset, count, strain-id, data-id
| Strain                  |       1.07 | StrainNames         <- with species ID and alias, id, species-id, name
| TissueProbeSetData      |      74.42 |                     <- link Id,TissueID with value
| TissueProbeSetXRef      |      14.73 | TissueGeneTable? <- data-id, gene-id, mean, symbol, TissueProbeSetFreezeId | ProbesetId | DataId
| TissueProbeSetFreeze    |       0.01 | tissueprobefreeze-id
| InbredSet               |       0.01 | InbredSetMeta -> Id,SpeciesId,FullName
| ProbeData               |   22405.44 | (OLD?) mRNAStrainValues used for partial correlations <- id, strain, value = individual probe data (mRNA) [GN1,GN3]
| ProbeSE                 |    6263.83 | (OLD?) Trait Error  <- trait SE aligns with ProbeData? [GN3]
+-------------------------+------------+

Less commonly used tables:

+-------------------------+------------+
| table_name              | Size in MB |
+-------------------------+------------+
| LCorrRamin3             |   18506.53 |
| SnpAll                  |   15484.67 |
| SnpPattern              |    9177.05 |
| QuickSearch             |    5972.86 |
| GenoData                |    3291.91 | Strain by genotype - only used in GN1
| CeleraINFO_mm6          |     989.80 |
| pubmedsearch            |    1032.50 |
| GeneRIF_BASIC           |     448.54 |
| BXDSnpPosition          |     224.44 |
| EnsemblProbe            |     133.66 |
| EnsemblProbeLocation    |     105.49 |
| Genbank                 |      37.71 |
| AccessLog               |      42.38 |
| GeneList                |      34.11 |
| Geno                    |      33.90 | Marker probe info (incl. sequence)
| MachineAccessLog        |      28.34 |
| IndelAll                |      22.42 |
| ProbeH2                 |      13.26 |
| GenoXRef                |      22.83 |
| TempData                |       8.35 |
| GeneList_rn3            |       5.54 |
| GORef                   |       4.97 |
| temporary               |       3.59 |
| InfoFiles               |       3.32 |
| Publication             |       3.42 |
| Homologene              |       5.69 |
| GeneList_rn33           |       2.61 |
| GeneRIF                 |       2.18 |
| Vlookup                 |       1.87 |
| H2                      |       2.18 |
| IndelXRef               |       2.91 |
| GeneMap_cuiyan          |       0.51 |
| user_collection         |       0.30 |
| CaseAttributeXRef       |       0.44 |
| StrainXRef              |       0.56 |
| GeneIDXRef              |       0.77 |
| Docs                    |       0.17 |
| News                    |       0.17 |
| GeneRIFXRef             |       0.24 |
| Sample                  |       0.06 |
| login                   |       0.06 |
| user                    |       0.04 |
| TableFieldAnnotation    |       0.05 |
| DatasetMapInvestigator  |       0.05 |
| User                    |       0.04 |
| TableComments           |       0.02 |
| Investigators           |       0.02 |
| DBList                  |       0.03 |
| Tissue                  |       0.02 |
| GeneChip                |       0.01 |
| GeneCategory            |       0.01 |
| SampleXRef              |       0.01 |
| SnpAllele_to_be_deleted |       0.00 |
| Organizations           |       0.01 |
| PublishFreeze           |       0.00 |
| GenoFreeze              |       0.00 | Used for public/private
| Chr_Length              |       0.01 |
| SnpSource               |       0.00 |
| AvgMethod               |       0.00 |
| Species                 |       0.00 |
| Dataset_mbat            |       0.00 |
| TissueProbeFreeze       |       0.00 |
| EnsemblChip             |       0.00 |
| UserPrivilege           |       0.00 |
| CaseAttribute           |       0.00 |
| MappingMethod           |       0.00 |
| DBType                  |       0.00 |
| InfoFilesUser_md5       |       0.00 |
| GenoCode                |       0.00 |
| DatasetStatus           |       0.00 |
| GeneChipEnsemblXRef     |       0.00 |
| GenoSE                  |       0.00 |
| user_openids            |       0.00 |
| roles_users             |       0.00 |
| role                    |       0.00 |
| Temp                    |       NULL |
+-------------------------+------------+
97 rows in set, 1 warning (0.01 sec)

All *Data tables are large

Tables containing trait values

A trait on GN is defined by a trait-id with a dataset-id.

The trait-id can also be a probe name

One of the more problematic aspects of GN is that there are two tables containing trait values (actually there are three!). ProbeSetData mostly contains expression data. PublishData contains 'classical' phenotypes. ProbeData is considered defunct.

So, a set of trait values gets described by the dataset+probe (trait_id) OR by BXDPublish --- which is its own table --- and an identifier, here 10031.

OK, let's look at the ProbeSetData (expression) traits:

MariaDB [db_webqtl]> select * from ProbeSetData limit 5;
+----+----------+-------+
| Id | StrainId | value |
+----+----------+-------+
|  1 |        1 | 5.742 |
|  1 |        2 | 5.006 |
|  1 |        3 | 6.079 |
|  1 |        4 | 6.414 |
|  1 |        5 | 4.885 |
+----+----------+-------+
5 rows in set (0.193 sec)
MariaDB [db_webqtl]> select * from ProbeData limit 5;
+--------+----------+---------+
| Id     | StrainId | value   |
+--------+----------+---------+
| 503636 |       42 | 11.6906 |
| 503636 |       43 | 11.4205 |
| 503636 |       44 | 11.2491 |
| 503636 |       45 | 11.2373 |
| 503636 |       46 | 12.0471 |
+--------+----------+---------+
5 rows in set (0.183 sec)

ProbeSet describes ProbeSetData. I.e., every probe ID comes with a sequence (microarray) etc.

As for duplicated data: duplicated or "detached"* data happens sometimes, though that's not related to the PublishData/ProbeSetData distinction (unless this is done deliberately for some reason). I believe that whether data is entered as one or the other primarily comes down to the desire/need to divide it into datasets (or by tissue) within a group (with mRNA expression data just being the most common reason for this). I've encountered a situation before with Arthur where there was data in ProbeSetData that wasn't also in ProbeSetXRef

an you give an example of exactly what you mean? PublishData would be stuff like sex, weight, etc (is this what you mean?) while ProbeSetData is used for mRNA expression data (except for a few situations where it isn't lol).

That being said, *functionally*, I think the only real distinction (aside from what metadata is displayed) is that "ProbeSet" data has extra levels of "granularity" where it's also organized by tissue type and can be split into "datasets" (while "PublishData" traits are only associated with a Group (InbredSet in DB). That's why some non-mRNA expression data is still classified as "ProbeSet" - I think it's basically just a way to separate it into datasets within a group, often for specific tissues.

So the organization is something like this:

Group -> PublishData
Group -> Tissue -> Dataset -> ProbeSetData

ProbeData

[OBSOLETE] ProbeData meanwhile is a table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be *deleted* later since it is only used by the Probe Table display in GN1. Not used in GN2 "ProbeData" should probably be "AssayData" or something more neutral.

In comparison the "ProbeSetData" table contains more molecular assay data, including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).

"StrainId" should be "CaseId" or "SampleId" or "GenometypeId", see nomenclature above.

select * from ProbeData limit 2;
+--------+----------+---------+
| Id     | StrainId | value   |
+--------+----------+---------+
| 503636 |       42 | 11.6906 |
| 503636 |       43 | 11.4205 |
+--------+----------+---------+
2 rows in set (0.00 sec)

select count(*) from ProbeData limit 2;
+-----------+
| count(*)  |
+-----------+
| 976753435 |
+-----------+
1 row in set (0.00 sec)

PublishData

These are the classic phenotypes under BXDPublish.

MariaDB [db_webqtl]> select * from PublishData where StrainId=5 limit 5;
+---------+----------+------------+
| Id      | StrainId | value      |
+---------+----------+------------+
| 8967043 |        5 |  49.000000 |
| 8967044 |        5 |  50.099998 |
| 8967045 |        5 | 403.000000 |
| 8967046 |        5 |  45.500000 |
| 8967047 |        5 |  44.900002 |
+---------+----------+------------+
5 rows in set (0.265 sec)
MariaDB [db_webqtl]> select * from PublishSE where StrainId=5 limit 5;
+---------+----------+-------+
| DataId  | StrainId | error |
+---------+----------+-------+
| 8967043 |        5 |  1.25 |
| 8967044 |        5 |  0.71 |
| 8967045 |        5 |   8.6 |
| 8967046 |        5 |  1.23 |
| 8967047 |        5 |  1.42 |
+---------+----------+-------+
5 rows in set (0.203 sec)
MariaDB [db_webqtl]> select * from PublishXRef limit 2;
+-------+-------------+-------------+---------------+---------+-------------------+----------------+------------------+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Id    | InbredSetId | PhenotypeId | PublicationId | DataId  | mean              | Locus          | LRS              | additive         | Sequence | comments                                                                                                                                                     |
+-------+-------------+-------------+---------------+---------+-------------------+----------------+------------------+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 10001 |           1 |           4 |           116 | 8967043 | 52.13529418496525 | rs48756159     | 13.4974911471087 | 2.39444435069444 |        1 | robwilliams modified post_publication_description at Mon Jul 30 14:58:10 2012
robwilliams modified post_publication_description at Sat Jan 30 13:48:49 2016
 |
| 10002 |           1 |          10 |           116 | 8967044 | 52.22058767430923 | rsm10000005699 |  22.004269639323 | 2.08178575714286 |        1 | robwilliams modified phenotype at Thu Oct 28 21:43:28 2010
                                                                                                  |
+-------+-------------+-------------+---------------+---------+-------------------+----------------+------------------+------------------+----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+
2 rows in set (0.328 sec)

ProbeSet

Comment: PLEASE CHANGE TABLE NAME and rework fields carefully. This is a terrible table but it works well (RWW March 2016). It is used in combination with the crucial TRAIT DATA and ANALYSIS pages in GN1 and GN2. It is also used by annotators using the UPDATE INFO AND DATA web form to correct and update annotation. It is used by Arthur to enter new annotation files and metadata for arrays, genes, proteins, metabolites. The main problem with this table is that it is doing too much work. And it is not doing enough because it is huge, but does not track changes. The plan is to migrate to lmdb for that.

Initially (2003) this table contained only Affymetrix ProbeSet data for mouse (U74aV2 initially). Many other array platforms for different species were added. At least four other major categories of molecular assays have been added since about 2010:

1. RNA-seq annotation and sequence data for transcripts using ENSEMBL identifiers or NCBI NM_XXXXX and NR_XXXXX type identifiers

2. Protein and peptide annotation and sequence data (see BXD Liver Proteome data, SRM and SWATH type data) with identifiers such as "abcb10_q9ji39_t311" for SRM data and "LLGNMIVIVLGHHLGKDFTPAAQAA" for SWATH data where the latter is just the peptide fragment that has been quantified. Data first entered in 2015 for work by Rudi Aebersold and colleagues.

3. Metabolite annotation and metadata (see BXD Liver Metabolome data) with identifiers that are usually Mass charge ratios such as "149.0970810_MZ"

4. Epigenomic and methylome data (e.g. Human CANDLE Methylation data with identifiers such as "cg24523000")

It would make good sense to break this table into four or more types of molecular assay metadata or annotation tables) (AssayRNA_Anno, AssayProtein_Anno, AssayMetabolite_Anno, AssayEpigenome_Anno, AssayMetagenome_Anno), since these assays will have many differences in annotation content compared to RNAs (RWW).

Some complex logic is used to update contents of this table when annotators modify and correct the information (for example, updating gene symbols). These features requested by Rob so that annotating one gene symbol in one species would annotate all gene symbols in the same species based on common NCBI GeneID number. For example, changing the gene alias for one ProbeSet.Id will changing the list of aliases in all instances with the same gene symbol.

If the ProbeSet.BlatSeq (or is this ProbSetTargetSeq) is identical between different ProbeSet.Ids then annotation is forced to be the same even if the symbol or geneID is different. This "feature" was implemented when we found many probe sets with identical sequence but different annotations and identifiers.

select count(*) from ProbeSet limit 5;
+----------+
| count(*) |
+----------+
|  4351030 |
+----------+
| Id   | ChipId | Name     | TargetId | Symbol | description                                  | Chr  | Mb        | alias    | GeneId | GenbankId | SNP  | BlatSeq                                                                                                                                                                     |TargetSeq                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            | UniGeneId | Strand_Probe | Strand_Gene | OMIM   | comments | Probe_set_target_region | Probe_set_specificity | Probe_set_BLAT_score | Probe_set_Blat_Mb_start | Probe_set_Blat_Mb_end | Probe_set_strand | Probe_set_Note_by_RW | flag | Symbol_H | description_H | chromosome_H | MB_H | alias_H | GeneId_H | chr_num | name_num | Probe_Target_Description | RefSeq_TranscriptId | Chr_mm8 | Mb_mm8    | Probe_set_Blat_Mb_start_mm8 | Probe_set_Blat_Mb_end_mm8 | HomoloGeneID | Biotype_ENS | ProteinID | ProteinName | Flybase_Id | HMDB_ID | Confidence | ChEBI_ID | ChEMBL_ID | CAS_number | PubChem_ID | ChemSpider_ID | UNII_ID | EC_number | KEGG_ID | Molecular_Weight | Nugowiki_ID | Type | Tissue | PrimaryName | SecondaryNames | PeptideSequence |
+------+--------+----------+----------+--------+----------------------------------------------+------+-----------+----------+--------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+-------------+--------+----------+-------------------------+-----------------------+----------------------+-------------------------+-----------------------+------------------+----------------------+------+----------+---------------+--------------+------+---------+----------+---------+----------+--------------------------+---------------------+---------+-----------+-----------------------------+---------------------------+--------------+-------------+-----------+-------------+------------+---------+------------+----------+-----------+------------+------------+---------------+---------+-----------+---------+------------------+-------------+------+--------+-------------+----------------+-----------------+
| 7282 |      1 | 93288_at | NULL     | Arpc2  | actin related protein 2/3 complex, subunit 2 | 1    | 74.310961 | AK008777 | 76709  | AI835883  |    0 | CCGACTTCCTTAAGGTGCTCAACCGGACTGCTTGCTACTGGATAATCGTGAGGGATTCTCCATTTGGGTTCCATTTTGTACGAGTTTGGCAAATAACCTGCAGAAACGAGCTGTGCTTGCAAGGACTTGATAGTTCCTAATCCTTTTCCAAGCTGTTTGCTTTGCAATATGT | ccgacttccttaaggtgctcaaccgtnnnnnnccnannnnccnagaaaaaagaaatgaaaannnnnnnnnnnnnnnnnnnttcatcccgctaactcttgggaactgaggaggaagcgctgtcgaccgaagnntggactgcttgctactggataatcgtnnnnnnnnnnnnnnnnnnnnnnnnnnnnnnntgagggattctccatttgggttccattttgtacgagtttggcaaataacctgcagaaacgagctgtgcttgcaaggacttgatagttcctaagaattanaanaaaaaaaanaanttccacttgatcaanttaattcccttttatttttcctccctcantccccttccttttccaagctgtttgctttgcaatatgt                                                                                                                                                                                                                                     | Mm.337038 | +            |             | 604224 |          | NULL                    |                  8.45 |                  169 |               74.310961 |              74.31466 | NULL             | NULL                 | 3    | NULL     | NULL          | NULL         | NULL | NULL    | NULL     |       1 |    93288 | NULL                     | XM_129773           | 1       | 74.197594 |                   74.197594 |                 74.201293 | 4187         | NULL        | NULL      | NULL        | NULL       | NULL    |       NULL |     NULL | NULL      | NULL       |       NULL |          NULL | NULL    | NULL      | NULL    |             NULL |        NULL | NULL | NULL   | NULL        | NULL           | NULL            |
+------+--------+----------+----------+--------+----------------------------------------------+------+-----------+----------+--------+-----------+------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+-----------+--------------+-------------+--------+----------+-------------------------+-----------------------+----------------------+-------------------------+-----------------------+------------------+----------------------+------+----------+---------------+--------------+------+---------+----------+---------+----------+--------------------------+---------------------+---------+-----------+-----------------------------+---------------------------+--------------+-------------+-----------+-------------+------------+---------+------------+----------+-----------+------------+------------+---------------+---------+-----------+---------+------------------+-------------+------+--------+-------------+----------------+-----------------+
2 rows in set (0.00 sec)

** ProbeSetXRef (phenotypes/dataset_name.json)

For every probe set (read dataset measuring point):

select * from ProbeSetXRef;
| ProbeSetFreezeId | ProbeSetId | DataId   | Locus_old | LRS_old | pValue_old | mean             | se   | Locus      | LRS               | pValue | additive              | h2   |
|              112 |     123528 | 23439389 | NULL      |    NULL |       NULL |  6.7460707070707 | NULL | rs6239372  |  10.9675593568894 |  0.567 |    0.0448545966228878 | NULL |
|              112 |     123527 | 23439388 | NULL      |    NULL |       NULL | 6.19416161616162 | NULL | rs13476936 |  10.9075670392762 |  0.567 |   -0.0358456732993988 | NULL |

where ProbeSetFreezeId is the dataset (experiment). ProbesetId refers to the probe set information (measuring point). DataId points to the data point. The other values are used for search. It is used in search thus:

SELECT distinct ProbeSet.Name as TNAME,
  ProbeSetXRef.Mean as TMEAN, ProbeSetXRef.LRS as TLRS,
  ProbeSetXRef.PVALUE as TPVALUE, ProbeSet.Chr_num as TCHR_NUM,
  ProbeSet.Mb as TMB, ProbeSet.Symbol as TSYMBOL,
  ProbeSet.name_num as TNAME_NUM
FROM ProbeSetXRef, ProbeSet
WHERE ProbeSet.Id = ProbeSetXRef.ProbeSetId
  and ProbeSetXRef.ProbeSetFreezeId = 112
  ORDER BY ProbeSet.symbol ASC limit 5;
| TNAME      | TMEAN            | TLRS               | TPVALUE               | TCHR_NUM | TMB        | TSYMBOL       | TNAME_NUM |
| 1445618_at | 7.05679797979798 |   13.5417452764616 |                  0.17 |        8 |  75.077895 | NULL          |   1445618 |
| 1452452_at |            7.232 |   30.4944361132252 | 0.0000609756097560421 |       12 |    12.6694 | NULL          |   1452452 |

Probedata - main molecular data. Probesets, metabolome,

Almost all important molecular assay data is in this table including probe set data, RNA-seq data, proteomic data, and metabolomic data. 2.5 billion rows March 2016. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).

Strain

select * from Strain limit 5;
+----+----------+----------+-----------+--------+-------+
| Id | Name     | Name2    | SpeciesId | Symbol | Alias |
+----+----------+----------+-----------+--------+-------+
|  1 | B6D2F1   | B6D2F1   |         1 | NULL   | NULL  |
|  2 | C57BL/6J | C57BL/6J |         1 | B6J    | NULL  |
|  3 | DBA/2J   | DBA/2J   |         1 | D2J    | NULL  |
|  4 | BXD1     | BXD1     |         1 | NULL   | NULL  |
|  5 | BXD2     | BXD2     |         1 | NULL   | NULL  |
+----+----------+----------+-----------+--------+-------+
show indexes from Strain;
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table  | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Strain |          0 | PRIMARY  |            1 | Id          | A         |       14368 |     NULL | NULL   |      | BTREE      |         |               |
| Strain |          0 | Name     |            1 | Name        | A         |       14368 |     NULL | NULL   | YES  | BTREE      |         |               |
| Strain |          0 | Name     |            2 | SpeciesId   | A         |       14368 |     NULL | NULL   |      | BTREE      |         |               |
| Strain |          1 | Symbol   |            1 | Symbol      | A         |       14368 |     NULL | NULL   | YES  | BTREE      |         |               |
+--------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

A typical query may look like

SELECT Strain.Name, ProbeSetData.value, ProbeSetSE.error, ProbeSetData.Id
                    FROM (ProbeSetData, ProbeSetFreeze, Strain, ProbeSet, ProbeSetXRef)
                    left join ProbeSetSE on
                      (ProbeSetSE.DataId = ProbeSetData.Id AND ProbeSetSE.StrainId = ProbeSetData.StrainId)
                    WHERE
                      ProbeSetFreeze.name = 'B139_K_1206_M' AND
                      ProbeSetXRef.ProbeSetId = ProbeSet.Id AND
                      ProbeSetXRef.ProbeSetFreezeId = ProbeSetFreeze.Id AND
                      ProbeSetXRef.DataId = ProbeSetData.Id AND
                      ProbeSetData.StrainId = Strain.Id
                    Order BY Strain.Name

+-------+-------+-------+----------+
| Name  | value | error | Id       |
+-------+-------+-------+----------+
| SM001 |  38.3 |  NULL | 25309550 |
| SM001 |   2.7 |  NULL | 25309520 |
| SM001 |  20.3 |  NULL | 25309507 |
| SM001 | 125.8 |  NULL | 25309511 |
| SM001 |   8.2 |  NULL | 25309534 |
+-------+-------+-------+----------+
5 rows in set (22.28 sec)

ProbeSetFreeze

select * from ProbeSetFreeze limit 5;
+----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+
| Id | ProbeFreezeId | AvgID | Name        | Name2                           | FullName                                    | ShortName               | CreateTime | OrderList | public | confidentiality | AuthorisedUsers | DataScale |
+----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+
|  1 |             3 |     1 | Br_U_0803_M | BXDMicroArray_ProbeSet_August03 | UTHSC Brain mRNA U74Av2 (Aug03) MAS5        | Brain U74Av2 08/03 MAS5 | 2003-08-01 |      NULL |      0 |               0 | NULL            | log2      |
|  2 |            10 |     1 | Br_U_0603_M | BXDMicroArray_ProbeSet_June03   | UTHSC Brain mRNA U74Av2 (Jun03) MAS5        | Brain U74Av2 06/03 MAS5 | 2003-06-01 |      NULL |      0 |               0 | NULL            | log2      |
|  3 |             8 |     1 | Br_U_0303_M | BXDMicroArray_ProbeSet_March03  | UTHSC Brain mRNA U74Av2 (Mar03) MAS5        | Brain U74Av2 03/03 MAS5 | 2003-03-01 |      NULL |      0 |               0 | NULL            | log2      |
|  4 |             5 |     1 | Br_U_0503_M | BXDMicroArray_ProbeSet_May03    | UTHSC Brain mRNA U74Av2 (May03) MAS5        | Brain U74Av2 05/03 MAS5 | 2003-05-01 |      NULL |      0 |               0 | NULL            | log2      |
|  5 |             4 |     1 | HC_U_0303_M | GNFMicroArray_ProbeSet_March03  | GNF Hematopoietic Cells U74Av2 (Mar03) MAS5 | GNF U74Av2 03/03 MAS5   | 2003-03-01 |      NULL |      0 |               0 | NULL            | log2      |
+----+---------------+-------+-------------+---------------------------------+---------------------------------------------+-------------------------+------------+-----------+--------+-----------------+-----------------+-----------+

ProbeSetXRef

select * from ProbeSetXRef limit 5;
+------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+
| ProbeSetFreezeId | ProbeSetId | DataId | Locus_old  | LRS_old            | pValue_old | mean              | se                  | Locus           | LRS                | pValue | additive             | h2   |
+------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+
|                1 |          1 |      1 | 10.095.400 |   13.3971627898894 |      0.163 |  5.48794285714286 | 0.08525787814808819 | rs13480619      | 12.590069931048001 |  0.269 |          -0.28515625 | NULL |
|                1 |          2 |      2 | D15Mit189  | 10.042057464356201 |      0.431 |  9.90165714285714 |  0.0374686634976217 | CEL-17_50896182 |   10.5970737900941 |  0.304 | -0.11678333333333299 | NULL |
|                1 |          3 |      3 | D5Mit139   |   5.43678531742749 |      0.993 |  7.83948571428571 |  0.0457583416912569 | rs13478499      |    6.0970532702754 |  0.988 |    0.112957489878542 | NULL |
|                1 |          4 |      4 | D1Mit511   |   9.87815279480766 |      0.483 | 8.315628571428569 |  0.0470396593931327 | rs6154379       | 11.774867551173099 |  0.286 |   -0.157113725490196 | NULL |
|                1 |          5 |      5 | D16H21S16  | 10.191723834264499 |      0.528 |  9.19345714285714 |  0.0354801718293322 | rs4199265       | 10.923263374016202 |  0.468 |  0.11476470588235299 | NULL |
+------------------+------------+--------+------------+--------------------+------------+-------------------+---------------------+-----------------+--------------------+--------+----------------------+------+

Note that the following unlimited search is very slow:

select max(value) from ProbeSetData;

+------------+
| max(value) |
+------------+
|   26436006 |
+------------+
1 row in set (2 min 16.31 sec)

which is in some form is used in the search page, see [[https://github.com/genenetwork/genenetwork2_diet/blob/master/wqflask/wqflask/do_search.py#L811][the search code]].

*** Comments

I think the ProbeSetData table should be generalized to a 'phenotypes' table with an 'sample_id' column and a 'value' column.

A new table 'samples' will link each sample against an 'experiment', an 'individual' and which in turn can link to a 'strain'.

Experiment is here in a wide sense, GTex can be one - I don't want to use dataset ;)

This means a (slight) reordering:

phenotypes:  (id), sample_id, value
samples:     experiment_id, individual_id
experiments: name, version
individual:  strain_id
strains:     species_id
species:     ...

ProbeData is also interesting, because it has the same structure as ProbeSetData, but only contains microarrays. This tables should be one (when we clear up the cross-referencing) as they both contain phenotype values. Both are large tables.

PublishData is another phenotype table with values only which can be merged into that same table. This data does not require the annotations of probesets(!)

So we have phenotype data in 3 tables with exactly the same layout. There is also TissueProbeSet*, but we'll ignore those for now. I think we should merge these into one and have the sample ref refer to the type of data (probeset, probe, metabolomics, whatever). These are all phenotype values and by having them split into different tables they won't play well when looking for correlations.

ProbeSet contains the metadata on the probes and should (eventually) move into NoSQL. There is plenty redundancy in that table now.

I know it is going to be a pain to reorganize the database, but if we want to use it in the long run we are going to have to simplify it.

ProbeSetFreeze and ProbeFreeze (/dataset/name.json)

GN_SERVER: /dataset/HC_M2_0606_P.json

ProbesetFreeze contains DataSet information, such as name, fullname of datasets, as well as whether they are public and how the data is scaled:

select * from ProbeSetFreeze;
| Id  | ProbeFreezeId | AvgID | Name         | Name2                              | FullName                                      | ShortName                                     | CreateTime | OrderList | public | confidentiality | AuthorisedUsers | DataScale |
| 112 |            30 |     2 | HC_M2_0606_P | Hippocampus_M430_V2_BXD_PDNN_Jun06 | Hippocampus Consortium M430v2 (Jun06) PDNN    | Hippocampus M430v2 BXD 06/06 PDNN             | 2006-06-23 |      NULL |      2 |               0 | NULL            | log2      |

Another table contains a tissue reference and a back reference to the cross type:

select * from ProbeFreeze;
| Id  | ProbeFreezeId | ChipId | TissueId | Name                                        | FullName | ShortName | CreateTime | InbredSetId |
|  30 |            30 |      4 |        9 | Hippocampus Consortium M430v2 Probe (Jun06) |          |           | 2006-07-07 |           1 |

NOTE: these tables can probably be merged into one.

show indexes from ProbeSetFreeze;
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table          | Non_unique | Key_name  | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| ProbeSetFreeze |          0 | PRIMARY   |            1 | Id          | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ProbeSetFreeze |          0 | FullName  |            1 | FullName    | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| ProbeSetFreeze |          0 | Name      |            1 | Name        | A         |           2 |     NULL | NULL   | YES  | BTREE      |         |               |
| ProbeSetFreeze |          1 | NameIndex |            1 | Name2       | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
+----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+

ProbeSetSE

select * from ProbeSetSE limit 5;
+--------+----------+----------+
| DataId | StrainId | error    |
+--------+----------+----------+
|      1 |        1 | 0.681091 |
|      1 |        2 | 0.361151 |
|      1 |        3 | 0.364342 |
|      1 |        4 | 0.827588 |
|      1 |        5 | 0.303492 |
+--------+----------+----------+

More information

For the other tables you may check the GN2/doc/database.org document (that was the starting point for this document).

(made with skribilo)