From 55ba3554d10a61ab038e4ab3c85ec2e1684503fc Mon Sep 17 00:00:00 2001
From: Pjotr Prins
Date: Sat, 11 Nov 2023 10:21:36 +0100
Subject: DB docs

---
 doc/database.org | 35 +++++++++++++++++------------------
 1 file changed, 17 insertions(+), 18 deletions(-)

(limited to 'doc')

diff --git a/doc/database.org b/doc/database.org
index 315ee2b6..d408f5ca 100644
--- a/doc/database.org
+++ b/doc/database.org
@@ -17,62 +17,61 @@ number of the BXD's and the Human liver dataset (GSE9588).
 
 * GeneNetwork database
 
-** Estimated table sizes
-
+** Estimated table sizes with metadata comment
 
 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 |
 +-------------------------+------------+
-| ProbeSetData            |   59358.80 |
+| ProbeSetData            |   59358.80 | DataStrainValues    <- id, strain, value
 | SnpAll                  |   15484.67 |
-| ProbeData               |   22405.44 |
+| ProbeData               |   22405.44 | mRNAStrainValues    <- individual probe data (mRNA) [GN1,GN3]
 | SnpPattern              |    9177.05 |
-| ProbeSetSE              |   14551.02 |
+| ProbeSetSE              |   14551.02 | ProbeSetStrainError <- SE values aligns with ProbeSetData
 | QuickSearch             |    5972.86 |
-| ProbeSetXRef            |    4532.89 |
+| ProbeSetXRef            |    4532.89 | PrecomputeLRS       <- precomputed LRS values
 | LCorrRamin3             |   18506.53 |
-| ProbeSE                 |    6263.83 |
-| ProbeSet                |    2880.21 |
-| Probe                   |    2150.30 |
+| ProbeSE                 |    6263.83 | DataStrainError     <- trait SE [GN3]
+| ProbeSet                |    2880.21 | ProbeSetInfo        <- trait, e.g. 100001_at
+| Probe                   |    2150.30 | ProbeInfo           <- trait info incl sequence
 | GenoData                |    3291.91 |
 | CeleraINFO_mm6          |     989.80 |
 | pubmedsearch            |    1032.50 |
-| ProbeXRef               |     743.38 |
+| ProbeXRef               |     743.38 | ProbeFreezeDataIDs  <- link ProbeFreezeId,ProbeId with DataId
 | GeneRIF_BASIC           |     448.54 |
 | BXDSnpPosition          |     224.44 |
 | EnsemblProbe            |     133.66 |
 | EnsemblProbeLocation    |     105.49 |
 | Genbank                 |      37.71 |
-| TissueProbeSetData      |      74.42 |
+| TissueProbeSetData      |      74.42 | <- link Id,TissueID with value
 | AccessLog               |      42.38 |
 | GeneList                |      34.11 |
 | Geno                    |      33.90 |
 | MachineAccessLog        |      28.34 |
 | IndelAll                |      22.42 |
-| PublishData             |      22.54 |
+| PublishData             |      22.54 | PublishStrainValues  <- Id,StrainId,value
 | TissueProbeSetXRef      |      14.73 |
 | ProbeH2                 |      13.26 |
 | GenoXRef                |      22.83 |
 | TempData                |       8.35 |
 | GeneList_rn3            |       5.54 |
 | GORef                   |       4.97 |
-| Phenotype               |       6.50 |
+| Phenotype               |       6.50 | PhenotypeMeta        <- "Hippocampus weight"
 | temporary               |       3.59 |
 | InfoFiles               |       3.32 |
 | Publication             |       3.42 |
 | Homologene              |       5.69 |
-| Datasets                |       2.31 |
+| Datasets                |       2.31 | DatasetMeta          <- "Data generated by..."
 | GeneList_rn33           |       2.61 |
 | PublishSE               |       4.71 |
 | GeneRIF                 |       2.18 |
 | Vlookup                 |       1.87 |
 | H2                      |       2.18 |
 | PublishXRef             |       2.18 |
-| NStrain                 |       4.80 |
+| NStrain                 |       4.80 | StrainCountDataId    <- Strains used in dataset
 | IndelXRef               |       2.91 |
-| Strain                  |       1.07 |
+| Strain                  |       1.07 | StrainNames          <- with species ID and alias
 | GeneMap_cuiyan          |       0.51 |
 | user_collection         |       0.30 |
 | CaseAttributeXRef       |       0.44 |
@@ -80,7 +79,7 @@ select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size i
 | GeneIDXRef              |       0.77 |
 | Docs                    |       0.17 |
 | News                    |       0.17 |
-| ProbeSetFreeze          |       0.22 |
+| ProbeSetFreeze          |       0.22 | -> dataset, e.g. "Hippocampus_M430_V2_BXD_PDNN_Jun06"
 | GeneRIFXRef             |       0.24 |
 | Sample                  |       0.06 |
 | login                   |       0.06 |
@@ -96,7 +95,7 @@ select table_name,round(((data_length + index_length) / 1024 / 1024), 2) `Size i
 | GeneChip                |       0.01 |
 | GeneCategory            |       0.01 |
 | SampleXRef              |       0.01 |
-| InbredSet               |       0.01 |
+| InbredSet               |       0.01 | InbredSetMeta -> Id,SpeciesId,FullName
 | SnpAllele_to_be_deleted |       0.00 |
 | Organizations           |       0.01 |
 | PublishFreeze           |       0.00 |
-- 
cgit v1.2.3