EnsemblEnsembl Home

Ensembl Variation - Schema documentation

This document gives a high-level description of the tables that make up the Ensembl variation schema. Tables are listed by alphabetical order, and the purpose of each table is explained. It is intended to allow people to familiarise themselves with the schema when encountering it for the first time, or when they need to use some tables that they've not used before.

This document refers to version 75 of the Ensembl variation schema.

The variation database schema diagram (PDF format) is available here:



List of the tables:

Attributes tables

Metadata tables



Attributes tables

These tables define the variation attributes data.

Defines various attributes used elsewhere in the database

Example:

See below the query to display a subset of the attrib entries:

SELECT * FROM attrib WHERE attrib_type_id IN (1,2,3) ORDER BY attrib_id LIMIT 21;
show Show query results

See also:

Groups related attributes together

See also:

Defines the set of possible attribute types used in the attrib table

Example:

See below the command to display a subset of the attrib_type entries:

SELECT * FROM attrib_type LIMIT 10;
show Show query results

See also:



Failed tables

These tables define the list of variants/alleles flagged as "failed" in the Variation pipeline.
The list of reasons for a variation being flagged as failed is available in the Quality Control documentation.

Contains alleles that did not pass the Ensembl filters

See also:

failed_description

This table contains descriptions of reasons for a variation being flagged as failed.

Example:

See below the list of the descriptions available in the ensembl variation databases:

SELECT * FROM failed_description;
show Show query results

See also:

failed_structural_variation

For various reasons it may be necessary to store information about a structural variation that has failed quality checks (mappings) in the Structural Variation pipeline. This table acts as a flag for such failures.

See also:

failed_variation

For various reasons it may be necessary to store information about a variation that has failed quality checks in the Variation pipeline. This table acts as a flag for such failures.

See also:



Genotype tables

These tables define the genotype data at the individual and population levels.

compressed_genotype_region

This table holds genotypes compressed using the pack() method in Perl. These genotypes are mapped to particular genomic locations rather than variation objects. The data have been compressed to reduce table size and increase the speed of the web code when retrieving strain slices and LD data. Only data from resequenced and individuals used for LD calculations are included in this table

See also:

compressed_genotype_var

This table holds genotypes compressed using the pack() method in Perl. These genotypes are mapped directly to variation objects. The data have been compressed to reduce table size. All genotypes in the database are included in this table (included duplicates of those genotypes contained in the compressed_genotype_region table). This table is optimised for retrieval from

See also:

individual_genotype_multiple_bp

This table holds uncompressed genotypes for given variations.

See also:

population_genotype

This table stores genotypes and frequencies for variations in given populations.

See also:

tmp_individual_genotype_single_bp

his table is only needed to create master schema when run healthcheck system. Needed for other species, but human, so keep it.

See also:



Metadata tables

These tables define some metadata information.

This table stores various metadata relating to the database, generally used by the Ensembl web code.

This table gives the coordinate system used by various tables in the database.



Other tables

These tables define the other data associated with a variation.

This table stores the relationship between the internal allele identifiers and the alleles themselves.

Example:

See below the first entries in the allele_code table:

SELECT * FROM allele_code LIMIT 6;
show Show query results

See also:

Stores information about the available co-ordinate systems for the species identified through the species_id field. Note that for each species, there must be one co-ordinate system that has the attribute "top_level" and one that has the attribute "sequence_level".

See also:

This table stores genotype codes as multiple rows of allele_code identifiers, linked by genotype_code_id and ordered by haplotype_id.

See also:

This table stores details of the phenotypes associated with phenotype_features.

See also:

This table stores the relationship between Ensembl's internal coordinate system identifiers and traditional chromosome names.

See also:

submitter_handle

This table holds a short string to distinguish data submitters

See also:

This table contains the SubSNP(ss) ID and the name of the submitter handle of dbSNP.

See also:

tagged_variation_feature

This table lists variation feature IDs that are tagged by another variation feature ID. Tag pairs are defined as having an r2 > 0.99.

See also:



Phenotype tables

These tables store information linking entities (variants, genes, QTLs) with phenotypes and other annotations.

phenotype_feature

This table stores information linking entities (variants, genes, QTLs) and phenotypes.

See also:

phenotype_feature_attrib

This table stores additional information on a given phenotype/object association. It is styled as an attrib table to allow for a variety of fields to be populated across different object types.

See also:



Protein tables

These tables define the protein prediction data.

protein_function_predictions

Contains encoded protein function predictions for every protein-coding transcript in this species.

See also:

