Generic Genetic Studies Database Schema


Tables

affection_status_allele_table
affection_status_table
age_group_phenotype_percentile_link_table
age_group_table
allele_table
database_table
gene_table
genotype_table
individual_affection_status_link_table
individual_group_table
individual_individual_group_link_table
individual_table
map_marker_link_table
map_table
marker_table
pedigree_affection_status_link_table
pedigree_group_table
pedigree_pedigree_group_link_table
pedigree_table
phenotype_table
qualitative_phenotype_value_table
quantitative_phenotype_value_table
user_database_link_table
user_table

affection_status_allele_table

Top
Field Name Data Type Size Default Value Other Foreign Key
affection_status_allele_id int 11 PRIMARY KEY, NOT NULL
affection_status_id int 11 UNIQUE, NOT NULL
allele_code int 2 UNIQUE, NOT NULL
database_id int 11 NOT NULL
allele_frequency float 7,5 0.00000 NOT NULL
last_updated datetime 0 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE affection_status_id, allele_code

affection_status_table

Top
Field Name Data Type Size Default Value Other Foreign Key
affection_status_id int 11 PRIMARY KEY, NOT NULL
affection_status_name varchar 52 UNIQUE, NOT NULL
number_of_alleles int 2 2 NOT NULL
affection_status_description text 65535
database_id int 11 0 UNIQUE, NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE affection_status_name, database_id

age_group_phenotype_percentile_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
age_group_phenotype_percentile_link_id int 11 PRIMARY KEY, NOT NULL
age_group_id int 11 0 UNIQUE, NOT NULL
phenotype_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
age_group_phenotype_male_10th_percentile_value float 10,5 0.00000 NOT NULL
age_group_phenotype_male_90th_percentile_value float 10,5 0.00000 NOT NULL
age_group_phenotype_female_10th_percentile_value float 10,5 0.00000 NOT NULL
age_group_phenotype_female_90th_percentile_value float 10,5 0.00000 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE age_group_id, phenotype_id

age_group_table

Top
Field Name Data Type Size Default Value Other Foreign Key
age_group_id int 11 PRIMARY KEY, NOT NULL
age_group_min_age int 3 0 UNIQUE, NOT NULL
age_group_max_age int 3 0 UNIQUE, NOT NULL
age_group_nationality varchar 52 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE age_group_min_age, age_group_max_age, age_group_nationality, database_id

allele_table

Top
Field Name Data Type Size Default Value Other Foreign Key
allele_id int 11 PRIMARY KEY, NOT NULL
marker_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
allele_code int 2 0 UNIQUE, NOT NULL
allele_sequence char 52 UNIQUE, NOT NULL
allele_size int 4 NULL
allele_frequency float 7,5 0.00000 NOT NULL
allele_affymetrix_code char 1 NULL
allele_illumina_code char 1 NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id
allele_affymetrix_code allele_affymetrix_code
allele_illumina_code allele_illumina_code

Constraints

Type Fields
UNIQUE marker_id, allele_code
UNIQUE marker_id, allele_sequence

database_table

Top
Field Name Data Type Size Default Value Other Foreign Key
database_id int 11 PRIMARY KEY, NOT NULL
database_name varchar 52 UNIQUE, NOT NULL
database_description text 65535 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Constraints

Type Fields
UNIQUE database_name

gene_table

Top
Field Name Data Type Size Default Value Other Foreign Key
gene_id int 11 PRIMARY KEY, NOT NULL
gene_name varchar 52 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
chromosome char 2 NOT NULL
gene_start_position int 11 NULL
gene_stop_position int 11 NULL
number_of_exons int 4 NULL
number_of_introns int 4 NULL
unigene_accession_number varchar 12 NULL
gene_description text 65535
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id
chromosome chromosome

Constraints

Type Fields
UNIQUE gene_name, database_id

genotype_table

Top
Field Name Data Type Size Default Value Other Foreign Key
genotype_id int 11 PRIMARY KEY, NOT NULL
individual_table_id int 11 0 UNIQUE, NOT NULL
marker_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
allele_1_id int 11 0 NOT NULL
allele_2_id int 11 0 NOT NULL
genotype_platform enum 10 NULL
genotype_score float 8,6 NULL
flagged int 1 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id
allele_1_id allele_1_id
allele_2_id allele_2_id
genotype_platform genotype_platform

Constraints

Type Fields
UNIQUE individual_table_id, marker_id

individual_affection_status_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
individual_affection_status_link_id int 11 PRIMARY KEY, NOT NULL
individual_table_id int 11 0 UNIQUE, NOT NULL
affection_status_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
individual_affection_status int 1 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE individual_table_id, affection_status_id

individual_group_table

Top
Field Name Data Type Size Default Value Other Foreign Key
individual_group_id int 11 PRIMARY KEY, NOT NULL
individual_group_name varchar 52 UNIQUE, NOT NULL
individual_group_description text 65535 NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE individual_group_name, database_id

individual_individual_group_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
individual_individual_group_link_id int 11 PRIMARY KEY, NOT NULL
individual_table_id int 11 0 UNIQUE, NOT NULL
individual_group_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE individual_table_id, individual_group_id

individual_table

Top
Field Name Data Type Size Default Value Other Foreign Key
individual_table_id int 11 PRIMARY KEY, NOT NULL
pedigree_table_id int 11 0 UNIQUE, NOT NULL
individual_id varchar 25 UNIQUE, NOT NULL
pedigree_id varchar 25 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
father_id varchar 25 NOT NULL
mother_id varchar 25 NOT NULL
sex int 1 0 NOT NULL
position_in_family varchar 25 NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
father_id father_id
mother_id mother_id

Constraints

Type Fields
UNIQUE individual_id, pedigree_id, pedigree_table_id, database_id

map_marker_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
map_marker_link_id int 11 PRIMARY KEY, NOT NULL
map_id int 11 UNIQUE, NOT NULL
marker_id int 11 UNIQUE, NOT NULL
database_id int 11 NOT NULL
chromosome varchar 2 NOT NULL
distance_from_previous_marker float 10,5 NOT NULL
marker_number_in_map int 11 NOT NULL
last_updated datetime 0 NOT NULL

Indices

Name Fields
database_id database_id
marker_number_in_map marker_number_in_map
last_updated last_updated
distance_from_previous_marker distance_from_previous_marker
chromosome chromosome

Constraints

Type Fields
UNIQUE map_id, marker_id

map_table

Top
Field Name Data Type Size Default Value Other Foreign Key
map_id int 11 PRIMARY KEY, NOT NULL
database_id int 11 UNIQUE, NOT NULL
map_name varchar 52 UNIQUE, NOT NULL
num_markers_in_map int 11 NOT NULL
distance_measure enum 22 base_pairs NOT NULL
map_description text 65535
last_updated datetime 0 NOT NULL

Indices

Name Fields
distance_measure distance_measure
num_markers_in_map num_markers_in_map
last_updated last_updated

Constraints

Type Fields
UNIQUE map_name, database_id

marker_table

Top
Field Name Data Type Size Default Value Other Foreign Key
marker_id int 11 PRIMARY KEY, NOT NULL
marker_name varchar 25 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
marker_type enum 14 snp NOT NULL
gene_id int 11 NULL
chromosome char 2 NOT NULL
chromosome_position int 11 NULL
genomic_strand enum 8 NULL
genomic_position enum 11 NULL
genome_build int 3 NULL
db_snp_version int 3 NULL
tag_snp int 1 0 NOT NULL
amino_acid_change enum 13 NULL
number_of_alleles int 2 0 NOT NULL
hardy_weinburg_equilibrium int 1 1 NOT NULL
flagged int 1 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
marker_type marker_type
gene_id gene_id
chromosome chromosome
genomic_position genomic_position
database_id database_id
genomic_strand genomic_strand

Constraints

Type Fields
UNIQUE marker_name, database_id

pedigree_affection_status_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
pedigree_affection_status_link_id int 11 PRIMARY KEY, NOT NULL
pedigree_table_id int 11 0 UNIQUE, NOT NULL
affection_status_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
number_affected_in_pedigree int 4 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE pedigree_table_id, affection_status_id

pedigree_group_table

Top
Field Name Data Type Size Default Value Other Foreign Key
pedigree_group_id int 11 PRIMARY KEY, NOT NULL
pedigree_group_name varchar 52 UNIQUE, NOT NULL
pedigree_group_description text 65535 NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE pedigree_group_name, database_id

pedigree_pedigree_group_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
pedigree_pedigree_group_link_id int 11 PRIMARY KEY, NOT NULL
pedigree_table_id int 11 0 UNIQUE, NOT NULL
pedigree_group_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE pedigree_table_id, pedigree_group_id

pedigree_table

Top
Field Name Data Type Size Default Value Other Foreign Key
pedigree_table_id int 11 PRIMARY KEY, NOT NULL
pedigree_id varchar 25 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
nationality varchar 52 NOT NULL
number_in_pedigree int 4 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
nationality nationality
database_id database_id

Constraints

Type Fields
UNIQUE pedigree_id, database_id

phenotype_table

Top
Field Name Data Type Size Default Value Other Foreign Key
phenotype_id int 11 PRIMARY KEY, NOT NULL
phenotype_name varchar 52 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
type_of_phenotype enum 12 quantitative NOT NULL
phenotype_description text 65535
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
type_of_phenotype type_of_phenotype
database_id database_id

Constraints

Type Fields
UNIQUE phenotype_name, database_id

qualitative_phenotype_value_table

Top
Field Name Data Type Size Default Value Other Foreign Key
qualitative_phenotype_value_id int 11 PRIMARY KEY, NOT NULL
individual_table_id int 11 UNIQUE, NOT NULL
phenotype_id int 11 UNIQUE, NOT NULL
database_id int 11 NOT NULL
phenotype_value varchar 52 NOT NULL
flagged int 1 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id
flagged flagged
phenotype_value phenotype_value
last_updated last_updated

Constraints

Type Fields
UNIQUE individual_table_id, phenotype_id

quantitative_phenotype_value_table

Top
Field Name Data Type Size Default Value Other Foreign Key
quantitative_phenotype_value_id int 11 PRIMARY KEY, NOT NULL
individual_table_id int 11 UNIQUE, NOT NULL
phenotype_id int 11 UNIQUE, NOT NULL
database_id int 11 NOT NULL
phenotype_value float 20,5 0.00000 NOT NULL
flagged int 1 0 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id
phenotype_value phenotype_value
flagged flagged
last_updated last_updated

Constraints

Type Fields
UNIQUE individual_table_id, phenotype_id

user_database_link_table

Top
Field Name Data Type Size Default Value Other Foreign Key
user_database_link_id int 11 PRIMARY KEY, NOT NULL
user_id int 11 0 UNIQUE, NOT NULL
database_id int 11 0 UNIQUE, NOT NULL
database_privileges enum 14 search NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Indices

Name Fields
database_id database_id

Constraints

Type Fields
UNIQUE user_id, database_id

user_table

Top
Field Name Data Type Size Default Value Other Foreign Key
user_id int 11 PRIMARY KEY, NOT NULL
user_name varchar 52 UNIQUE, NOT NULL
user_passwd varchar 52 NOT NULL
user_email varchar 100 NOT NULL
last_updated datetime 0 0000-00-00 00:00:00 NOT NULL

Constraints

Type Fields
UNIQUE user_name

Created by
SQL::Translator 0.09000