WebQTL
 
   |    Home    |    Search    |    Help    |    News    |    References    |    Policies    |    Links    |    Welcome! Login   

Description of Schema

This page provides a partial description of the database tables used by The GeneNetwork. This schema is dynamically updated from the MySQL database. Short text annotation at the top of many tables is entered manually. [Implemented by Hongqiang Li, Aug 2005.]

Suggested Conventions for Table Names: Please start with an upper case character for each distinct word used to name the table, for example "AccessLog", "AvgMethod", "ProbeFreeze". A mix of upper case and lower case is fine. In general, avoid unscore. However, use of the underscore character is apppropriate for particular freezes or source of data in a table, for example, "GeneList_hg17" and "CeleraInfo_mm6".

Suggested Conventions for Field Names: Use of lower case is preferred when possible. Separate words in a field name with underscore: examples: ip_address, allele_B6. Please try to make field names self-explanatory to a bioinformatics expert. Please annotate and describe the field name when you make a new table or add a new field. Avoid cryptic suffixes and prefixes to field names.

Last edited March 21, 2016


Tables

AccessLog
AvgMethod
BXDSnpPosition
CaseAttribute
CaseAttributeXRef
CaseAttributeXRefNew
CeleraINFO_mm6
Chr_Length
DatasetMapInvestigator
DatasetStatus
Dataset_mbat
Datasets
Docs
EnsemblChip
EnsemblProbe
EnsemblProbeLocation
GORef
Genbank
GeneCategory
GeneChip
GeneChipEnsemblXRef
GeneIDXRef
GeneList
GeneList_rn3
GeneList_rn33
GeneMap_cuiyan
GeneRIF
GeneRIFXRef
GeneRIF_BASIC
Geno
GenoCode
GenoData
GenoFile
GenoFreeze
GenoSE
GenoXRef
H2
Homologene
InbredSet
IndelAll
IndelXRef
InfoFiles
InfoFilesUser_md5
Investigators
LCorrRamin3
MachineAccessLog
MappingMethod
NStrain
News
Organizations
Phenotype
Probe
ProbeData
ProbeFreeze
ProbeH2
ProbeSE
ProbeSet
ProbeSetData
ProbeSetFreeze
ProbeSetSE
ProbeSetXRef
ProbeXRef
Publication
Publication_Test
PublishData
PublishFreeze
PublishSE
PublishXRef
RatSnpPattern
Sample
SampleXRef
SnpAll
SnpAllRat
SnpAllele_to_be_deleted
SnpPattern
SnpSource
Species
Strain
StrainXRef
Tissue
TissueProbeFreeze
TissueProbeSetData
TissueProbeSetFreeze
TissueProbeSetXRef
TraitMetadata
UserPrivilege
Vlookup
login
pubmedsearch
role
roles_users
temporary
user
user_collection
user_openids

AccessLog

Comment:
This table tracks access time and IP addresses. Used for logging in registered users and tracking cookies.
Created by Jintao Wang, 2004.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(10) unsigned NO PRI   auto_increment   internal identifier
accesstime datetime NO   0000-00-00 00:00:00     time stamp of login
ip_address char(20) NO   0.0.0.0     Internet protocol address


AvgMethod

Comment:
Should be deprecated. Used by ProbeSetFreeze (March 2016 by RWW)

A table that simply lists the transformations used to generate microarray probe or probe set data. For example, for the Affymetrix platform MAS5, PDNN, RMA, are common transformation methods. For Illumina, the transform methods included Rank, RankInv, LOESS, LOESS_NB (no background). A single raw data set can be transformed in several ways, giving rise to a family of related data sets. This table was originally set up by Jintao Wang. We should confirm that this table is still used. Possibly a development vestige. "mlratio" was added by Evan Williams June 2008 to describe the Agilent "mean log" method used by Lusis and colleagues.
The table is referenced in ProbeSetFreeze, so it is still in use on Dec 14,2006. 6 records on Dec14, 2006. ID12065
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   internal identifier
AvgMethodId int(5) YES          
Name char(30) NO         name or abbreviation of the method used to generate the probe set consensus estimates of transcript abundance
Normalization varchar(30) YES          


BXDSnpPosition

Comment:
PLEASE CORRECT TALBE AND FIELD NAMES. A table created by Hongqiang (October 2007) to be used to display SNPs on the interval maps for all crosses of mice including BXD, AXB, AKXD and B6D2F2 mouse crosses. Field are not well annotated. All data in this table is taken from the main mouse SNP table. This table should be refreshed every day or week. Hongqiang would need to write a script to do this. Not done yet (Nov 13, 2007). Reviewed by Kev and Rob, July 2008. Evan may have used this table to get SNPs to show up on the BHF2 interval maps. Still needs to be renamed. Needs to handle CXB RI set and LXS RI set.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
Chr char(2) YES MUL       Chromosome
StrainId1 int(11) YES         Not sure, Strain identifier (probably C57BL/6J)
StrainId2 int(11) YES         Not sure, Strain identifier (probably DBA/2J
Mb double YES         SNP position in megabases rather than basepairs from mm8. Is this really mm8. Should be mm9 up to Mar 2016.
Mb_2016 double YES          


CaseAttribute

Comment:
This is a new table created by Zach Sloan and Lei Yan in 2014-2015 to allow us to add cofactors and metadata that typically display in the Trait Data and Analysis page along side of the phenotype values for each case/sample. For example, if you review any of the human data sets where GROUP = "Brain, Development: Normal Gene Expression (Yale/Sestan)" you will find CaseAttribute data for the expression data sets such as Sex, PostMortem Interval (PMI), Age, Ethnicity, Tissue, pH.

This table may not really be necessary, since all data here could also be put into the Phenotype data table. But then we need code that would allow the user (or programmer or annotator) to decide what cofactors to show in the Trait Data and Analysis page.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Name varchar(30) NO         The name of the cofactor shown in the Trait Data and Analysis page (e.g. "Ethn.", "pH", "Sex")


CaseAttributeXRef

Comment:
Table used for handle database metadata. One to one mapping for ProbeSetFreeze etc.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
ProbeSetFreezeId smallint(5) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
CaseAttributeId smallint(5) NO PRI 0      
Value varchar(100) NO          


CaseAttributeXRefNew

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
InbredSetId smallint(5) unsigned NO PRI 0      
StrainId int(8) unsigned NO PRI 0      
CaseAttributeId smallint(5) NO PRI 0      
Value varchar(100) NO          


CeleraINFO_mm6

Comment:
PLEASE DELETE: DEPRECATED: use the Snp table instead.

CeleraINFO_mm6 provides information on single nucelotide polymorphisms (SNPs) for five strains of mice, C57BL/6J, DBA/2J, A/J, 129S1/SvImJ, and 129X1/SvJ, obtained from Celera Genomics by Alex Williams and Chris Vincent, summer 2003.

"mm6" refers to the megabase positions taken from the sixth build of Mus musculus as presented in UCSC Genome Browser (also known as NCBI Mouse Build 34 that was current from March 2005 through approximately March 2006).

mm6 data were generated by Yanhua Qu by BLAT sequence alignment of ~300 bp of sequence around each SNP. This table was generated by Robert Crowell and Yanhua Qu, June 2005, for use on WebQTL physical maps.

FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment    
SNPID char(14) NO MUL       from Celera Genomics
chromosome char(3) YES MUL       mouse chromosome from BLAT alignment
MB_UCSC double YES MUL       mouse mm6 position in megabases from the UCSC Genome Brower mm6 BLAT analysis (NCBI Build 34)
MB_celera double YES         megabase position given by Celera
allele_B6 char(4) YES         the allele of C57BL/6J
allele_D2 char(4) YES         the allele of DBA/2J
allele_AJ char(4) YES         the allele of A/J
B6_D2 char(1) YES         a flag (0 or 1) that denotes if B6 and D2 have ths same allele (0) or a different allele (1) that is used in generating SNP tracks on BXD physical maps
B6_AJ char(1) YES         same as above but used for the AXB/BXA cross
D2_AJ char(1) YES         same as above
MB_UCSC_OLD double YES         the mm5 position values
allele_S1 char(4) YES          
allele_X1 char(4) YES          
flanking5 char(100) YES          
flanking3 char(100) YES          


Chr_Length

Comment:
This table provides the approximate length of a chromosome in megabases taken from the most recent public genome assemblies of several species. As of Aug. 2005, this table contains lengths for mouse, rat, and Arabidopsis chromosomes. No data for Barley since there is not physical assembly yet. We are usually missing the Y chromosome and the mitochondrial genome (Chr Y and Chr M, respectively).

Some of these fields should be semantically registered (SpeciesId, Chromosome_name).
Created by Jintao Wang, 2004.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Name char(3) NO PRI       At most 3 letter name of the chromosome ID1206
SpeciesId smallint(5) unsigned NO PRI 0   Species.Id the identifier for the species. This may be the official NCBI species code or just an internal GN code. Check
OrderId smallint(5) unsigned NO     auto_increment   the numerical order of chromosomes for purposes of generating whole genome maps (Chr X is 20 in mouse)
Length int(10) unsigned NO   0     the length of the chromosome in megabase units
Length_2016 int(10) unsigned NO   0      
Length_mm8 int(10) unsigned YES          


DatasetMapInvestigator

Comment:
Dataset owner or creator. Not sure how this is used? Is it used to for data access or password control? Check with Arthur Centeno or Lei Yan if this is used to define private ProbeSetFreeze (RWW Mar 2016). Probably created by Arthur Centeno 2014-2015. Why is the word "Map" in the table name?
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id mediumint(9) NO PRI   auto_increment    
DatasetId int(6) NO          
InvestigatorId int(6) NO          


DatasetStatus

Comment:
This is the private versus public flag for a large molecular phenotype data sets (aka prior to 2016 as a ProbeSetFreeze) (RWW March 2016). This table probably created by Arthur Centeno in 2014-2015.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DatasetStatusId int(5) NO PRI        
DatasetStatusName varchar(20) YES          


Dataset_mbat

Comment:
DEPRECATED. What is mbat? Is this associated with 8 tissues and phenotypes of BXD only. Hippocampus, brain, cerebellum, eye, neocortex, NAc, PFC, striatum and phenotypes. All before 2008. (RWW Mar 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
switch int(1) YES          
species varchar(255) YES          
cross varchar(255) YES          
tissue varchar(255) YES          
database varchar(255) YES          
database_LongName varchar(255) YES          


Datasets

Comment:
DEPRECATED in favor of "InfoFiles". Check if this is still needed (March 2016). These are the annotation/metadata fields used to describe molecular phenotypes only (aka ProbeSetFreezes). This table designed by Arthur Centeno and used to generate and modify "INFO" tables. Created in 2014-2015.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DatasetId int(6) NO PRI   auto_increment    
DatasetName varchar(255) YES          
GeoSeries varchar(30) YES          
PublicationTitle longtext YES          
Summary longtext YES          
ExperimentDesign longtext YES          
AboutCases longtext YES          
AboutTissue longtext YES          
AboutPlatform longtext YES          
AboutDataProcessing longtext YES          
Contributors longtext YES          
Citation longtext YES          
Acknowledgment longtext YES          
Notes longtext YES          
InvestigatorId int(5) NO          
DatasetStatusId int(5) NO          


Docs

Comment:
Rob suspects that this table is used to track images, PDF, etc, that are uploaded into GN documentation (e.g., References and Glossary). Check with Arthur or Lei. (RWW March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
entry varchar(255) NO          
title varchar(255) NO          
content text NO          


EnsemblChip

Comment:
Deprecated and not a function in GN2. For that matter, not even a function of most array data in GN1. One of several tables created by Hongqiang Li to be used with Probe Tool functions for M430 Affymetrix array to show location of probes only in mouse. All locations are equivalent to mm8 in mouse. Xusheng Wang points out that this could be done from UCSC browser.

This table should also be updated at some point.


Probe locations were obtained from Ensembl ftp://ftp.ensembl.org/pub/current_mus_musculus/data/mysql/mus_musculus_core_43 by Hongqiang Li. We made use of text files and MySQL tables called:

oligo_feature.txt.table.gz (25774 KB file of 3/1/07 1:53:00 AM)
oligo_probe.txt.table.gz (24411 KB 3/1/07 1:54:00 AM)
seq_region.txt.table.gz (383 KB, 3/1/07 1:59:00 AM)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(11) NO          
ProbeSetSize int(11) NO          
Name varchar(40) NO          
Type enum('AFFY','OLIGO') YES          


EnsemblProbe

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(11) NO MUL        
ChipId int(11) NO          
ProbeSet varchar(40) YES          
Name varchar(40) YES MUL        
length int(11) NO          


EnsemblProbeLocation

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Chr char(2) NO          
Start int(11) NO          
Start_2016 int(11) YES          
End int(11) NO          
End_2016 int(11) YES          
Strand int(11) NO          
MisMataches int(11) YES          
ProbeId int(11) NO MUL        


GORef

Comment:
Gene Ontology identifiers linked to gene symbols. Only properly implemented for mouse. Note that this table does not have a GORef.species field. GN1 code does return hits even for Drosophila but symbols have mouse format. Check how this happens. (RWW March 2016)

Important and should probably be updated periodically. Used for GO searches in GN1 and GN2. E.g. GO:0045202 searches for synapse-associated genes listed in the Gene Ontology using information in this table.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
goterm varchar(255) YES          
genes text YES          


Genbank

Comment:
DEPRECATED. Apparently only used by the "Probe Tools". This table contains a complete or truncated copy of GenBank sequence data associated with particular Affymetrix Probe sets. When a GenBank sequence entry was long, we took only the most terminal 1000 nt under the assumption that this was the 3' end of the sequence. This assumption will often be incorrect. This table is used primarily in association with Affymetrix Probe sets generated using GenBank sequence. The Probe Tools table in GN is able to BLAT the GenBank sequences provided in this table.
Created by Yanhua Qu, August 2005. Deprecated by RWW March 2016.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id varchar(20) NO PRI       conventional GenBank identifier of the sequence
Sequence text YES         up to 1000 nucleotides of sequence
SpeciesId smallint(5) unsigned NO   0     species identifier used by GenBank and NCBI


GeneCategory

Comment:
DEPRECATED. Used by GeneWiki notes to classify notes. This table has never been used.
If this table is deleted then please delete all checkboxes associated with GeneWiki data entry. RWW March 2016.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Name char(255) NO MUL        


GeneChip

Comment:
This table lists the array platforms that are used by the GeneNetwork. For example Affymetrix U74Av2, M430A, RAE230A. etc.
The name 'GeneChip' is much too specific. 'Platform' would be better term.

A new record can be inserted only if the relevant record in Species table is in place. ID1206

Please use the following conventions for naming array platform:

MG_U74AV2 is a good form but
Affy_U74Av2 would be better

Start with a four letter vendor code (Upper case lower case underscore)
"Affy_" for Affymetrix
"Illu_" for Illumina
"Agil_"

then use the name of the array given by the vendor
for example: U74AV2, MOE430A, MOE430B, MOE430V2, RAE230A, G4121A, MOUSE6.0, MOUSE6.1, MOUSE6.2
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   internal GN identifier of the array platform
GeneChipId int(5) YES          
GeneChipName varchar(200) YES          
Name char(30) NO         array platform long identifier or name
GeoPlatform char(15) YES          
Title varchar(100) YES          
SpeciesId int(5) YES   1   Species:Id species for which this array was designed to work best (U74Av2 for mouse, RAE230A for rat)
GO_tree_value varchar(50) YES          


GeneChipEnsemblXRef

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
GeneChipId int(11) NO          
EnsemblChipId int(11) NO          


GeneIDXRef

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
mouse int(11) NO MUL 0      
rat int(11) NO MUL 0      
human int(11) NO MUL 0      


GeneList

Comment:
Out of date in 2016. May be used by chromosome QTL maps and by SNP Tables. GN would need these data for multiple species. This is apparently meant as a generic table for any species. Should be updated periodically from BioMart or UCSC Genome Browser.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
SpeciesId int(5) unsigned NO PRI 1      
Id int(10) unsigned NO PRI   auto_increment    
GeneSymbol varchar(15) YES MUL        
GeneDescription text YES          
Chromosome varchar(10) YES          
TxStart double YES         We currently think that this should be the start of the 5' UTR but need to confirm
TxStart_2016 double YES          
TxEnd double YES         We currently think that this should be the end of the 3' UTR but need to confirm
TxEnd_2016 double YES          
Strand char(1) YES          
GeneID varchar(10) YES         This is the GeneID which used to be called the LocusLinkID
NM_ID varchar(15) YES         This is the messenger RNA reference id number (nucleotide Message)
kgID varchar(10) YES          
GenBankID varchar(15) YES          
UnigenID varchar(15) YES         Unigene IDs, often multiple
ProteinID varchar(15) YES          
AlignID varchar(10) YES          
exonCount int(7) NO   0      
exonStarts text YES         This is a concatenated list of exon starts, separated by comma
exonEnds text YES         This is a concatenated list of exon starts, separated by comma
cdsStart double YES         This is the start of the coding sequence (cds = protein coding part of mRNA)
cdsStart_2016 double YES          
cdsEnd double YES         This is the end of the coding sequence (cds = protein coding part of mRNA)
cdsEnd_2016 double YES          
TxStart_mm8 double YES          
TxEnd_mm8 double YES          
Strand_mm8 char(1) YES          
exonCount_mm8 int(7) YES          
exonStarts_mm8 text YES          
exonEnds_mm8 text YES          
cdsStart_mm8 double YES          
cdsEnd_mm8 double YES          
Chromosome_mm8 varchar(10) YES          
Info_mm9 text YES          
RGD_ID int(10) YES          


GeneList_rn3

Comment:
This table contains information on recognized genes in Rattus norvegicus (rn) from the rat build 3 (RGSC v3.4 of November 2004; see http://www.ncbi.nlm.nih.gov/genome/guide/rat/release_notes.html). Data are used by the Interval Analyst table in WebQTL. Data originally taken from UCSC ftp site July 2005. Annotation by Rob Williams and Senhua Yu, Aug. 2005. Created by Evan Williams, July 2005.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(10) unsigned NO PRI   auto_increment    
ProbeSet varchar(16) YES          
geneSymbol varchar(15) YES MUL       official gene symbol
geneID varchar(10) YES         Entrez gene ID
kgID varchar(10) YES          
geneDescription text YES         official gene description
genBankID varchar(15) YES         GenBank identifier
unigenID varchar(15) YES         Unigene identifier (Build 144)
score int(4) YES          
qStart int(3) YES          
qEnd int(3) YES          
qSize int(3) YES          
identity varchar(7) YES          
chromosome varchar(8) YES MUL        
strand char(1) YES          
txStart double YES          
txEnd double YES          
txSize double YES          
span int(7) YES          
specificity double YES         Check status of this field. Really a probe set field, not a gene field.
sequence text YES         Check status of this field. Really a probe set field, not a gene field.
flag int(1) YES         Check status of this field. Really a probe set field, not a gene field.


GeneList_rn33

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(10) unsigned NO PRI   auto_increment    
geneSymbol varchar(15) YES MUL        
txStart double YES          
txEnd double YES          
exonCount int(7) YES          
NM_ID varchar(15) YES          
chromosome varchar(8) YES MUL        
strand char(1) YES          
cdsStart double YES          
cdsEnd double YES          
exonStarts text YES          
exonEnds text YES          
kgID varchar(10) YES          


GeneMap_cuiyan

Comment:
Deprecated. This is a table used to link from GN1 single chromosome maps to Yan Cui's polyMiRTS database of microRNA binding sites linked to SNPs.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id bigint(20) NO PRI   auto_increment    
TranscriptID varchar(255) YES          
GeneID varchar(255) YES          
Symbol varchar(255) YES          


GeneRIF

Comment:
This is to be more than just GeneRIF but also includes GeneWIKI fields used to allow users to enter new GeneWiki data sets. Should be renamed GeneWiki_Entry_Data
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI 0      
versionId smallint(5) unsigned NO PRI 0      
symbol varchar(30) NO MUL       Official gene symbol taken from NCBI
PubMed_ID varchar(255) YES          
SpeciesId smallint(5) unsigned NO   1      
comment text YES          
email varchar(50) YES          
createtime datetime NO MUL 0000-00-00 00:00:00      
user_ip varchar(20) YES          
weburl varchar(255) YES          
initial varchar(10) YES          
display tinyint(4) YES MUL 1      
reason varchar(100) NO          


GeneRIFXRef

Comment:
Gene RIF link to corresponding NCBI PubMed Reference ID. (RWW March 2016). Odd that there are three primary keys. (RWW March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
GeneRIFId int(10) unsigned NO PRI 0      
versionId smallint(5) unsigned NO PRI 0      
GeneCategoryId smallint(5) unsigned NO PRI 0      


GeneRIF_BASIC

Comment:
Gene Reference into Function data table from NCBI. Data are updated each night from NCBI and used to perform RIF queries in GN1 and GN2. (RWW March 2016). Probably should be called "GeneRIF_Data_NCBI"
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
SpeciesId smallint(5) unsigned NO PRI 1      
GeneId int(10) unsigned NO PRI 0      
symbol varchar(255) NO MUL        
PubMed_ID int(10) unsigned NO PRI 0      
createtime datetime NO PRI 0000-00-00 00:00:00      
comment text YES MUL        
VersionId tinyint(3) unsigned NO PRI   auto_increment    


Geno

Comment:
Genotype marker information, not the actual genotypes. Should probably be renamed "Marker_Information" or "Genotype_Marker_Info". If genotype data is held in MySQL, then this table is used for updating genotypes and for the production of a new ".GENO" file after an update. Currently, the update feature i used almost exclusively for BXD mouse cohort. Contains 400,000 rows each with sequence data around markers (usually SNPs, but some microsatellites and other weird variants). (RWW March 2016)

This table contains descriptions of markers, the same ones that are used in the corresponding .geno file. This table is exactly the same as Geno_0609, so one of them might be redundant and also has one-to-one relationship with Geno2 table. For some information there is more than one entry (marker name, position) which may lead to inconsistent state. The marker fields look like references to single nucleotide poymorphisms within the marker, but this hasn't been confirmed.

For a few groups, this table can be updated by authorized users using an interface at www.genenetwork.org/manager.html (Update Genotype functions) (comment updated March 14, 2016 by RWW)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment   Primary key, used in Geno2, GenoXRef
SpeciesId smallint(5) unsigned NO MUL 1      
Name varchar(40) NO         Marker name, or other ID used in .geno file
Marker_Name varchar(40) YES          
Chr char(3) YES       Chr_Length.Name  
Mb double YES         This may be the genome-wide megabase values that is cumulative. Chr 2 starts at about 200 Mb. (Double check)
Mb_2016 double YES          
Sequence text YES         The sequence of the marker, if known. Usually 100 nt on each side of the SNP with the SNP is square brackets for BLAT analysis. gctcctaattgctgagatttctctccagctc
TGCCTCCCTTTCACACTCTCCTGCCCGTCCC
AATCAGAACATTAGAGCTGATAAGATTTACT
TATGGAC[CT]GATCTAAAATAGAAGTCCTT
TGGAGAACTTTGAGAGCTTTTCCAAGAAGTA
AAGTCGGTTAGTTGCTTTTCCAAAGAAATAA
AGTTAGTGATTCTCCACA
Source varchar(40) YES         The provider of the information, could be institute.
chr_num smallint(5) unsigned YES       Chr_Length.OrderId This is a number used to order chromosomes, thus Chr X in mouse = Chr 20, and Chr Y = 21
Source2 varchar(40) YES         The provider of the information, could be institute, often the same as Source.
Comments varchar(255) YES         Comment
used_by_geno_file varchar(40) YES         This is a flag that determines if a marker is used for mapping. Can be set by authorized users using the "Update Genotype" function. May be redundant with field in GenoXRef.
Mb_mm8 double YES         Megabase position (not bp). Should be deleted or updated to mm10 (March2016)
Chr_mm8 char(3) YES         Chromosome name. Should be deleted or updated to mm10 (March2016)


GenoCode

Comment:
Only has one row as of March 2016 and used exclusively for BXD or B6xD2 crosses.
May be used for Haplotype Analyst display function in QTL maps. May also be used to determine polarity of effect size (B allele defined as -1 and D allele defined as 1).

Inbred Set 1, AlleleType values mat, pat, het, unk, and AlleleSymbol B, D, H, U, and DatabaseValue -1, 1, 0 or NULL

"InbredSetId" should be renamed "GroupID" or "CohortID"
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
InbredSetId smallint(5) unsigned NO PRI 1      
AlleleType char(3) NO PRI        
AlleleSymbol char(2) NO PRI        
DatabaseValue smallint(5) YES          


GenoData

Comment:
This is the table that actually contains genotypes for individuals. Could be renamed "Genotype_Data". The GenoData.value will need to be updated to allow a wider range of values and probabilities to accommodate complex crosses and cohorts. Right now the GenoData.value is labeled as a "float" but most of the code in GN expects to see values of -1, 0, or 1.

As of March 2016 (140 million rows where each row is genotype from individual X at marker Y)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI 0     Primary key identifier
StrainId smallint(5) unsigned NO PRI 0     This field should be renamed SampleID.
value float NO         The actual genotype of the case. Usually -1, 0, 1. U is not allowed yet. No blank allowed.


GenoFile

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
server varchar(100) NO          
InbredSetID int(11) NO          
location varchar(255) NO          
title varchar(255) NO          
sort int(3) YES          


GenoFreeze

Comment:
Population or cohort description. Links the population or cohort data described here with the genotype information. The entry in this table must be present to enter the genotype data in the GenXRef and Data tables.

As of March 2016 has about 26 rows for mouse, rat, Drosopholia, potatoe, human, etc.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   Primary key referenced in GenoXRef.GenoFreezeId.
Name varchar(100) NO         Genotype file for the cohort name.
FullName varchar(100) NO         Extended genotype file name for the cohort.
ShortName varchar(100) NO         Shortened genotype name.
CreateTime date NO   2001-01-01     now()
public tinyint(4) NO   0     1 = beta data, 2 = available in GeneNetwork.
InbredSetId smallint(5) unsigned YES   1   InbredSet.Id links to the mapping population.
confidentiality tinyint(3) unsigned YES   0     0 = not confidential, 1 = confidential.
AuthorisedUsers varchar(100) YES          


GenoSE

Comment:
DELETE THIS TABLE. This is to be a stupid table that can be deleted (Rob W, March 2016). Likely to have been added as a parallel table similar to those used from phenotypes and quantitative traits.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
error float NO          


GenoXRef

Comment:
The table is used to establish links to other tables that contain genotype data. contains the information on which markers should be used for QTL mapping
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
GenoFreezeId smallint(5) unsigned NO PRI 0     The GenoFreezeID is the number that references a single coherent genotype file. For example the first BXDgeno file is 0001.
GenoId int(10) unsigned NO PRI 0     GenoID is an identifier for a specific marker (SNP or other)
DataId int(10) unsigned NO UNI 0     DataID is an identifier for a specific vector of genotypes
cM double YES   0     cM is the centiMorgan location of the marker
Used_for_mapping char(1) YES   N     Flag used to decide if a marker is used for mapping or not


H2

Comment:
Deprecated or delete this table (RWW March 2016). Compare to "Probe h2" used in Probe Table. Plain "H2" table has 60,000. This could be at the probe set level whereas "Probe h2" is for the individual probes on the probe set. (Danny Arends = DA March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned NO PRI 0      
ICH2 double NO   0      
H2SE double NO   0     heritability standard error ??
HPH2 double NO   0      


Homologene

Comment:
Coupling genes to other genes using homology between them. (DA March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
HomologeneId int(11) YES          
GeneId int(11) YES MUL        
TaxonomyId int(11) YES          


InbredSet

Comment:
Important table that is used to select appropriate analytic and mapping methods. We should change the name of this table to "Population_Description" or "Sample_Description" (RWW March 2016)

"orderid" is used to set the order of in the pull-down menu.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
InbredSetId int(5) YES          
InbredSetName varchar(100) YES          
Name char(30) NO          
SpeciesId smallint(5) unsigned YES   1      
FullName varchar(100) YES          
public tinyint(3) unsigned YES   2      
MappingMethodId char(50) YES   1     prefered mapping method to use ??
GeneticType varchar(255) YES         e.g. intercross
Family varchar(100) YES          
FamilyOrder int(5) YES          
MenuOrderId double NO          
InbredSetCode varchar(5) YES          


IndelAll

Comment:
Information about indels (comaprable to the SNP data) used by the Mouse SNP browser GN1 only at this time. Only B6 versus D2 mouse indels have been entered so far. Has 140,000 rows but should have at least twice as many. Probably entered at an early stage of sequence analysis (circa 2010) (DA March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment    
SpeciesId smallint(5) unsigned YES MUL 1      
SourceId smallint(5) unsigned YES          
Name char(30) YES MUL        
Chromosome char(2) YES          
Mb_start double YES          
Mb_start_2016 double YES          
Strand char(1) YES          
Type char(15) YES          
Mb_end double YES          
Mb_end_2016 double YES          
Size double YES          
InDelSequence char(30) YES          


IndelXRef

Comment:
Just for C57BL/6J and DBA/2J indels with 140,000 rows
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
IndelId int(10) unsigned NO PRI 0      
StrainId1 smallint(5) unsigned NO PRI 0      
StrainId2 smallint(5) unsigned YES          


InfoFiles

Comment:
INFO file metadata. INFO files are currently limited to molecular data sets (mRNA, protein, metabolomes). Majority of mRNA transcriptome data sets. This table set up by Arthur 2014-2015. Compare to "Datasets" which appears to be a subset of this table.

"InfoFiles" has 700 rows whereas "Datasets" has only 230 rows (RWW March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DatasetId int(5) YES          
SpeciesId int(5) YES          
TissueId int(5) YES          
InbredSetId int(5) YES          
GeneChipId int(5) YES          
AvgMethodId int(5) YES          
InfoFileTitle longtext YES          
Specifics longtext YES          
Status varchar(255) YES          
Title varchar(255) YES          
Organism varchar(255) YES          
Experiment_Type longtext YES          
Summary longtext YES          
Overall_Design longtext YES          
Contributor longtext YES          
Citation longtext YES          
Submission_Date varchar(255) YES          
Contact_Name varchar(255) YES          
Emails varchar(255) YES          
Phone varchar(255) YES          
URL varchar(255) YES          
Organization_Name varchar(255) YES          
Department varchar(255) YES          
Laboratory varchar(255) YES          
Street varchar(255) YES          
City varchar(255) YES          
State varchar(255) YES          
ZIP varchar(255) YES          
Country varchar(255) YES          
Platforms varchar(255) YES          
Samples longtext YES          
Species varchar(255) YES          
Normalization varchar(255) YES          
InbredSet varchar(255) YES          
InfoPageName varchar(255) NO UNI        
DB_Name varchar(255) YES          
Organism_Id varchar(60) YES          
InfoPageTitle varchar(255) YES          
GN_AccesionId int(4) YES UNI        
Tissue varchar(60) YES          
AuthorizedUsers varchar(100) YES          
About_Cases longtext YES          
About_Tissue longtext YES          
About_Download longtext YES          
About_Array_Platform longtext YES          
About_Data_Values_Processing longtext YES          
Data_Source_Acknowledge longtext YES          
Progreso varchar(20) YES          
QualityControlStatus longtext YES          
InfoFileId int(6) NO PRI   auto_increment    


InfoFilesUser_md5

Comment:
Password information GN1 (unsecure no salt) use SALT !
Only two users as of March 2016, Rob and Arthur.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Username varchar(16) YES          
Password varchar(32) YES          


Investigators

Comment:
What is this used for? This is part of Arthur Centeno's database for INFO files.
As of March 2016 has about 86.54 rows.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
FirstName varchar(20) YES          
LastName varchar(20) YES          
Address varchar(200) YES          
City varchar(20) YES          
State varchar(20) YES          
ZipCode varchar(20) YES          
Phone varchar(200) YES          
Email varchar(200) YES          
Country varchar(35) YES          
Url text YES          
UserName varchar(30) YES          
UserPass varchar(50) YES          
UserDate datetime YES          
UserLevel int(8) YES          
OrganizationId int(5) NO          
InvestigatorId int(5) NO PRI   auto_increment    


LCorrRamin3

Comment:
Should be updated in 2016. Literature correlations by Prof Ramin Homayouni (v3) in GN1 and GN2. These are mouse GeneIDs (table starts with GeneId1 = 381629 = the gene with symbol Atraid in mouse. This genes maps to HomoloGene 15412 and to human ATRAID (human GeneId 51374). This table should ideally work for mouse, human, and rat since most genes will have 1-to-1 homologs with matched symbols.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
GeneId1 int(12) unsigned YES MUL       Entrez gene ID values (mouse)
GeneId2 int(12) unsigned YES MUL       Entrez gene ID values (mouse)
value double YES         Latent sematic index scorel, value between 0 and 1


MachineAccessLog

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(10) unsigned NO PRI   auto_increment    
accesstime datetime NO   0000-00-00 00:00:00      
ip_address char(20) NO   0.0.0.0      
db_id tinyint(3) unsigned NO   0      
data_id int(10) unsigned YES          
action char(10) YES          


MappingMethod

Comment:
Needs to be updated, but this is used in GN1 to select mapping methods for different cohorts/populations. PLINK is used for map human cohorts in GN1. Happy is not implemented as of March 2016 in either GN1 or GN2. R/qtl implemented by DA in GN2 in 2015. FastMap probably equal pyLMM and should be renamed. QTL Reaper = Haley Knott regression mapping and probably should be renamed HKMap.

QTL Reaper, R/qtl, Happy, PLINK, FastMap
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Name varchar(100) NO          


NStrain

Comment:
Merge this table to "PublishSE" and "PublishData". Values are used to track n of cases or n or strains for "published" phenotype values. Move these data to PublishSE and PublishData, phenotype SE, and N strain are three columns shows to users of Published Phenotypes. Unknown function at this time, seems like this contains information which is derived, so looks like it might be unneeded (DA March 2016).

Contains 160,000 rows, March 2016.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned YES MUL        
StrainId smallint(5) unsigned YES          
count varchar(5) YES          


News

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id int(11) NO PRI   auto_increment    
date date YES          
details text YES          


Organizations

Comment:
Table generated by Arthur Centeno for INFO files and metadata.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
OrganizationId int(5) NO PRI   auto_increment    
OrganizationName varchar(200) NO UNI        


Phenotype

Comment:
This table contains names, full descriptions, and short symbols for traits and phenotype used primarily in the Published Phenotypes databases.

Contains 10k rows, March 2016, of which 5000 are for the BXDs).

Created by Jintao Wang, March 2003.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Pre_publication_description text YES MUL        
Post_publication_description text YES MUL        
Original_description text YES          
Units varchar(100) NO   Unknown     units of measurement of the phenotype
Pre_publication_abbreviation varchar(40) YES MUL        
Post_publication_abbreviation varchar(40) YES MUL        
Lab_code varchar(255) YES          
Submitter varchar(255) YES          
Owner varchar(255) YES          
Authorized_Users varchar(255) YES          


Probe

Comment:
DEPRECATED. Used only for a few array platforms in GN1. Not used for GN2. This table contains data on the characteristics of individual Affymetrix probes (not probe sets). Data are used to populate the Probe Tables which display sequences of the perfect match and mismatch probes. This table could also contain data on the Agilent 60-mer probes.
Created by Yanhua Qu and Jintao Wang, 2003.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment   internal identifier
ProbeSetId int(10) unsigned NO MUL 0     Affymetrix probe set identifier to which the probe belongs using the conventional CDF probe-probeset mapping
Name char(20) YES         six to eight character name (depending on array) XXX coordinate then YYY coordinate with 0 used a buffer
Sequence char(30) YES         25 nucleotide sequence
ExonNo char(7) YES         exon to which the probe sequence corresponds. When a probe straddles two exons we use the format 10*11
SerialOrder double YES         probe order from the most 3' probe to the most 5' probe
Tm double YES         theoretical melting temperature of a DNA-DNA hybrid. The actual probes are cRNA
E_GSB double YES         the gene-specific binding energy computed using Li Zhang's PDNN method. Data provided by Li Zhang
E_NSB double YES         the non-specific binding energy computed using Li Zhang's PDNN method. Data provided by Li Zhang


ProbeData

Comment:
Table with fine-grained probe level Affymetrix data only. Contains 1 billion rows March 2016. This table may be deletable since it is only used by the Probe Table display in GN1. Not used in GN2 (double-check).

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. In comparison, ProbeData contains data only for Affymetrix probe level data (e.g. Exon array probes and M430 probes).


"ProbeData.StrainId" should be "CaseId" or "SampleId".

"ProbeData" should probably be "AssayData" or something more neutral.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
value float NO          


ProbeFreeze

Comment:
About the Name: ProbeFreeze is a stupid (historic) name for this table. The table should be renamed to more general and sensible such as "Data_Set_Group_Info" table and ProbeSetFreeze should be changed to something like "Data_Set_Info" table. At present, every ProbeSetFreeze record needs a parent ProbeFreeze record, even when the relation is 1-to-1.

About This Table: The ProbeFreeze table provides information about the overall set of microarray hybridization experiments - a meaningful name that identifies the experiment, the link to the microarray chip name, the link to tissue, organ or other generic biological material name, the link to the mapping population, inbred strain set name or similar used in the experiment.

A ProbeFreeze may have a subset of ProbeSetFreezes (one ProbeFreeze to many ProbeSetFreezes) to which it belongs as children data sets (for example, male data only, female data only, RMA data or MAS5 data. The name provides a short description of the experiment. New records in the table should be inserted only after the relevant records in the GeneChip, Tissue and InbredSet are in place. 34 records on Dec14,2006. ID1206
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment Primary key Incremented integer, uniquely idenifies the record. ID1206
ProbeFreezeId int(5) YES          
ChipId smallint(5) unsigned NO   0   GeneChip:Id Links to the information about the microarray chip used. ID1206
TissueId smallint(5) unsigned NO   0   Tissue:Id Links to the information about the biological material analysed . ID1206
Name varchar(100) NO UNI       Abbreviated description that identifies the microarray experiment. The existing records contain institute id, short biological material description, the microarray chip name and date in brackets, but the field can contain any meaningful description of the microarray experiment. ID1206
FullName varchar(100) NO         Empty field. ID1206
ShortName varchar(100) NO         Empty field. ID1206
CreateTime date NO   0000-00-00     now()
InbredSetId smallint(5) unsigned YES   1   InbredSet:Id Links to the information about the cross, mapping population, inbred strain set or similar used in the experiment. ID1206


ProbeH2

Comment:
DEPRECATED. Will not be used in GN2. Please compare to H2. Used only for some the heritability of probes shown in the Probe table.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
ProbeFreezeId smallint(5) unsigned NO PRI 0      
ProbeId int(10) unsigned NO PRI 0      
h2 double YES          
weight double YES          


ProbeSE

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
error float NO          


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.

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.

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.

Annotation by Rob Williams, Aug 19, 2005. Created by Jintao Wang, 2003. This annotation updated March 22, 2016 by Rob.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment   internal identifer used by GeneNetwork
ChipId smallint(5) unsigned NO MUL 0     identifier of the array type
Name varchar(100) YES MUL       Affymetrix probe set identifier or Agilent probe identifier
TargetId varchar(150) YES MUL        
Symbol varchar(100) YES MUL       gene symbol assigned to the probe set or probe (editable using Update page interface)
description longtext YES         gene description assigned to the probe set or probe (editable using Update page interface)
Chr char(3) YES MUL       chromosome assigned to the probe set or probe (editable using Update page interface)
Mb double YES          
Chr_2016 char(3) YES          
Mb_2016 double YES          
alias longtext YES         gene aliases and old symbols associated with the gene assigned to the probe set or probe (editable using Update page interface)
GeneId varchar(20) YES MUL       Entrez gene ID assigned to the probe set or probe (editable using Update page interface)
GenbankId varchar(1000) YES MUL       GenBank ID assigned to the probe set or probe as given to us by Affymetrix or Agilent (not editable from Update page)
SNP int(2) YES         unknown use
BlatSeq text NO         probe sequence or concatenated probe set sequence ( trimmed of overlap) used for BLAT alignment to genome (viewable but not editable from Update page)
TargetSeq text YES         target sequence as given to us by Affymetrix (viewable but not editable from Update page)
UniGeneId varchar(100) YES         chromosome assigned to the probe set or probe (viewable but not editable from Update page)
Strand_Probe char(1) YES         unknown use (redundant with Probe_set_strand ?) (editable using Update page interface)
Strand_Gene char(1) YES         the DNA strand (+ or -) of the gene assigned to the probe set or probe (editable using Update page interface)
OMIM varchar(20) YES         OMIM identifier assigned to the probe set or probe (editable using Update page interface)
comments text NO         record of modification time and person making modifications. Used to prevent overwriting of modified records.
Probe_set_target_region varchar(255) YES         DO NOT USE. Unknown use. May want to delete this field after review of possible use.
Probe_set_specificity double YES         the BLAT score of the correct probe set target mRNA divided by the best or next best BLAT score
Probe_set_BLAT_score double YES         the BLAT score generated by the concatenated probe set (or probe) sequence for the correct target mRNA. This will usually be the highest BLAT score, but in some cases a non-trasncribed genomic sequence may match better than the actual transcribed mRNA target sequence.
Probe_set_Blat_Mb_start double YES         the proximal (low) megabase position matched by the probe set sequence (3' or 5' end)
Probe_set_Blat_Mb_end double YES         the distal (high) megabase position matched by the probe set sequence (5' or 3' end)
Probe_set_Blat_Mb_start_2016 double YES          
Probe_set_Blat_Mb_end_2016 double YES          
Probe_set_strand varchar(255) YES         the DNA strand (+ or -) that is identical to the probe set nucleotide sequence. By convention, correctly directed probe sets have the same direction as the gene.
Probe_set_Note_by_RW varchar(255) YES         notes of the probe set by Robert Williams
flag char(1) YES         a status flag on the probe set: 0=mismatch between blat results and affy symbols (Problem!!!!); 1=match between blat results and affy symbols (Excellent); 2=symbols from TIGR or replaced by Blat symbols (The original affy symbols have "///" or "_predicted") (ok); 3: symbols from BLAT (ok); 4. symbols from Affy (not bad) or no symbol (sad); 5. symbols from target sequence blating (??); 6. symbols from genebank sequence blating (???); 7=symbols from blating to mouse genome(????)
Symbol_H varchar(100) YES         official human gene symbol from UCSC ftp site (hg17)
description_H varchar(255) YES         official human gene description from NCBI ftp site (Build 35)
chromosome_H char(3) YES         official human gene description from UCSC ftp site (Build 35)
MB_H double YES         Converted from ProbeSet.Mb_mm6 by Batch Coordinate Conversion
alias_H varchar(255) YES         official human gene description from NCBI ftp site (Build 35)
GeneId_H varchar(20) YES         official human gene description from NCBI ftp site (Build 35)
chr_num smallint(5) unsigned YES   30     the numerical value of chromosomes, for example, X is 20 or 21 depending on species
name_num int(10) unsigned YES   4294967290     the numerical value of Affymetrix probe set identifier or Agilent probe identifier
Probe_Target_Description varchar(225) YES         description of the region of the gene and transcript targetted by the probe set or probe (this text is displayed after the semicolon in Search Results; this is a searchable field)
RefSeq_TranscriptId varchar(255) YES MUL       the reference sequence of the mRNA associated with the probe set. These are always receded with "NM_". This field was added to allow easier linkage from the UCSC Genome Browser to GN.
ENSEMBLGeneId varchar(50) YES          
Chr_mm8 char(3) YES          
Mb_mm8 double YES          
Probe_set_Blat_Mb_start_mm8 double YES          
Probe_set_Blat_Mb_end_mm8 double YES          
HomoloGeneID varchar(20) YES          
Biotype_ENS varchar(255) YES          
ProteinID varchar(50) YES          
ProteinName varchar(50) YES          
UniProtID varchar(20) YES          
Flybase_Id varchar(25) YES          
RGD_ID int(10) YES          
HMDB_ID varchar(255) YES          
Confidence int(5) YES          
ChEBI_ID int(10) YES          
ChEMBL_ID varchar(100) YES          
CAS_number varchar(100) YES          
PubChem_ID int(10) YES          
ChemSpider_ID int(10) YES          
UNII_ID varchar(100) YES          
EC_number varchar(100) YES          
KEGG_ID varchar(100) YES          
Molecular_Weight double YES          
Nugowiki_ID int(10) YES          
Type varchar(255) YES          
Tissue varchar(255) YES          
PrimaryName varchar(255) YES          
SecondaryNames longtext YES          
PeptideSequence varchar(20) YES          


ProbeSetData

Comment:
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).


"StrainId" should be "CaseId" or "SampleId".

"ProbeSetData" should probably be "AssayData" or something more neutral.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
value float NO          


ProbeSetFreeze

Comment:
RENAME: AssayDataSet or something more neutral. Delete the word "Freeze" from this table.

ProbeSetFreeze contains the information of the data analysis method used in the processing the microarray experiment data described in the ProbeFreeze table and the confidentiality of the resulting data. New records should be inserted only if the relevant ProbeFreeze and AvgMethod records are in place. The use of the four different name fields effectively containing 4 versions of the same information needs to be clarified. 120 records on Dec 2006. About 700 records March 2016.

The name of a data set in GN is "ProbeSetFreeze.FullName" and is used in several output tables and graphs, for example ClusterMap in this format -- ProbesetID::FullName


Comment by PP and RW March 2016: This key table needs to be evaluated and renamed. Was initially designed to hold large microarray data, but now must accommodate any large "omics" data, but not genotypes and not classic phenotypes. Some trait data is ambiguous such as metagenomics. Metagenomics probably should be included here rather than in Phenotypes.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment Primary Key Incremented integer, uniquely identifies the record. ID1206
ProbeFreezeId smallint(5) unsigned NO   0   ProbeFreeze:Id Links to the microarray experiment description in ProbeFreeze table. ID1206
AvgID smallint(5) unsigned NO   0   AvgMethod:Id Links to the method used in the processing of the microarray experiment data (like RMA, MAS etc.). ID1206
Name varchar(40) YES UNI       Very short abbreviation of the microarray experiment description, the use needs to be clarified. Contains tissue abbreviaton_chip abbreviation_date_processing method abbreviation. ID1206
Name2 varchar(100) NO MUL       The same as in Name, only a bit longer - tissue_chip_method_date. The use needs to be clarified. ID1206
FullName varchar(100) NO UNI       Similar to ProbeFreeze name (institute tissue chip date)+ the data analysis method used. ID1206
ShortName varchar(100) NO         Similar to FullName with random items abbreviated. ID1206
CreateTime date NO   0000-00-00     now()
OrderList int(5) YES          
public tinyint(4) NO   0     1 = beta data, 2 = available in GeneNetwork. ID1206. beta means the dataset will not appear in the search page if the user does not login
confidentiality tinyint(4) NO   0     0 = not confidential, 1 = confidential. ID1206. confidential means the dataset will appear in the search page, but will be serached only after the user login
AuthorisedUsers varchar(300) NO          
DataScale varchar(20) NO   log2      


ProbeSetSE

Comment:
Contains standard error of assays in ProbeSetData. Roughly 0.5 billion rows as of March 2016. Human data do not have error terms usually.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
error float NO          


ProbeSetXRef

Comment:
This table contains summary data used by the Advanced Search feature in GN1 and GN2 (e.g "mean=(6 20)", for a particular data sets, including information on average expression of probe sets and probes, phenotypes, LRS values of single best QTL, p values of single best QTL, additive effect of single best QTL, locus ID of the marker closest to the single best QTL, etc.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
ProbeSetFreezeId smallint(5) unsigned NO PRI 0      
ProbeSetId int(10) unsigned NO PRI 0      
DataId int(10) unsigned NO UNI 0      
Locus_old char(20) YES          
LRS_old double YES          
pValue_old double YES          
mean double YES         average expression across data set for a particular probe set
se double YES         range of expression (not actually SE) across data set for a particular probe set
Locus char(20) YES         locus or marker closest to highest QTL peak
LRS double YES         LRS from QTL Reaper of highest QTL peak
pValue double YES         p value based on QTL Reaper of highest QTL peak
additive double YES         additive effect size from QTL Reaper at highest QTL peak
h2 float YES          


ProbeXRef

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
ProbeFreezeId smallint(5) unsigned NO PRI 0      
ProbeId int(10) unsigned NO PRI 0      
DataId int(10) unsigned NO UNI 0      


Publication

Comment:
Used by Phenotypes data sets. Each published phenotype is associated with a PubMed ID. All data should ideally be populated automatically from PubMed rather entered by users.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
PubMed_ID int(10) unsigned YES UNI        
Abstract text YES          
Authors text NO          
Title varchar(255) YES          
Journal varchar(255) YES          
Volume varchar(255) YES          
Pages varchar(255) YES          
Month varchar(255) YES          
Year varchar(255) NO   0      


Publication_Test

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
PubMed_ID int(10) unsigned YES UNI        
Abstract text YES          
Authors text NO          
Title varchar(255) YES          
Journal varchar(255) YES          
Volume varchar(255) YES          
Pages varchar(255) YES          
Month varchar(255) YES          
Year varchar(255) NO   0      


PublishData

Comment:
Data on phenotypes. Equivalent roughly to ProbeSetData. 1 million records as of March 2016. Much of these data are actually not published yet.
This is really "StandardPhenotypeData". Currently also includes some metagenomic data.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
value float(14,6) YES          


PublishFreeze

Comment:
This is a table of cohorts/populations that have associated Phenotype data in the "Type" menu of the GN "Select and Search" page. As of March 2016 this table has 34 rows corresponding to 34 Published Phenotype data sets for different groups. When we enter a new group (cohort or population or RI set) that will have phenotypes into GeneNetwork, then we need to add data to this table. Be careful, when you add a new "PublishFreeze.Name" here then you must also make sure that the Group name (e.g. "BXD" in the "InbredSet" table) exactly matches the first part of the PublishFreeze.Name" or else the code will generate an error.

FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   Unique integer. Just an integer that unique specifies one of the Published Phenotype data sets. 1 = BXDPublish, 34 = HSNIHPPublish
Name varchar(100) NO         Must be unique. This is the name of the Published (and unpublished) data associated with a cohort or population. The name here (example "BXDPublish") must match a specific cohort name (e.g. "BXD", also known as an "InbredSet" name).
FullName varchar(100) NO         This is the long name that goes into the menu. Order is not controlled yet but this could be added.
ShortName varchar(100) NO          
CreateTime date NO   2001-01-01      
public tinyint(4) NO   0      
InbredSetId smallint(5) unsigned YES   1      
confidentiality tinyint(3) YES   0      
AuthorisedUsers varchar(100) YES          


PublishSE

Comment:
Table contains the standard error of the phenotype value.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
DataId int(10) unsigned NO PRI 0      
StrainId smallint(5) unsigned NO PRI 0      
error float NO          


PublishXRef

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment    
InbredSetId smallint(5) unsigned NO PRI 0      
PhenotypeId smallint(5) unsigned NO UNI 0      
PublicationId smallint(5) unsigned NO   0      
DataId int(10) unsigned NO UNI 0      
mean double YES          
Locus char(50) YES          
LRS double YES          
additive double YES          
Sequence smallint(5) unsigned NO   1      
comments text NO          


RatSnpPattern

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(12) NO PRI   auto_increment    
SnpId int(12) NO UNI        
BN char(1) YES          
F344 char(1) YES          
ACI char(1) YES          
BBDP char(1) YES          
FHH char(1) YES          
FHL char(1) YES          
GK char(1) YES          
LE char(1) YES          
LEW char(1) YES          
LH char(1) YES          
LL char(1) YES          
LN char(1) YES          
MHS char(1) YES          
MNS char(1) YES          
SBH char(1) YES          
SBN char(1) YES          
SHR char(1) YES          
SHRSP char(1) YES          
SR char(1) YES          
SS char(1) YES          
WAG char(1) YES          
WLI char(1) YES          
WMI char(1) YES          
WKY char(1) YES          
ACI_N char(1) YES          
BN_N char(1) YES          
BUF_N char(1) YES          
F344_N char(1) YES          
M520_N char(1) YES          
MR_N char(1) YES          
WKY_N char(1) YES          
WN_N char(1) YES          


Sample

Comment:
DEPRECATED. Only used in GN1 between 2004 and about 2006 to display images of microarray data.

A table that provides access to low-level array data in GeneNetwork. This table is used only from tables embedded in INFO files such as http://www.genenetwork.org/dbdoc/BR_U_1203_MR.html. The table will call an Image URL and CEL file URL, a DAT file URL, etc. This format and table has not been used since about 2005. We should develop a better method to allow access to raw data from GN.

FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
StrainId smallint(5) unsigned NO MUL 0      
Name varchar(30) YES          
Age smallint(6) NO   0      
Sex enum('F','M') NO   F      
CreateTime date NO   2001-01-01      
TissueType varchar(30) YES          
FromSrc varchar(10) YES          
ImageURL varchar(100) YES          
CELURL varchar(120) YES          
DATURL varchar(100) YES          
CHPURL varchar(100) YES          
RPTURL varchar(100) YES          
EXPURL varchar(100) YES          
TXTURL varchar(100) YES          


SampleXRef

Comment:
DEPRECATED. Used only in GN1 from 2004 to 2006 to display CEL files and array scan images for QC.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
SampleId smallint(5) unsigned NO PRI 0      
ProbeFreezeId smallint(5) unsigned NO PRI 0      


SnpAll

Comment:
CHECK, UPDATE or DELETE. Antique data that may be used by SNP Browser or by SNP displays in GN1 maps (single chromosome views). Probably only data for mouse in the table although structured for other species too.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(20) unsigned NO PRI   auto_increment    
SpeciesId smallint(5) unsigned YES   1      
SnpName char(30) YES MUL        
Rs char(30) YES MUL        
Chromosome char(2) YES MUL        
Position double YES          
Position_2016 double YES          
Alleles char(5) YES          
Source char(35) YES MUL        
ConservationScore double YES          
3Prime_UTR text YES          
5Prime_UTR text YES          
Upstream text YES          
Downstream text YES          
Intron char(1) YES          
Non_Splice_Site text YES          
Splice_Site text YES          
Intergenic char(1) YES          
Exon char(1) YES          
Non_Synonymous_Coding text YES          
Synonymous_Coding text YES          
Start_Gained text YES          
Start_Lost text YES          
Stop_Gained text YES          
Stop_Lost text YES          
Unknown_Effect_In_Exon text YES          
Domain varchar(50) YES          
Gene varchar(30) YES          
Transcript varchar(50) YES          


SnpAllRat

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(20) NO PRI   auto_increment    
SpeciesId int(5) YES   2      
SnpName char(30) YES MUL        
Chromosome char(2) YES MUL        
Position double YES          
Alleles char(5) YES          
Source char(35) YES MUL        
ConservationScore double YES          
Domain varchar(50) YES          
Gene varchar(30) YES          
Transcript varchar(50) YES          
Function varchar(50) YES          


SnpAllele_to_be_deleted

Comment:
OK, delete this then. (RWW March 2016)
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Base char(20) YES          
Info char(255) YES          


SnpPattern

Comment:
Data used by SNP Browser. Variant browser needs to be redone from scratch using a decent architecture. 80 million SNPs in this table from sequence data of 2011 (Keane et al. Nature) but imputed to many other strains.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
SnpId int(10) unsigned NO PRI 0      
129P2/OlaHsd char(1) YES          
129S1/SvImJ char(1) YES          
129S5/SvEvBrd char(1) YES          
AKR/J char(1) YES          
A/J char(1) YES          
BALB/cJ char(1) YES          
C3H/HeJ char(1) YES          
C57BL/6J char(1) YES          
CAST/EiJ char(1) YES          
CBA/J char(1) YES          
DBA/2J char(1) YES          
LP/J char(1) YES          
NOD/ShiLtJ char(1) YES          
NZO/HlLtJ char(1) YES          
PWK/PhJ char(1) YES          
SPRET/EiJ char(1) YES          
WSB/EiJ char(1) YES          
PWD/PhJ char(1) YES          
SJL/J char(1) YES          
NZL/LtJ char(1) YES          
CZECHII/EiJ char(1) YES          
CALB/RkJ char(1) YES          
ST/bJ char(1) YES          
ISS/IbgTejJ char(1) YES          
C57L/J char(1) YES          
Qsi5 char(1) YES          
B6A6_Esline_Regeneron char(1) YES          
129T2/SvEmsJ char(1) YES          
BALB/cByJ char(1) YES          
NZB/BlNJ char(1) YES          
P/J char(1) YES          
I/LnJ char(1) YES          
PERC/EiJ char(1) YES          
TALLYHO/JngJ char(1) YES          
CE/J char(1) YES          
MRL/MpJ char(1) YES          
PERA/EiJ char(1) YES          
IS/CamRkJ char(1) YES          
ZALENDE/EiJ char(1) YES          
Fline char(1) YES          
BTBRT<+>tf/J char(1) YES          
O20 char(1) YES          
C58/J char(1) YES          
BPH/2J char(1) YES          
DDK/Pas char(1) YES          
C57BL/6NHsd char(1) YES          
C57BL/6NTac char(1) YES          
129S4/SvJae char(1) YES          
BPL/1J char(1) YES          
BPN/3J char(1) YES          
PL/J char(1) YES          
DBA/1J char(1) YES          
MSM/Ms char(1) YES          
MA/MyJ char(1) YES          
NZW/LacJ char(1) YES          
C57BL/10J char(1) YES          
C57BL/6ByJ char(1) YES          
RF/J char(1) YES          
C57BR/cdJ char(1) YES          
129S6/SvEv char(1) YES          
MAI/Pas char(1) YES          
RIIIS/J char(1) YES          
C57BL/6NNIH char(1) YES          
FVB/NJ char(1) YES          
SEG/Pas char(1) YES          
MOLF/EiJ char(1) YES          
C3HeB/FeJ char(1) YES          
Lline char(1) YES          
SKIVE/EiJ char(1) YES          
C57BL/6NCrl char(1) YES          
KK/HlJ char(1) YES          
LG/J char(1) YES          
C57BLKS/J char(1) YES          
SM/J char(1) YES          
NOR/LtJ char(1) YES          
ILS/IbgTejJ char(1) YES          
C57BL/6JOlaHsd char(1) YES          
SWR/J char(1) YES          
C57BL/6JBomTac char(1) YES          
SOD1/EiJ char(1) YES          
NON/LtJ char(1) YES          
JF1/Ms char(1) YES          
129X1/SvJ char(1) YES          
C2T1_Esline_Nagy char(1) YES          
C57BL/6NJ char(1) YES          
LEWES/EiJ char(1) YES          
RBA/DnJ char(1) YES          
DDY/JclSidSeyFrkJ char(1) YES          
SEA/GnJ char(1) YES          
C57BL/6JCrl char(1) YES          
EL/SuzSeyFrkJ char(1) YES          
HTG/GoSfSnJ char(1) YES          
129S2/SvHsd char(1) YES          
MOLG/DnJ char(1) YES          
BUB/BnJ char(1) YES          


SnpSource

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
Name char(35) YES          
DateCreated date YES          
DateAdded date YES          


Species

Comment:
Contains the internal Ids and names for various species.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   internal GeneNetwork species identifier
SpeciesId int(5) YES          
SpeciesName varchar(50) YES          
Name char(30) NO MUL       the common name of the species
MenuName char(50) YES          
FullName char(100) NO          
TaxonomyId int(11) YES          
OrderId smallint(6) YES          


Strain

Comment:
RENAME "SubjectDescription" . This table to keep track of case/subject identifiers (cases or F2 or strains). But we have the problem that there may be multiple observation per subject or the time series data for one case, (multiple ages for one strain, etc). Generic sample/case identifiers here.
Contains 14,000 rows as of March 2016. We really need a new table "ObservationOfSubject" (RWW 2016)

Apurva would like to extend this table to include "DateOfBirth" and "BatchNumber".


FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   GN internal identifier for the strain
Name varchar(100) YES MUL       official strain name/symbol
Name2 varchar(100) YES          
SpeciesId smallint(5) unsigned NO   0   Species:Id species of strain
Symbol char(5) YES MUL       short strain symbol used in graphs and tables
Alias varchar(255) YES          


StrainXRef

Comment:
RENAME something like "SubjectXRef". This table links the information in the Strain table with InbredSet and provides order for the strains in the mapping population.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
InbredSetId smallint(5) unsigned NO PRI 0   InbredSet:Id The Id of the mapping population in the InbredSet table.
StrainId smallint(5) unsigned NO PRI 0   Strain:Id The Id of the mapping population strain in Strain table.
OrderId smallint(5) unsigned NO   0     Order of the strains in the Genotype file or mapping population, follows the pattern 10, 20 rather than 1,2.
Used_for_mapping char(1) YES   N      
PedigreeStatus varchar(255) YES          


Tissue

Comment:
(RWW Nov 2007): A small table that may be used to build pull-down menus in GN1 and GN2. This table contains simple one to three word terms describing the tissue, cell, organ, or other biological material used in the microarray experiment. This table is used in conjunction with the FIND RECORDS "Type" field.

As of Nov 2007 this table contained only 15 rows:
As of Mar 2016 this table contains 158 rows.

Whole Brain
Cerebellum
Hematopoietic Cells
Liver
etc.

How is this table used? Probably to create menu fields. Talk with Chris Mungall and colleagues about controlled vocabulary for APIs.

FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment   Incremented integer uniquely identifies the record.
TissueId int(5) YES          
TissueName varchar(50) YES          
Name char(50) YES UNI       Name of the biological material used in the experiment.
Short_Name char(30) NO UNI        
BIRN_lex_ID char(30) YES         Need to get official IDs for tissues, cells, etc.
BIRN_lex_Name char(30) YES         Need to get official IDs for tissues, cells, etc.


TissueProbeFreeze

Comment:
These 'Tissue' tables are used by the Tissue Correlation tool in GN1. Not implemented yet in GN2.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
ChipId smallint(5) unsigned NO   0      
StrainId varchar(100) NO   0      
Name varchar(100) NO UNI        
FullName varchar(100) NO UNI        
ShortName varchar(100) NO          
CreateTime date NO   0000-00-00      
InbredSetId smallint(5) unsigned YES   1      


TissueProbeSetData

Comment:
These 'Tissue' tables are used by the Tissue Correlation tool in GN1. Not implemented yet in GN2. Mainly Illumina Mouse version 6.1 data.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(10) unsigned NO PRI   auto_increment    
TissueID int(10) unsigned NO PRI 0      
value float NO   0      


TissueProbeSetFreeze

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id smallint(5) unsigned NO PRI   auto_increment    
TissueProbeFreezeId smallint(5) unsigned NO   0      
AvgID smallint(5) unsigned NO   0      
Name varchar(40) YES UNI        
Name2 varchar(100) NO MUL        
FullName varchar(100) NO UNI        
ShortName varchar(100) NO          
CreateTime date NO   0000-00-00      
public tinyint(4) NO   0      
confidentiality tinyint(4) NO   0      
AuthorisedUsers varchar(100) YES          


TissueProbeSetXRef

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
TissueProbeSetFreezeId smallint(5) unsigned NO PRI 0      
ProbesetId int(10) unsigned NO PRI 0      
DataId int(10) unsigned NO UNI 0      
Mean float YES   0      
useStatus char(1) YES          
Symbol varchar(100) YES MUL        
GeneId varchar(20) YES MUL        
Chr char(3) YES MUL        
Mb double YES          
Mb_2016 double YES          
description varchar(255) YES          
Probe_Target_Description varchar(225) YES          


TraitMetadata

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
type varchar(255) YES          
value longtext YES          


UserPrivilege

Comment:
Used to define if an Assay data set (old "ProbeSetFreeze") can be opened and downloaded.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
UserId int(10) unsigned NO MUL        
ProbeSetFreezeId smallint(5) unsigned NO          
download_result_priv enum('N','Y') NO   N      


Vlookup

Comment:
It's been used by Arthur for annotation updates.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
Id int(11) NO MUL   auto_increment    
VLProbeSetId text YES          
VLBlatSeq longtext YES          
InfoFileId int(5) YES          
DatasetId int(5) YES          
SpeciesId int(5) YES          
TissueId int(5) YES          
InbredSetId int(5) YES          
GeneChipId int(5) YES          
AvgMethodId int(5) YES          
InfoPageName varchar(255) YES          
GN_AccesionId int(5) YES          
Name varchar(100) YES          
GeneId varchar(10) YES          
Mb double YES          
Chr varchar(10) YES          
Probe_set_Blat_Mb_start double YES          
Probe_set_Blat_Mb_end double YES          
Strand char(1) YES          
TxStart double YES          
TxEnd double YES          
cdsStart double YES          
cdsEnd double YES          
exonCount int(7) YES          
exonStarts text YES          
exonEnds text YES          
ProteinID varchar(15) YES          
AlignID varchar(10) YES          
kgID varchar(10) YES          
NM_ID varchar(15) YES          
SnpName char(30) YES          
Position double YES          
HMDB_ID varchar(255) YES          
Symbol varchar(100) YES          
description longtext YES          
alias longtext YES          
Full_Description longtext YES          
BlatSeq text YES          
ChEBI_ID int(10) YES          
ChEMBL_ID varchar(100) YES          
CAS_number varchar(100) YES          
PubChem_ID int(10) YES          
ChemSpider_ID varchar(10) YES          
UNII_ID varchar(100) YES          
EC_number varchar(100) YES          
KEGG_ID varchar(100) YES          
Molecular_Weight varchar(100) YES          
Nugowiki_ID varchar(100) YES          
assembly varchar(10) YES          


login

Comment:
Used by GN2. Just a login file. 277 login in GN2 as of March 2016.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id varchar(36) NO PRI        
user varchar(36) YES MUL        
timestamp datetime YES          
ip_address varchar(39) YES          
successful tinyint(1) NO          
session_id text YES          
assumed_by varchar(36) YES MUL        


pubmedsearch

Comment:
Data table used to find gene symbols associated with authors. Created by Lei Yan for Author search functions in GN1. Check if this works in GN2. This function has alrways been a bit flaky.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id bigint(20) NO PRI   auto_increment    
pubmedid bigint(20) YES          
title text YES          
authorfullname text YES MUL        
authorshortname text YES          
institute text YES MUL        
geneid varchar(20) YES MUL        


role

Comment:
Noble intent table. Can be deleted or implemented. Idea was "administrator", "curator", "owner", "user"
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
the_id varchar(36) NO PRI        
name varchar(80) NO UNI        
description varchar(255) YES          


roles_users

Comment:
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
user_id int(11) YES MUL        
role_id int(11) YES MUL        


temporary

Comment:
This is probably the content of the user's collection. Lost at end of user's session.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
tax_id varchar(20) YES          
GeneID varchar(20) YES          
Symbol varchar(100) YES          
OMIM varchar(100) YES          
HomoloGene varchar(100) YES          
Other_GeneID varchar(20) YES          


user

Comment:
GN2 only.

Comment on User type and password.
This table has 47 users in GN2 as of Mar 2016.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id varchar(36) NO PRI        
email_address varchar(50) NO UNI        
password text NO         this is a hash value of user's password
full_name varchar(50) YES          
organization varchar(50) YES          
active tinyint(1) NO          
registration_info text YES          
confirmed text YES          
superuser text YES          


user_collection

Comment:
GN2 user collection of traits.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
id varchar(36) NO PRI        
user varchar(36) YES MUL        
name text YES          
created_timestamp datetime YES          
changed_timestamp datetime YES          
members text YES          


user_openids

Comment:
TO BE IMPLEMENTED (maybe). Table to link to OpenID. Probably not needed.
FieldTypeNullKeyDefaultExtraForeign_KeyAnnotation
openid_url varchar(255) NO PRI        
user_id varchar(36) NO MUL        


CITG Web services initiated January, 1994 as Portable Dictionary of the Mouse Genome; June 15, 2001 as WebQTL; and Jan 5, 2005 as GeneNetwork. This site is currently operated by Rob Williams, Pjotr Prins, Zachary Sloan, Arthur Centeno. Design and code by Pjotr Prins, Zach Sloan, Arthur Centeno, Danny Arends, Christian Fischer, Sam Ockman, Lei Yan, Xiaodong Zhou, Christian Fernandez, Ning Liu, Rudi Alberts, Elissa Chesler, Sujoy Roy, Evan G. Williams, Alexander G. Williams, Kenneth Manly, Jintao Wang, and Robert W. Williams, colleagues. Python Powered Registered with Nif
GeneNetwork support from:
  • The UT Center for Integrative and Translational Genomics
  • NIGMS Systems Genetics and Precision Medicine project (R01 GM123489, 2017-2021)
  • NIDA NIDA Core Center of Excellence in Transcriptomics, Systems Genetics, and the Addictome (P30 DA044223, 2017-2022)
  • NIA Translational Systems Genetics of Mitochondria, Metabolism, and Aging (R01AG043930, 2013-2018)
  • NIAAA Integrative Neuroscience Initiative on Alcoholism (U01 AA016662, U01 AA013499, U24 AA013513, U01 AA014425, 2006-2017)
  • NIDA, NIMH, and NIAAA (P20-DA 21131, 2001-2012)
  • NCI MMHCC (U01CA105417), NCRR, BIRN, (U24 RR021760)
    Join GeneNetwork Mailing List
    It took 0.306 second(s) for lily.uthsc.edu to generate this page