Table allele_donor

Product where an allele of a variant or haplotype originates or inherited from

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
  designation varchar Designation or germplasm name of a product that contributes an allele of variant, this is usually human readable and sometimes referred to as variety name or accession name
  expected_phenotype varchar This is the phenotype that the donor is expected to express or manifest
Indexes
pk_allele_donor_id ON id


Table allele_status

Defines the status of an allele of a variant or haplotype whether it is positive or negative or undefined

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* haplotype_definition_id integer
  allele_status varchar( 100 ) Indicates whether this particular allele is favorable [+], unfavorable [-], or undefined
  allele_description text Free-text description, this will be useful later in reporting to breeders
Indexes
pk_allele_status_id ON id
Foreign Keys
haplotype_definition_id_fk_allele_status ( haplotype_definition_id ) ref haplotype_definition (id)


Table assay

Platform specific chemistry that serves to detect presence or absence of particular allele, or genotype, of a variant.

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY Primary key of the table
* name varchar( 100 ) Name of the assay given and reported back by the vendor
  strand varchar Strand where assay was designed on, plus or minus
platform_id integer
* variant_id integer ID of the variant
  call_rate varchar Call rate for this marker on the validation run or across historical runs
  clarity varchar Whether a marker can clearly distinguish between genotypes
Indexes
pk_assay_id ON id
Foreign Keys
platform_id_fk_assay ( platform_id ) ref platform (id)
variant_id_fk_assay ( variant_id ) ref variant (id)


Table haplotype_definition

Defines haplotypes, which are alleles of a group of markers that are linked or inherited together

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* marker_group_variant_id integer
  allele_name varchar( 100 ) Name of haplotype
  position_call varchar Call for a particular bp position A/C/G/T/Indel specifier e.g. -10bp
allele_source_id integer
Indexes
pk_haplotype_definition_id ON id
Foreign Keys
allele_source_id_fk_haplotype_definition ( allele_source_id ) ref allele_donor (id)
marker_group_variant_id_fk_haplotype_definition ( marker_group_variant_id ) ref marker_group_variant (id)


Table marker_group

Group to where variants belong to. Commonly referred to as QTL but may not be always the case.

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY Primary key of the table.
* name varchar( 100 ) Name to describe the marker group. Must be unique across the system.
  type varchar Type to describe a particular marker group.
allele_source_id integer Id referring to a particular allele donor source
  description text Description for the QTL/Marker group
  derived_qtl_allele varchar
Indexes
pk_marker_group_id ON id
name_unique_idx_marker_group ON name
Foreign Keys
allele_source_id_fk_marker_group ( allele_source_id ) ref allele_donor (id)


Table marker_group_variant

Table to hold the relationship between marker group and variant table. This will enable a many-to-many relationship

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* marker_group_id integer
* variant_id integer
Indexes
pk_marker_group_variant_id ON id
Foreign Keys
marker_group_id_fk_marker_group_variant ( marker_group_id ) ref marker_group (id)
variant_id_fk_marker_group_variant ( variant_id ) ref variant (id)


Table platform
IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* name varchar( 100 ) Name of the platform
* abbrev varchar Abbreviation describing the platform
  supplier varchar Company than manufactures the platform (e.g. Illumina)
Indexes
pk_platform_id ON id
abbrev_unique_idx_platform ON abbrev
name_unique_idx_platform ON name


Table service_provider
IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
  company_name varchar( 100 ) Name of the service provider
  contact_name varchar( 100 ) Contact name of the provider is a person
  email_address varchar( 100 ) Email address to correspond to for the service
Indexes
pk_service_provider_id ON id


Table trait

The target trait which is influenced by the presence or absence of particular QTL(s).

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY Primary key of the table.
* name varchar( 100 ) Name of the trait. Must be unique across the system.
* abbrev varchar Abbreviation to describe the trait. Must be unique across the system.
Indexes
pk_trait_id ON id
abbrev_unique_idx_trait ON abbrev
name_unique_idx_trait ON name


Table trait_category

Category for each trait in the database. 2 or more trait categories can share the same traits.

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY Primary key of the table
* name varchar( 100 ) Name of the category. Must be unique within the system.
* abbrev varchar Abbreviation of the trait category. must be unique
  description varchar Description of the trait category.
Indexes
pk_trait_category_id ON id
abbrev_unique_idx_trait_category ON abbrev
name_unique_idx_trait_category ON name


Table trait_category_member

Table to hold the relationship between trait and trait_category. This will enable a many-to-many relationship

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table.
* trait_category_id integer
* trait_id integer
Indexes
pk_trait_category_member_id ON id
Foreign Keys
trait_id_fk_trait_category_member ( trait_id ) ref trait (id)
trait_category_id_fk_trait_category_member ( trait_category_id ) ref trait_category (id)


Table trait_marker_group

Table to hold the relatioship between trait and marker group. this will enable a many-to-many relationship

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* trait_id integer
* marker_group_id integer
Indexes
pk_trait_marker_group_id ON id
Foreign Keys
marker_group_id_fk_trait_marker_group ( marker_group_id ) ref marker_group (id)
trait_id_fk_trait_marker_group ( trait_id ) ref trait (id)


Table variant

Commonly referred to as a marker. Variants are used to detect the presence or absence of genes that are linked to the trait of interest.

IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY Primary key of the table.
* name varchar( 100 ) Name to describe the variant.
  type varchar The type of variant whether SNP or indel
  chromosome varchar Strand of DNA where the variant is located
  position varchar Physical position in bases where the variant is located
  genome varchar Reference genome where the position of the variant was based on
  reference_allele varchar Allele of reference genome
  alternate_allele varchar Allele alternate to the reference genome
  utility float8 Rate to which marker was used to make decision over all genotyped samples
  false_positive_rate float8 Rate to which an identified positive sample was actually a negative
  false_negative_rate float8 Rate to which an identified negative sample was actually a positive
  priority varchar If multiple variants within a marker group are used in an analysis, the highest priority  (1) will be prioritized over other variants (null or greater than 1)
  target_allele varchar Target source of allele, whether donor, recipient or unknown
Indexes
pk_variant_id ON id
name_unique_idx_variant ON name


Table vendor_platform
IdxField NameData TypeDescription
* id integer GENERATED BY DEFAULT AS IDENTITY primary key of the table
* name varchar( 100 ) Name of vendor
* service_provider_id integer
* platform_id integer
Indexes
pk_vendor_platform_id ON id
name_unique_idx_vendor_platform ON name
Foreign Keys
platform_id_fk_vendor_platform ( platform_id ) ref platform (id)
service_provider_id_fk_vendor_platform ( service_provider_id ) ref service_provider (id)