Database Schema

If you plan to use the database (especially running SQL queries), it is recommended that you understand the 'Database Schema' section below. You may also find the 'Example Queries' section to be useful.

Database Schema

This section describes the redundancy database tables and their relationships (the schema).

The goals of the schema are:

• to make analysis fast
• to make queries simple
• to provide flexibility to answer a wide range of questions

Tables

taxon

description: taxon proteome record

columns:

• id

protein

description: canonical protein records

columns:

• id
• sequence (notated as amino acid residues)
• mass

taxon_protein

description: records if taxon proteome X contains protein Y

columns:

• id
• taxon_id (foreign key to taxon.id)
• protein_id (foreign key to protein.id)
• metadata (metadata associated with the occurence of the protein, e.g. FASTA headers or NIH IDs)

peptide

description: canonical peptide records

columns:

• id
• sequence (in amino acid residues)
• mass

protease

description: protease records

columns:

• id
• cleavage_rule (rule for how this protease cleaves an amino acid sequence. Normally this is a regular expression)

digest

description: digest records columns:

• id
• protease_id (foreign key to protease.id)
• max_missed_cleavages
• min_acids (ignore any peptides with < this many acids)
• max_acids (ignore any peptides with > this many acids)

taxon_digest

description: records whether taxon proteome X has been digested with digest Y

columns:

• id
• taxon_id (foreign key to taxon.id)
• digest_id (foreign key to digest.id)

protein_digest

description: records whether protein X has been digested with digest Y

columns:

• id
• protein_id (foreign key to protein.id)
• digest_id (foreign key to digest.id)

protein_digest_peptide

description: records if petide X is a product of protein_digest Y

columns:

• id
• peptide_id (foreign key to peptide.id)
• protein_digest_id (foreign key to protein_digest.id)
• count (# of times peptide X appeared in protein_digest Y)

protein_digest_peptide

description: records if petide X is a product of taxon_digest Y

columns:

• id
• peptide_id (foreign key to peptide.id)
• taxon_digest_id (foreign key to taxon_digest.id)
• count (# of times peptide X appeared in taxon_digest Y)