New SPAR tables explained

Support the user

1. favourite_activity table
2. seedlot_registration_a_class_save table
3. user_profile table

Code Tables

1. cone_collection_method_code table
2. gametic_methodology_list table
3. genetic_class_code table
4. genetic_worth_code table
5. method_of_payment_code table
6. seedlot_source_list table
7. seedlot_status_list table

New Orchard x SPU Relationship (one-to-one)

1. active_orchard_spu table

Seedlot Tables

1. seedlot

• Main table for a Seedlot;
• Created when the users clicks the "Create seedlot number" button;
• Updated when the user submit or modify the seedlot form;

2. seedlot_collection_method

• Holds all collection methods selected by the user in the Seedlot registration form step 1 to a particular seedlot;
• I.e.: Aerial clipping/topping, Climbing, felled trees;
• This is a one-to-many relationship. One Seedlot can have multiple collection methods;

3. seedlot_owner_quantity

• Seedlot x Owner relationship
• Holds one or more Ownership tied to a particular seedlot.
• This is a one-to-many relationship. One seedlot can have multiple owners.
• This table is populated with information provided by the user on the Seedlot registration form step 2;
• This table is populated when the user submits the form or modifies it;

4. seedlot_orchard

• Seedlot x Orchard relationship
• Holds all Orchard ID to a particular seedlot;
• This is a one-to-one relationship. One seedlot can have only one Orchard ID connection;
• This table is populated when the user submits the form or modifies it;
• This table is populated with information provided by the user on the Seedlot registration form step 4;

5. seedlot_parent_tree

• Seedlot x Parent Tree relationship
• Holds information for each parent tree related to a seedlot;
• They are:
  • parent_tree_id the parent tree ID;
  • cone_count the number of cones counted or estimated from the Parent Tree. Populated from the user's CSV;
  • pollen_count the number of pollen counted or estimated from the Parent Tree. Populated from the user's CSV;
  • smp_success_pct the estimated success (percent) of the supplemental mass pollination mix on the Parent Trees. Populated from the user's CSV;
  • non_orchard_pollen_contam_pct the percentage of non-orchard pollen contamination. Populated from the user's CSV;
• This table is populated with information provided by the user's CSV on the Seedlot registration form step 5;
• This table is populated when the user submits the form or modifies it;

6. seedlot_parent_tree_gen_qlty

• Seedlot x Parent Tree x Genetic Quality breeding value relationship
• Holds information for each parent tree related to a seedlot;
• All parent trees that contributed to the seedlot Genetic Quality;
• All parent trees in this table should be INSIDE the Orchard;
• All parent trees present in the Seedlot registration form step 5, tabs 1 and 2.
• This table stores the same information as of Oracle. They are being replicated for ETL purposes;
• They are:
  • parent_tree_id the parent tree ID;
  • genetic_type_code comparative measure of genetic value. It cab ne one of: BV (Breeding Value) and CV (Clonal Value).
  • genetic_worth_code trait code related to the GENETIC VALUE. E.g.: WWD, GVO.
  • genetic_quality_value The Genetic Quality breeding value based on the test assessment for a Parent Tree.
• PS: both columns estimated_ind and untested_ind are not displayed to the user.
• This table is populated with information from the Oracle database, when the user fills in the Orchard ID;
• This table is populated when the user submits the form or modifies it;

7. smp_mix

• Seedlot x Parent Tree x SMP Mix relationship
• Holds information for each parent tree related to a seedlot;
• All parent trees that contributed to the seedlot SMP Mix;
• All parent trees present in the Seedlot registration form step 5, tab 3.
• It may contains parent tree OUTSIDE the Orchard;
• This tab (tab 3) is filled manually by the user.
• Columns:
  • parent_tree_id filled by the user.
  • amount_of_material filled by the user.
  • proportion calculated after any changes in this table.
• This table is populated when the user submits the form or modifies it;

8. smp_mix_gen_qlty

• Seedlot x Parent Tree x SMP Mix breeding value relationship
• Holds information for each parent tree related to a seedlot;
• All parent trees that contributed to the seedlot SMP Mix;
• It may contains parent tree OUTSIDE the Orchard;
• This table stores the same information as of Oracle. They are being replicated for ETL purposes;
• They are:
  • parent_tree_id the parent tree ID;
  • genetic_type_code comparative measure of genetic value. It cab ne one of: BV (Breeding Value) and CV (Clonal Value).
  • genetic_worth_code trait code related to the SMP MIX VALUE. E.g.: WWD, GVO.
  • genetic_quality_value The SMP Mix breeding value based on the test assessment for a Parent Tree.

9. seedlot_parent_tree_smp_mix

• Seedlot x Parent Tree x SMP Mix breeding value relationship
• Holds information for each parent tree related to a seedlot;
• All parent trees that contributed to the seedlot SMP Mix;
• All parent trees in this table should be INSIDE the Orchard;
• All parent trees present in the Seedlot registration form step 5, tab 3.
• This table stores the same information as of Oracle. They are being replicated for ETL purposes;
• They are:
  • parent_tree_id the parent tree ID;
  • genetic_type_code comparative measure of genetic value. It cab ne one of: BV (Breeding Value) and CV (Clonal Value).
  • genetic_worth_code trait code related to the SMP MIX VALUE. E.g.: WWD, GVO.
  • genetic_quality_value The SMP Mix breeding value based on the test assessment for a Parent Tree.
• This table is populated with information from the Oracle database, when the user fills in the Orchard ID;
• This table is populated when the user submits the form or modifies it;

10. seedlot_genetic_worth

• Holds all breeding values for the Genetic Worth to a particular seedlot, given its species;
• This table stores calculated values for the seedlot, Genetic Quality breeding values for each trait, from tabs 1 and 2;
• The calculation happens once the user submit the CSV with pollen and cone count and hits the "Calculate Genetic worth and Effective populations values" button;
• This table is populated when the user submits the form or modifies it, not after the calculations;

11. seedlot_smp_mix

• Holds all breeding values for the SMP Mix to a particular seedlot, given its species;
• This table stores calculated values for the seedlot, SMP Mix breeding values for each trait, from tab 3;
• The calculation happens once the user submit the CSV with pollen and cone count and hits the "Calculate Genetic worth and Effective populations values" button;
  • Where's this button? Maybe we need to created it!?
• This table is populated when the user submits the form or modifies it, not after the calculations;

12. seedlot_seed_plan_zone

• Hold the Seedlot code describing the Seedlot Planning Zone;
• This table is populated automatically when the user submits the form or modifies it;
• This table value it's not displayed to the user;