Database Properties
Database Type: PostgreSQL - 13.1 (Debian 13.1-1.pgdg100+1)
Tables
| Table / View | Children | Parents | Columns | Rows | Type | Comments |
|---|---|---|---|---|---|---|
| observation_requested_subzones | 0 | 2 | 2 | 0 | Table | If an observation should only cover a specific set of subzones, the subzone IDs are stored here. If an observation is of the entire site (the default), there will be no rows for that observation in this table. |
| observation_plot_statuses | 1 | 0 | 2 | 4 | Table | (Enum) The status of an observation plot. |
| planting_site_populations | 0 | 2 | 4 | 0 | Table | Total number of plants of each species in each planting site. |
| recorded_species_certainties | 4 | 0 | 2 | 3 | Table | (Enum) Levels of certainty about the identity of a species recorded in a monitoring plot observation. |
| planting_types | 1 | 0 | 2 | 4 | Table | (Enum) Type of planting associated with a delivery. Different planting types distinguish reassignments from initial plantings. |
| planting_subzone_populations | 0 | 2 | 4 | 0 | Table | Total number of plants of each species in each subzone. |
| observation_plots | 6 | 8 | 15 | 0 | Table | Information about monitoring plots that are required to be surveyed as part of observations. This is not populated until the scheduled start time of the observation. |
| monitoring_plots | 9 | 4 | 14 | 0 | Table | Regions within planting subzones that can be comprehensively surveyed in order to extrapolate results for the entire zone. Any monitoring plot in a subzone is expected to have roughly the same number of plants of the same species as any other monitoring plot in the same subzone. |
| planting_subzone_histories | 1 | 2 | 6 | 0 | Table | Versions of planting subzone maps over time. Each time a planting site map changes, its subzones’ maps are inserted into this table. |
| plantings | 0 | 10 | 10 | 0 | Table | Details about plants that were planted or reassigned as part of a delivery. There is one plantings row per species in a delivery. |
| observed_plot_species_totals | 0 | 4 | 11 | 0 | Table | Aggregated per-monitoring-plot, per-species totals of plants recorded during observations. |
| observation_plot_positions | 2 | 0 | 2 | 4 | Table | (Enum) Positions in a monitoring plot where users can take photos or record coordinates. |
| planting_seasons | 0 | 1 | 7 | 0 | Table | Start and end dates of planting seasons for planting sites. |
| observed_zone_species_totals | 0 | 4 | 11 | 0 | Table | Aggregated per-planting-zone, per-species totals of plants recorded during observations. |
| planting_subzones | 8 | 6 | 12 | 0 | Table | Regions within planting zones that are a convenient size for a planting operation. Typically <10Ha. |
| planting_site_histories | 2 | 2 | 7 | 0 | Table | Versions of planting site maps over time. Each time a planting site map changes, the new map is inserted into this table and its child tables. |
| monitoring_plot_overlaps | 0 | 2 | 2 | 0 | Table | Which monitoring plots overlap with previously-used monitoring plots. A plot may overlap with multiple older or newer plots. |
| planting_sites | 12 | 6 | 15 | 0 | Table | Top-level information about entire planting sites. Every planting site has at least one planting zone. |
| recorded_plants | 0 | 7 | 8 | 0 | Table | Information about individual plants observed in monitoring plots. |
| observed_plot_coordinates | 0 | 5 | 5 | 0 | Table | Observed GPS coordinates in monitoring plots. Does not include photo coordinates or coordinates of recorded plants. |
| observations | 9 | 2 | 8 | 0 | Table | Scheduled observations of planting sites. This table may contain rows describing future observations as well as current and past ones. |
| recorded_plant_statuses | 1 | 0 | 2 | 3 | Table | (Enum) Possible statuses of a plant recorded during observation of a monitoring plot. |
| observed_site_species_totals | 0 | 4 | 11 | 0 | Table | Aggregated per-planting-site, per-species totals of plants recorded during observations. |
| observable_conditions | 1 | 0 | 2 | 7 | Table | (Enum) Conditions that can be observed in a monitoring plot. |
| planting_site_notifications | 0 | 2 | 5 | 0 | Table | Tracks which notifications have already been sent regarding planting sites. |
| observation_plot_conditions | 0 | 3 | 3 | 0 | Table | List of conditions observed in each monitoring plot. |
| deliveries | 3 | 5 | 9 | 0 | Table | Incoming deliveries of new seedlings to a planting site. Mostly exists to link plantings and nursery withdrawals. |
| planting_zones | 6 | 3 | 16 | 0 | Table | Regions within planting sites that have a consistent set of conditions such that survey results from any part of the zone can be extrapolated to the entire zone. Planting zones are subdivided into plots. Every planting zone has at least one plot. |
| observation_photos | 0 | 6 | 5 | 0 | Table | Observation-specific details about a photo of a monitoring plot. Generic metadata is in the |
| planting_zone_populations | 0 | 2 | 4 | 0 | Table | Total number of plants of each species in each zone. |
| draft_planting_sites | 0 | 7 | 13 | 0 | Table | Details of planting sites that are in the process of being defined. |
| observation_states | 1 | 0 | 2 | 5 | Table | (Enum) Where in the observation lifecycle a particular observation is. |
| planting_zone_histories | 1 | 2 | 5 | 0 | Table | Versions of planting zone maps over time. Each time a planting site map changes, its zones’ maps are inserted into this table. |
| monitoring_plot_histories | 1 | 6 | 10 | 0 | Table | Versions of monitoring plots over time. Each time a planting site changes, its monitoring plots are added to this table. |