translation_md5

Maps a hex MD5 hash of a translation sequence to an ID used for the protein function predictions

See also:



Sample tables

These tables define the individual and population information.

Stores information about an identifiable individual, including gender and the identifiers of the individual's parents (if known).

See also:

individual_population

This table resolves the many-to-many relationship between the individual and population tables; i.e. individuals may belong to more than one population. Hence it is composed of rows of individual and population identifiers.

See also:

individual_synonym

Used to store alternative names for individuals when data comes from multiple sources.

See also:

individual_type

This table gives a deatailed description for each of the possible individual types: fully_inbred, partly_inbred, outbred, mutant

Example:

See below the list of individual types:

SELECT * FROM individual_type;
show Show query results

See also:

Stores information about a population. A population may be an ethnic group (e.g. caucasian, hispanic), assay group (e.g. 24 europeans), phenotypic group (e.g. blue eyed, diabetes) etc. Populations may be composed of other populations by defining relationships in the population_structure table.

See also:

population_structure

This table stores hierarchical relationships between populations by relating them as populations and sub-populations.

See also:

population_synonym

Used to store alternative names for populations when data comes from multiple sources.

See also:



Source/study tables

These tables define the variation source and study information.

associate_study

This table contains identifiers of associated studies (e.g. NHGRI and EGA studies with the same pubmed identifier).

See also:

This table contains details of publications citing variations. This information comes from dbSNP, UCSC and Europe PMC.

See also:

This table contains details of the source from which a variation is derived. Most commonly this is NCBI's dbSNP; other sources include SNPs called by Ensembl.
You can see the complete list, by species, here.

Example:

See below the command listing all the data sources in the human variation database:

SELECT * FROM source ORDER BY source_id;
show Show query results

See also:

This table contains details of the studies. The studies information can come from internal studies (DGVa, EGA) or from external studies (Uniprot, NHGRI, ...).

See also:

study_variation

Links a variation to a study

See also:

variation_citation

This table links a variation to a publication

See also:



Structural variation tables

These tables define the structural variation data.

structural_variation

This table stores information about structural variation.

See also:

structural_variation_association

This table stores the associations between structural variations and their supporting evidences.

See also:

structural_variation_feature

This table stores information about structural variation features (i.e. mappings of structural variations to genomic locations).

See also:

structural_variation_sample

This table stores individual and strain information for structural variants and their supporting evidences.

See also:



Variation effect tables

These tables define the variation effect prediction data in different Ensembl features.

motif_feature_variation

This table relates a single allele of a variation_feature to a motif feature (see Regulation documentation). It contains the consequence of the allele.

See also:

regulatory_feature_variation

This table relates a single allele of a variation_feature to a regulatory feature (see Regulation documentation). It contains the consequence of the allele.

See also:

transcript_variation

This table relates a single allele of a variation_feature to a transcript (see Core documentation). It contains the consequence of the allele e.g. intron_variant, non_synonymous_codon, stop_lost etc, along with the change in amino acid in the resulting protein if applicable.

See also:



Variation set tables

These tables define the variation and structural variation set data. The complete list of variation sets with their descriptions is available here.

This table containts the name of sets and subsets of variations stored in the database. It usually represents the name of the project or subproject where a group of variations has been identified.

See also:

variation_set_structural_variation

A table for mapping structural variations to variation_sets.

See also:

variation_set_structure

This table stores hierarchical relationships between variation sets by relating them as variation sets and variation subsets.

See also:

variation_set_variation

A table for mapping variations to variation_sets.

See also:



Variation tables

These tables define the central variation data.

This table stores information about each of a variation's alleles, along with population frequencies.

See also:

This is the schema's generic representation of a variation, defined as a genetic feature that varies between individuals of the same species. The most common type is the single nucleotide variation (SNP) though the schema also accommodates copy number variations (CNVs) and structural variations (SVs).
In Ensembl, a variation is defined by its flanking sequence rather than its mapped location on a chromosome; a variation may in fact have multiple mappings across a genome, although this fails our Quality Control.
This table stores a variation's name (commonly an ID of the form e.g. rs123456, assigned by dbSNP), along with a validation status and ancestral (or reference) allele.

See also:

variation_feature

This table represents mappings of variations to genomic locations. It stores an allele string representing the different possible alleles that are found at that locus e.g. "A/T" for a SNP, as well as a "worst case" consequence of the mutation. It also acts as part of the relationship between variations and transcripts.

See also:

variation_synonym

This table allows for a variation to have multiple IDs, generally given by multiple sources.

See also: