Navigating the Database #

Last updated on 2026-10-09

The tables in the AKVEG Database can be broadly classified into data tables, reference tables, and metadata tables.

Data tables contain information on vegetation, environmental, and soils characteristics, as well as information about the sites at which these data were collected (e.g., coordinates, dimensions).

Reference tables are referenced by the data tables to constrain the set of allowable values in a field. For example, the plot_dimensions table specifies the allowable plot dimensions that can be used in the plot_dimensions_m field of the Site Visit table; no other values are allowed. Most categorical fields in the AKVEG Database are constrained by reference tables.

Metadata tables contain information about the database itself:

• database_schema: Information on the structure of the database, including field descriptions, relationships between tables, and specification of null values.
• database_dictionary: Controlled vocabulary and definitions for all constrained values.
• database_version: Lists the current version of the database and logs all updates, fixes, and modifications made to the database over time.
• project_citations: Links project codes to companion publications, reports, or source datasets.

We provide ready-made SQL queries for all data tables and most metadata tables that resolve foreign keys into human-readable values.

Overview #

There are 13 data tables in the AKVEG Database:

Project & Site Schema #

Below is a simplified diagram that illustrates how the Project, Site, and Site Visit tables are related to each other.

The site_visit_code, first specified in the Site Visit table, is the field that all subsequent tables (Tables 5-14) refer to. In contrast, the Site and Project tables are not referred to beyond the first three tables. You will likely want to consult the Site and Project tables to obtain coordinates and plot dimensions (Site table) and for general information on the projects that the data belong to (Project table).

erDiagram
direction RL
project {
varchar project_code
}
site {
varchar establishing_project_code
varchar site_code

    }
    site_visit {
        varchar project_code
        varchar site_code
        varchar site_visit_code
    }
    site_visit }o--|| project : " "
    site_visit }o--|| site : " "
    site }o--|| project : ""

Project Table #

The Project table provides information about the overarching projects that the data were collected under. This table includes information on the organization(s) that collected the data (originator_id) and funded the project (funder_id), the years during which the data collection took place (year_start, year_end), a short description of the project’s purpose (project_description), and whether the data is publicly available (private). This table also includes information about how (source_type) and when (source_date, acquisition_date) the data were acquired.

Each project code is associated with one or more publications, reports, or source datasets. Bibliographic references for these sources can be accessed via the project_citations table.

This table references the following metadata tables: organization, personnel, completion, and source_type.

erDiagram
direction TB
project {
varchar project_code PK
varchar project_name UK
smallint originator_id FK
smallint funder_id FK
smallint manager_id FK
smallint completion_id FK
smallint year_start
smallint year_end
varchar project_description
boolean private
smallint source_type_id FK
date source_date
date acquisition_date

}
project }o--|| organization : originator_id
project }o--|| organization : funder_id
project }o--|| personnel : manager_id
project }o--|| completion : completion_id
project }o--|| source_type : source_type_id

Site Table #

The Site table provides information about the site at which data were collected. In addition to the site_code, which is used to identify unique sites, the table contains information on:

• Perspective (ground or aerial)
• Cover method (e.g., visual estimate, line-point intercept)
• Plot coordinates
• Plot dimensions

All fields except for the site_code field refer to constrained values in the Project table or in the following metadata tables: perspective, cover_method, plot_dimensions, h_datum, positional_accuracy, location_type. There is only one possible value for h_datum_epsg: 4269 (NAD83). All coordinates are converted to this datum prior to ingestion in the database.

Open Site table in fullscreen for larger viewing.

erDiagram
direction TB
site {
varchar site_code PK
varchar establishing_project_code FK
smallint perspective_id FK
smallint cover_method_id FK
smallint plot_dimensions_id FK
integer h_datum_epsg FK
decimal latitude_dd
decimal longitude_dd
decimal h_error_m
smallint positional_accuracy_id FK
smallint location_type_id FK
}
site }o--|| project : establishing_project_code
site }o--|| perspective : perspective_id
site }o--|| cover_method : cover_method_id
site }o--|| plot_dimensions : plot_dimensions_id
site }o--|| h_datum : h_datum_epsg
site }o--|| positional_accuracy : positional_accuracy_id
site }o--|| location_type : location_type_id

Site Visit Table #

The Site Visit table contains the site_visit_code, which identifies unique site visits (i.e., an individual sampling event conducted at a site on a specific date). This code is used as a foreign key in all subsequent tables (Tables 5-14). The Site Visit table also includes information on the taxonomic scope of the survey, the date of the site visit, the personnel that were involved, and the dominant vegetation class at the time of the visit.

To simplify the diagram, we do not show all of the relationships between the Site Visit table and the Scope and Personnel tables. Fields with the ‘scope’ prefix refer to the scope table. Fields that refer to people (i.e., that include the word ‘observer’ or ‘recorder’) refer to the personnel table.

erDiagram
direction TB
site_visit {
varchar site_visit_code PK
varchar project_code FK
varchar site_code FK
smallint data_tier_id FK
smallint scope_vascular_id FK
smallint scope_bryophyte_id FK
smallint scope_lichen_id FK
varchar observe_date
smallint veg_observer_id FK
smallint veg_recorder_id FK
smallint env_observer_id FK
smallint soils_observer_id FK
varchar structural_class_code FK
boolean homogeneous
}
site_visit }o--|| project : project_code
site_visit }o--|| site : site_code
site_visit }o--|| data_tier : data_tier_id
site_visit }o--|| scope : scope_vascular_id
site_visit }o--|| personnel : veg_observer_id
site_visit }o--|| structural_class : structural_class_code

Cover Tables #

Vegetation Cover #

The Vegetation Cover table contains data on the vegetation (vascular plants, bryophytes, and lichens) encountered during site visits. Both the original scientific name recorded in the source data (name_original) and the taxonomic code referring to the resolved scientific name (code_adjudicated) are provided.

There are two instances where a site visit will contain multiple entries for the same taxonomic code:

• The taxon was encountered in both ‘dead’ and ’live’ forms. In these cases, the taxon will have two entries that will differ on the basis of dead_status.
• Different original names were recorded that now refer to the same accepted name. In these cases, the taxon will have two entries that will differ on the basis of name_original.

To retrieve the descriptive names of each cover‑type method, join the Vegetation Cover table with the cover_type table on cover_type_id. To retrieve the scientific name associated with the taxonomic code, join the Vegetation Cover table with taxon_all on code_adjudicated. To retrieve the currently accepted scientific names, join taxon_all with taxon_accepted on taxon_accepted_code. Refer to the Vegetation Cover SQL query for a ready-made implementation.

erDiagram
direction TB
vegetation_cover {
serial vegetation_cover_id PK
varchar site_visit_code FK
smallint cover_type_id FK
varchar name_original
varchar code_adjudicated FK
decimal cover_percent
boolean dead_status
}
vegetation_cover }o--|| site_visit : site_visit_code
vegetation_cover }o--|| cover_type : cover_type_id
vegetation_cover }o--|| taxon_all : code_adjudicated

Abiotic Top Cover #

The Abiotic Top Cover table records the percent top cover for each abiotic element class within a plot. Abiotic top cover is defined as the proportional area covered by the abiotic element and not overtopped by any vegetation (i.e., visible from an aerial perspective). The sum of all abiotic top cover measurements within a plot cannot exceed 100%.

The abiotic element code references the ground_element table because the abiotic elements are a subset of ground elements. Join the abiotic_top_cover table with ground_element to obtain the descriptive names of the abiotic/ground element codes. Refer to the Abiotic Top Cover SQL query for a ready-made implementation.

erDiagram
direction LR
abiotic_top_cover {
serial abiotic_cover_id PK
varchar site_visit_code FK
varchar abiotic_element_code FK
decimal abiotic_top_cover_percent
}
abiotic_top_cover }o--|| site_visit : site_visit_code
abiotic_top_cover }o--|| ground_element : abiotic_element_code

Whole Tussock Cover #

The Whole Tussock Cover table records the proportional ground area covered by tussock structures within each plot. Whole tussock cover includes both live and dead plant material and the tussock base. It can be reported as either absolute canopy cover or top canopy cover.

Join the whole_tussock_cover table with cover_type on cover_type_id to retrieve the descriptive names of each cover‑type method. Refer to the Whole Tussock Cover SQL query for a ready-made implementation.

erDiagram
direction LR
whole_tussock_cover {
serial tussock_id PK
varchar site_visit_code FK
smallint cover_type_id FK
decimal cover_percent
}
whole_tussock_cover }o--|| site_visit : site_visit_code
whole_tussock_cover }o--|| cover_type : cover_type_id

Ground Cover #

The Ground Cover table records the percent cover for each ground element within a plot. Ground cover percent is defined as the proportional area of ground covered by the ground element at the soil surface. The sum of all ground cover measurements must equal 100%.

Join the ground_cover table with ground_element on ground_element_code to retrieve the descriptive names of each ground element. Refer to the Ground Cover SQL query for a ready-made implementation.

erDiagram
direction LR
ground_cover {
serial ground_cover_id PK
varchar site_visit_code FK
varchar ground_element_code FK
decimal ground_cover_percent
}
ground_cover }o--|| site_visit : site_visit_code
ground_cover }o--|| ground_element : ground_element_code

Structure Tables #

Structural Group Cover #

The Structural Group Cover table records the percent cover of each structural group within a plot. Cover type must be either absolute foliar cover or absolute canopy cover. Top cover types are invalid because measurements represent vegetation structure, which includes multiple canopy strata - not just the top stratum.

Join the structural_group_cover table with cover_type on cover_type_id to retrieve the descriptive names of each cover‑type method. Join structural_group_cover with structural_group on structural_group_id to retrieve the descriptive names of each structural group element. Refer to the Structural Group SQL query for a ready-made implementation.

erDiagram
direction TB
structural_group_cover {
serial structural_cover_id PK
varchar site_visit_code FK
smallint cover_type_id FK
smallint structural_group_id FK
decimal cover_percent
}

structural_group_cover }o--|| site_visit : site_visit_code
structural_group_cover }o--|| cover_type : cover_type_id
structural_group_cover }o--|| structural_group : structural_group_id

Tree Structure #

The Tree Structure table records characteristics for tree species that occur within a plot and within a crown class stratum. Top cover types are invalid for this table as the measurements represent woody vegetation structure across all strata, not just the top stratum.

Note: As of 2026-10-09, there are no records in the Tree Structure table.

erDiagram
direction TB
tree_structure {
serial tree_structure_id PK
varchar site_visit_code FK
varchar name_original
varchar code_adjudicated FK
smallint crown_class_id FK
smallint height_type_id FK
decimal height_cm
smallint cover_type_id FK
decimal cover_percent
decimal mean_dbh_cm
smallint number_stems
smallint mean_tree_age_y
decimal tree_subplot_area_m2
}

tree_structure }o--|| site_visit : site_visit_code
tree_structure }o--|| taxon_all : code_adjudicated
tree_structure }o--|| crown_class : crown_class_id
tree_structure }o--|| height_type : height_type_id
tree_structure }o..|| cover_type : cover_type_id

Shrub Structure #

The Shrub Structure table records characteristics for shrub species that occur within a plot and within a shrub class stratum. Top cover types are invalid for this table as the measurements represent woody vegetation structure across all strata, rather than just the top stratum.

Refer to the Shrub Structure SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the shrub structure table.

erDiagram
direction TB
shrub_structure {
  serial shrub_structure_id PK
  varchar site_visit_code FK
  varchar name_original
  varchar code_adjudicated FK
  smallint shrub_class_id FK
  smallint height_type_id FK
  decimal height_cm
  smallint cover_type_id FK
  decimal cover_percent
  decimal mean_diameter_cm
  smallint number_stems
  decimal shrub_subplot_area_m2
}

shrub_structure }o--|| site_visit : site_visit_code
shrub_structure }o--|| taxon_all : code_adjudicated
shrub_structure }o--|| shrub_class : shrub_class_id
shrub_structure }o--|| height_type : height_type_id
shrub_structure }o..|| cover_type : cover_type_id

Environment Table #

The Environment table records various environmental characteristics of a plot at the time of the site visit. Data on a site visit’s physical geography, soil-hydrologic regime, and primary disturbance process are included.

Here, we show a simplified diagram of the environment table that omits most of the relationships between this table and the reference tables. The omitted relationships are straightforward: Fields that end in _id are foreign keys. The reference table has the same name as the field, minus the _id suffix. For example, the microtopography_id refers to the microtopography table, while the disturbance_severity_id refers to the disturbance_severity table. The join field in the reference table is the same as the foreign key. Refer the Environment SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the environment table. You can also view all relationships by opening the Environment table in fullscreen.

erDiagram
direction LR
  environment {
    serial environment_id PK
    varchar site_visit_code FK
    smallint physiography_id FK
    smallint geomorphology_id FK
    smallint macrotopography_id FK
    smallint microtopography_id FK
    decimal microrelief_cm
    smallint drainage_id FK
    smallint moisture_id FK
    decimal depth_water_cm
    decimal depth_moss_duff_cm
    decimal depth_restrictive_layer_cm
    smallint restrictive_type_id FK
    smallint disturbance_id FK
    smallint disturbance_severity_id FK
    smallint disturbance_time_y
    boolean surface_water
    smallint soil_class_id FK
    boolean cryoturbation
    varchar dominant_texture_40_cm_code FK
    decimal depth_15_percent_coarse_fragments_cm
    }

    environment }o--|| site_visit : site_visit_code
    environment }o--|| physiography : physiography_id
    environment }o--|| restrictive_type : restrictive_type_id
    environment }o..|| soil_texture : dominant_texture_40_cm_code

Soils Tables #

Soil Metrics #

The Soil Metrics table records measurements on pH, conductivity, and temperature within a plot. Where the water table is at or above the soil surface, the water can be measured instead. In these cases, the value of the water_measurement field is True.

Refer to the Soil Metrics SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the soil metrics table.

erDiagram
direction TB
  soil_metrics {
    serial soil_metric_id PK
    varchar site_visit_code FK
    boolean water_measurement
    decimal measure_depth_cm
    decimal ph
    decimal conductivity_mus
    decimal temperature_deg_c
    }

soil_metrics }o--|| site_visit : site_visit_code

Soil Horizons #

The Soil Horizons table records a soil horizon’s position and its physical characteristics within a plot. Both quantitative and qualitative data are recorded, including horizon depth, thickness, color, and texture. Measurements on pH, conductivity, and temperature are included in the Soil Metrics table, rather than the Soil Horizons table. While soils data are included in AKVEG, users who are particularly interested in soils characteristics may wish to consult the Alaska Soil Databank.

Below, we show a simplified diagram of the environment table that omits most of the relationships between this table and the reference tables. The horizon_primary_code and horizon_secondary_code both refer to the soil_horizon_type table. Fields starting with horizon_suffix refer to the soil_horizon_suffix table. The texture_code and structure_code foreign keys refer to the soil_texture and soil_structure tables, respectively.

Refer to the Soil Horizons SQL query for a ready‑to‑use statement that joins the related foreign‑key tables to the soil horizons table. You can also view all relationships by opening the Soil Horizons table in fullscreen.

erDiagram
direction LR

  soil_horizons {
        serial soil_horizon_id PK
        varchar site_visit_code FK
        smallint horizon_order
        decimal thickness_cm
        decimal depth_upper_cm
        decimal depth_lower_cm
        boolean depth_extend
        varchar horizon_primary_code FK
        varchar horizon_suffix_1_code FK
        varchar horizon_suffix_2_code FK
        varchar horizon_secondary_code FK
        varchar horizon_suffix_3_code FK
        varchar horizon_suffix_4_code FK
        varchar texture_code FK
        decimal clay_percent
        decimal total_coarse_fragment_percent
        decimal gravel_percent
        decimal cobble_percent
        decimal stone_percent
        decimal boulder_percent
        varchar structure_code FK
        varchar matrix_hue_code FK
        decimal matrix_value
        smallint matrix_chroma
        varchar nonmatrix_feature_code FK
        varchar nonmatrix_hue_code FK
        decimal nonmatrix_value
        smallint nonmatrix_chroma
    }

    soil_horizons }o--|| site_visit : site_visit_code
    soil_horizons }o..|| soil_horizon_type : horizon_primary_code
    soil_horizons }o..|| soil_horizon_suffix : horizon_suffix_1_code
    soil_horizons }o..|| soil_hue : matrix_hue_code
    soil_horizons }o..|| soil_nonmatrix_features : nonmatrix_feature_code
    soil_horizons }o..|| soil_hue : nonmatrix_hue_code