ap_03_database_design.qmd

# Data model {#sec-erds}

```{r}
library(dplyr)
library(tibble)
library(gt)
library(gtExtras)
```

The following diagrams demonstrate one way in which the contents of this manual can be structured in a database. The diagrams only deal with the data themselves - security, user management, change tracking and other helper functionalities are left out for clarity. Data tables for samples and laboratory results are also not included. The model is optimised to highlight the relationships between the various data points and contains some structures that support the controlled vocabulary lists, but is not optimised for performance. 

## Conceptual model

This overview shows the relationships between each part of the standard.

```{mermaid}
%%| fig-cap: "Overview - conceptual relationships"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  Site ||--|| Location : "has one"
  Site ||--|| Landform : "is in one"
  Site ||--|| Landscape : "is in one"
  Landscape ||--|{ Landform : "comprises at least one"
  Site ||--o{ "Recent events" : "may have some"
  Site ||--|| "Site surface" : "has one"
  "Site surface" ||--|{ "Surface cover" : "has"
  "Site surface" ||--o{ Erosion : "may have some"
  "Site surface" ||--o{ Deposition : "may have some"
  Site ||--|{ Horizon : "has at least one"
  Horizon ||--o{ "Peds" : "may contain"
  Horizon ||--o{ "Rock Fragments" : "may contain"
  Horizon ||--o{ "Other Fragments" : "may contain"
  Horizon ||--o{ "Artefacts" : "may contain"
  Horizon ||--o{ "Roots" : "may contain"
  Horizon ||--o{ "Voids" : "may contain"
  Horizon ||--o{ "Pores" : "may contain"
  Horizon ||--|{ "Matrix colour" : "has one or more"
  Horizon ||--o{ "Colour patterns" : "may have"
  Horizon ||--|| "Texture" : "has a"
  Horizon ||--|| "Particle size distribution (estimate)" : "has a"
  Horizon ||--o{ "Concentrations" : "may have some"
  Horizon ||--o{ "Coatings" : "may have some"
  Horizon ||--o{ "Pan" : "may be a"
  Horizon ||--o{ "Stress Features" : "may have some"
  Horizon ||--o{ "Biological features" : "may have some"
  Horizon ||--o{ "Consistence tests" : "may have some"
  Site ||--o{ "Field tests" : "may have some"
  Site ||--|{ "NZ Soil Classification" : "has an"
  Horizon ||--|| "Horizon Names" : "have"
  Horizon ||--|| "Functional Horizon Names" : "have"

```

## Soil Setting {#sec-erd-setting}

The setting tables store site location (@sec-loc), landscape and landform (@sec-gmph), land cover (@sec-lcov), and land use (@sec-luse).

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Setting - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site ||--|| location : "has one"
  location }|--|{ elevation_measurement_methods : "code comes from"
  location }|--|{ gnss_types : "code comes from"
  site ||--|| landscape : "has one associated"
  landscape }|--|{ provinces : "code comes from"
  landscape }|--|{ landscapes : "code comes from"
  landscape ||--|{ landform : "contains at least one"
  landscape ||--o{ lscape_landuse : "may have some"
  lscape_landuse }|--|{ landuse_primary : "code comes from"
  lscape_landuse }|--|{ landuse_secondary : "code comes from"
  landform }|--|{ landform_types : "code comes from"
  landform }|--|{ measurement_types : "code comes from"
  landform }|--|{ landform_elements : "code comes from"
  landform }|--|{ landform_element_vmods : "code comes from"
  landform }|--|{ landform_element_hmods : "code comes from"
  landform ||--o{ lform_landuse : "may have some"
  landform }|--|{ land_cover_types : "code comes from"
  lform_landuse }|--|{ landuse_primary : "code comes from"
  lform_landuse }|--|{ landuse_secondary : "code comes from"
  lform_landuse }|--|{ landuse_tertiary : "code comes from"
  lform_landuse }|--|{ landuse_impact : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Setting - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site ||--|| location : "has one"
  location }|--|{ elevation_measurement_methods : "code comes from"
  location }|--|{ gnss_types : "code comes from"
  site ||--|| landscape : "has one associated"
  landscape }|--|{ provinces : "code comes from"
  landscape }|--|{ landscapes : "code comes from"
  landscape ||--|{ landform : "contains at least one"
  landscape ||--o{ lscape_landuse : "may have some"
  lscape_landuse }|--|{ landuse_primary : "code comes from"
  lscape_landuse }|--|{ landuse_secondary : "code comes from"
  landform }|--|{ landform_types : "code comes from"
  landform }|--|{ measurement_types : "code comes from"
  landform }|--|{ landform_elements : "code comes from"
  landform }|--|{ landform_element_vmods : "code comes from"
  landform }|--|{ landform_element_hmods : "code comes from"
  landform ||--o{ lform_landuse : "may have some"
  landform }|--|{ land_cover_types : "code comes from"
  lform_landuse }|--|{ landuse_primary : "code comes from"
  lform_landuse }|--|{ landuse_secondary : "code comes from"
  lform_landuse }|--|{ landuse_tertiary : "code comes from"
  lform_landuse }|--|{ landuse_impact : "code comes from"
  
  %% data %%
  location {
    id           id_site PK                  
    geo          geo_point_site_xy        "Section 3.1.1"
    num[-5-3724] amt_elevation_abs_m      "Section 3.1.1"
    num[0-Inf]   amt_error_location_m     "Section 3.1.2"
    num[0-Inf]   amt_error_elevation_m    "Section 3.1.2"
    cat          catu_elevation_method FK "Section 3.1.3"
    cat          catu_gnss FK             "Section 3.1.4"
    num[-5-3724] amt_elevation_rel_m      "Section 3.2.1"
    txt          txt_notes_triangulation  "Section 3.2.2"
    txt          txt_location_desc        "Section 3.2.3"
    %% not defining code tables for these just now %%
    cat          catu_admin_rc            "Section 3.2.4"
    cat          catu_admin_dc            "Section 3.2.4"
  }
  
  elevation_measurement_methods {
    id           id_elevation_method PK
    cat          catu_elevation_method FK  "Table 3.1"
    txt          txt_elevation_method_name "Table 3.1"
  }

  gnss_types {
    id           id_gnss PK
    cat          catu_gnss FK              "Table 3.2"
    txt          txt_gnss_name             "Table 3.2"
    txt          txt_gnss_desc             "Table 3.2"
  }  
  
  %% data %%
  landscape {
    id           id_site PK
    cat          catu_province FK          "Section 4.1"
    cat          catu_landscape FK         "Section 4.2.1.4"
    %% landscape relief %%
    num[0-Inf]   amt_lscape_relief_med_m   "Section 4.2.1.1"
    %% landscape slope %%
    num[0-90]    amt_lscape_slope_med_d    "Section 4.2.1.2"
    num[0-90]    amt_lscape_slope_min_d    "Section 4.2.1.2"
    num[0-90]    amt_lscape_slope_max_d    "Section 4.2.1.2"
    %% not implementing stream channel stuff here %%
  }

  provinces {
    id           id_province PK
    cat          catu_province FK          "Table 4.1"
    txt          txt_province_name         "Table 4.1"
    txt          txt_province_desc         "Note 4.1"
  }
  
  landscapes {
    id           id_landscape PK
    cat          catu_landscape FK         "Table 4.2"
    txt          txt_landscape_name        "Table 4.2"
    txt          txt_landscape_desc        "Table 4.2"
  }
  
  %% data %%
  lscape_landuse {
    id           id_site PK
    id           id_landscape PK
    id           id_lscape_landuse PK
    cat          catu_lscape_landuse_primary FK    "Section 8.2.1"
    cat          catu_lscape_landuse_secondary FK  "Section 8.2.1"
    num[0-100]   amt_landuse_area_p                "Section 8.2.1"
  }

  landuse_primary {
    id           id_landuse_primary PK
    cat          catu_landuse_primary FK         "Fig. 8.1"
    txt          txt_landuse_primary_name        "Fig. 8.1"
  }

  landuse_secondary {
    id           id_landuse_primary PK
    id           id_landuse_secondary PK
    cat          catu_landuse_secondary FK       "Fig. 8.1"
    txt          txt_landuse_secondary_name      "Fig. 8.1"
  }

  %% data %%
  landform {
    id           id_site PK
    id           id_landscape PK
    id           id_landform PK
    cat          catu_landform FK                "Section 4.3.1"
    num[0-100]   amt_landform_p                  "Section 4.1.2.5"
    %% can only be true for one landform per site/landscape: %%
    bool         ind_landform_contains_site  
    %% (other way is separate tables for site-landform and    %%
    %% landscape-landforms)                                   %%
    cat          catu_land_cover FK              "Section 8.1"
    %% detailed optional stuff: %%
    num[0-Inf]   amt_landform_relief_m           "Section 4.3.2.1"
    num[0-90]    amt_landform_slope_med_d        "Section 4.3.2.2"
    num[0-90]    amt_landform_slope_min_d        "Section 4.3.2.2"
    num[0-90]    amt_landform_slope_max_d        "Section 4.3.2.2"
    num[0-359]   val_landform_aspect_med_d       "Section 4.3.2.3"
    num[0-359]   val_landform_aspect_min_d       "Section 4.3.2.3"
    num[0-359]   val_landform_aspect_max_d       "Section 4.3.2.3"
    cat          catu_landform_aspect_rec FK     "Section D.13.1"
    cat          catu_landform_element FK        "Section 4.3.2.4"
    cat          catu_landform_element_vmod FK   "Section 4.3.2.4"
    cat          catu_landform_element_hmod FK   "Section 4.3.2.4"
    %% extremely optional %%
    num[0-Inf]   amt_slope_length_m         "Section 4.4.1"
    num[-90-90]  amt_mesoscale_topo_index   "Section 4.4.2"
    num[-90-90]  amt_terrain_shape_index    "Section 4.4.3"
    
  }
  
  landform_types {
    id           id_landform PK
    cat          catu_landform FK           "Table 4.3"
    txt          txt_landform_name          "Table 4.3"
    txt          txt_landform_desc          "Table 4.3"
  }
  
  land_cover_types {
    id           id_land_cover PK
    cat          catu_land_cover FK         "Table 8.1"
    txt          txt_land_cover_name        "Table 8.1"
    txt          txt_land_cover_desc        "Table 8.1"
  }

  measurement_types {
    id          id_mtype PK
    cat         catu_mtype FK               "Table D.1.1"
    txt         txt_mtype_name              "Table D.1.1"
  }
  
  landform_elements {
    id          id_landform_element PK
    cat         catu_landform_element FK    "Table 4.4"
    txt         txt_landform_element_name   "Table 4.4"
  }
  
  landform_element_vmods {
    id          id_landform_element_vmod PK
    cat         catu_landform_element_vmod FK    "Table 4.5"
    txt         txt_landform_element_vmod_name   "Table 4.5"
    txt         txt_landform_element_vmod_desc   "Table 4.5"
  } 

  landform_element_hmods {
    id          id_landform_element_hmod PK
    cat         catu_landform_element_hmod FK    "Table 4.6"
    txt         txt_landform_element_hmod_name   "Table 4.6"
    txt         txt_landform_element_hmod_desc   "Table 4.6"
  } 

  %% data %%
  lform_landuse {
    id           id_site PK
    id           id_landform PK
    id           id_lform_landuse PK
    cat          catu_lform_landuse_tertiary FK   "Section 8.2.2"
    amt[0-100]   amt_landuse_area_p               "Section 8.2.2"
    cat          cato_landuse_impact              "Section 8.2.2"
  }

  landuse_tertiary {
    id           id_landuse_secondary PK
    id           id_landuse_tertiary PK
    cat          catu_landuse_tertiary FK         "Fig. 8.1"
    txt          txt_landuse_tertiary_name        "Fig. 8.1"
  }

  landuse_impacts {
    id           id_landuse_impact PK
    cat          catu_landuse_impact FK         "Table 8.2"
    txt          txt_landuse_impact_name        "Table 8.2"
    txt          txt_landuse_impact_desc        "Table 8.2"
  }

```

## Site overview {#sec-erd-siteover}

The site table stores reference data (@sec-ref), except for location.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Site - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site ||--|{ disturbances : "has one or more"
  disturbances }|--|{ disturbance_types : "code comes from"
  disturbances }|--|{ disturbance_ages : "code comes from"
  site }|--|{ site_purposes : "code comes from"
  site }|--|{ site_selection_methods : "code comes from"
  site }|--|{ site_exposure_types : "code comes from"
  site }|--|{ site_early_stop_types : "code comes from"
  site }|--|{ current_weather : "code comes from"
  site }|--|{ recent_rainfall : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Site - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site ||--|{ disturbances : "has one or more"
  disturbances }|--|{ disturbance_types : "code comes from"
  disturbances }|--|{ disturbance_ages : "code comes from"
  site }|--|{ site_purposes : "code comes from"
  site }|--|{ site_selection_methods : "code comes from"
  site }|--|{ site_exposure_types : "code comes from"
  site }|--|{ site_early_stop_types : "code comes from"
  site }|--|{ current_weather : "code comes from"
  site }|--|{ recent_rainfall : "code comes from"

  %% data %%
  site {
    id          id_site PK               "Section 10.2.1"
    usr         usr_author               "Section 10.2.2"
    dt          dt_site                  "Section 10.2.4"
    cat         catu_site_purpose FK     "Section 10.2.5"
    cat         catu_site_selection FK   "Section 10.2.6"
    cat         catu_site_exposure FK    "Section 10.2.7"
    num[0-1000] amt_site_depth_max_cm    "Section 10.2.8"
    cat         catu_site_early_stop FK  "Section 10.2.9"
    %% weather at visit %%
    cat         catu_weather FK          "Section 6.3.1"
    num[-50-50] val_temperature_c        "Section 6.3.1"
    cat         cato_recent_rain FK      "Section 6.3.2"
  }
  
  site_purposes {
    id          id_site_purpose PK
    cat         catu_site_purpose FK     "Table 10.1"
    txt         txt_site_purpose_name    "Table 10.1"
  }
  
  site_selection_methods {
    id          id_site_selection PK
    cat         catu_site_selection FK   "Table 10.2"
    txt         txt_site_selection_name  "Table 10.2"
  }
  
  site_exposure_types {
    id          id_site_exposure PK
    cat         catu_site_exposure FK    "Table 10.3"
    txt         txt_site_exposure_name   "Table 10.3"
  }
  
  site_early_stop_types {
    id          id_site_early_stop PK
    cat         catu_site_early_stop FK  "Table 10.4"
    txt         txt_site_early_stop_name "Table 10.4"
    txt         txt_site_early_stop_desc "Table 10.4"
  }

  current_weather {
    id          id_weather PK
    cat         catu_weather FK  "Table 6.3.1"
    txt         txt_weather_name "Table 6.3.1"
  }
  
  recent_rainfall {
    id          id_rainfall PK
    cat         catu_rainfall FK  "Table 6.3.2"
    txt         txt_rainfall_name "Table 6.3.2"
  }
  
  %% data %%
  disturbances {
    id          id_site PK
    id          id_disturbance PK
    cat         catu_disturbance FK      "Section 10.3"
    cat         catu_disturbance_age FK  "Section 10.3"
    num[0-Inf]  amt_disturbance_age_days "Section 10.3"
  }

  disturbance_types {
    id          id_disturbance PK
    cat         catu_disturbance FK      "Table 10.5"
    txt         txt_disturbance_name     "Table 10.5"
  }
  
  disturbance_ages {
    id          id_disturbance_age PK
    cat         catu_disturbance_age FK  "Table 10.6"
    txt         txt_disturbance_age_name "Table 10.6"
  }


  
```

## Surface {#sec-erd-surface}

The surface table stores slope, aspect, surface cover, surface water, surface cracking, microrelief, erosion and deposition data (@sec-pr-surface). It can also store LUC vegetation code (@sec-veg). This table can optionally extend the site table (@sec-erd-siteover) as it has a 1:1 relationship.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Surface - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site_surface }|--|{ slope_methods : "code comes from"
  site_surface }|--|{ measurement_types : "code comes from"
  site_surface }|--|{ aspect_methods : "code comes from"
  site_surface ||--|{ surface_cover : "has"
  surface_cover }|--|{ surface_cover_types : "code comes from"
  site_surface }|--|{ surface_water_types : "code comes from"
  site_surface }|--|{ surface_water_persts : "code comes from"
  site_surface }|--|{ surface_crack_types : "code comes from"
  site_surface }|--|{ surface_crack_shapes : "code comes from"
  site_surface ||--o{ site_erosion : "may have"
  site_surface }|--|{ site_microrelief_nat : "code comes from"
  site_surface }|--|{ site_microrelief_ant : "code comes from"
  site_erosion }|--|{ erosion_types : "code comes from"
  site_erosion }|--|{ erosion_severity : "code comes from"
  site_erosion }|--|{ erosion_activity : "code comes from"
  site_surface ||--o{ site_deposition : "may have"
  site_deposition }|--|{ deposition_types : "code comes from"
  site_deposition }|--|{ deposition_severity : "code comes from"
  site_deposition }|--|{ deposition_activity : "code comes from"
  site_surface }|--|{ water_depths : "code comes from"
  site_surface }|--|{ luc_vegetation_codes : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Surface - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  site_surface }|--|{ slope_methods : "code comes from"
  site_surface }|--|{ measurement_types : "code comes from"
  site_surface }|--|{ aspect_methods : "code comes from"
  site_surface ||--|{ surface_cover : "has"
  surface_cover }|--|{ surface_cover_types : "code comes from"
  site_surface }|--|{ surface_water_types : "code comes from"
  site_surface }|--|{ surface_water_persts : "code comes from"
  site_surface }|--|{ surface_crack_types : "code comes from"
  site_surface }|--|{ surface_crack_shapes : "code comes from"
  site_surface ||--o{ site_erosion : "may have"
  site_surface }|--|{ site_microrelief_nat : "code comes from"
  site_surface }|--|{ site_microrelief_ant : "code comes from"
  site_erosion }|--|{ erosion_types : "code comes from"
  site_erosion }|--|{ erosion_severity : "code comes from"
  site_erosion }|--|{ erosion_activity : "code comes from"
  site_surface ||--o{ site_deposition : "may have"
  site_deposition }|--|{ deposition_types : "code comes from"
  site_deposition }|--|{ deposition_severity : "code comes from"
  site_deposition }|--|{ deposition_activity : "code comes from"
  site_surface }|--|{ water_depths : "code comes from"
  site_surface }|--|{ luc_vegetation_codes : "code comes from"

  %% data %%
  site_surface {
    id          id_site PK                       "Section 10.2.1"
    num[0-90]   amt_slope_d                      "Section 11.1"
    cat         catu_slope_rec FK                "Section D.13.1"
    cat         catu_slope_method FK             "Section 11.1"
    num[0-359]  amt_aspect_d                     "Section 11.2"
    cat         catu_aspect_rec FK               "Section D.13.1"
    cat         catu_aspect_method FK            "Section 11.2"
    cat         catu_surface_water FK            "Section 11.4"
    cat         cato_surface_water_perst FK      "Section 11.4"
    cat         catu_surface_cracks FK           "Section 11.5"
    cat         catu_surface_crack_shp FK        "Section 11.5"
    cat         catu_microrelief_nat FK          "Section 11.6.1"
    %% optional details %%
    num[0-Inf]  amt_microrelief_nat_width_cm    "Section 11.6.1"
    num[0-Inf]  amt_microrelief_nat_height_cm   "Section 11.6.1"
    cat         catu_microrelief_ant FK         "Section 11.6.2"
    %% optional details %%
    num[0-Inf]  amt_microrelief_ant_width_cm    "Section 11.6.1"
    num[0-Inf]  amt_microrelief_ant_height_cm   "Section 11.6.1"
    num[0-Inf]  amt_depth_water_cm              "Section 12.7"
    cat         catu_depth_water FK             "Section 12.7"
    %% vegetation could also be stored at landscape and/or landform %%
    cat         catu_vegetation_luc FK          "Section 7.1"
  }
  
  slope_methods {
    id          id_slope_method PK
    cat         catu_slope_method FK     "Table 11.1"
    txt         txt_slope_method_name    "Table 11.1"
    txt         txt_slope_method_desc    "Table 11.1"
  }
  
  aspect_methods {
    id          id_aspect_method PK
    cat         catu_aspect_method FK     "Table 11.2"
    txt         txt_aspect_method_name    "Table 11.2"
    txt         txt_aspect_method_desc    "Table 11.2"
  }
  
  measurement_types {
    id          id_mtype PK
    cat         catu_mtype FK
    txt         txt_mtype_name           "Table D.1"
    txt         txt_mtype_desc           "Table D.1"
  }

  %% data %%
  surface_cover {
    id          id_site PK
    cat         catu_surface_cover FK    "Section 11.3"
    num[0-100]  amt_surface_cover_p      "Section 11.3"
  }
  
  surface_cover_types {
    id          id_surface_cover PK
    cat         catu_surface_cover FK    "Table 11.3"
    txt         txt_surface_cover_name   "Table 11.3"
    txt         txt_surface_cover_desc   "Table 11.3"
  }
  
  surface_water_types {
    id          id_surface_water PK
    cat         catu_surface_water FK   "Table 11.4"
    txt         txt_surface_water_name  "Table 11.4"
    txt         txt_surface_water_desc  "Table 11.4"
  }

  surface_water_persts {
    id          id_surface_water_perst PK
    cat         catu_surface_water_perst FK    "Table 11.5"
    txt         txt_surface_water_perst_name   "Table 11.5"
    txt         txt_surface_water_perst_desc   "Table 11.5"
  }

  surface_crack_types {
    id          id_surface_crack
    cat         catu_surface_crack             "Table 11.6"
    txt         txt_surface_crack_name         "Table 11.6"
    txt         txt_surface_crack_desc         "Table 11.6"
  }
  
  surface_crack_shapes {
    id          id_surface_crack_shp PK
    cat         catu_surface_crack_shp FK      "Table 11.7"
    txt         txt_surface_crack_shp_name     "Table 11.7"
    txt         txt_surface_crack_shp_desc     "Table 11.7"
  }
  
  site_microrelief_nat {
    id          id_microrelief_nat PK
    cat         catu_microrelief_nat FK      "Table 11.8"
    txt         txt_microrelief_nat_name     "Table 11.8"
    txt         txt_microrelief_nat_desc     "Table 11.8"
  }
  
  site_microrelief_ant {
    id          id_microrelief_ant PK
    cat         catu_microrelief_ant FK      "Table 11.9"
    txt         txt_microrelief_ant_name     "Table 11.9"
    txt         txt_microrelief_ant_desc     "Table 11.9"
  }
  
  %% data %%
  site_erosion {
    id          id_site PK
    id          id_erosion PK                   
    cat         catu_erosion FK              "Section 11.7"            
    cat         cato_erosion_severity FK     "Section 11.7"
    cat         cato_erosion_activity FK     "Section 11.7"
  }
  
  erosion_types {
    id          id_erosion PK
    cat         catu_erosion FK              "Table 11.10"
    txt         txt_erosion_name             "Table 11.10"
    txt         txt_erosion_desc             "Table 11.10"
  }
  
  erosion_severity {
    id          id_erosion_severity PK
    cat         catu_erosion_severity FK     "Table 11.11"
    txt         txt_erosion_severity_name    "Table 11.11"
    txt         txt_erosion_severity_desc    "Table 11.11"
  }  
  
  erosion_activity {
    id          id_erosion_activity PK
    cat         catu_erosion_activity FK     "Table 11.12"
    txt         txt_erosion_activity_name    "Table 11.12"
    txt         txt_erosion_activity_desc    "Table 11.12"
  }  
  
 %% data %%
 site_deposition {
    id          id_site PK
    id          id_deposition PK
    cat         catu_deposition FK             "Section 11.8"    
    cat         cato_deposition_severity FK    "Section 11.8"
    cat         cato_deposition_activity FK    "Section 11.8"
  }
  
  deposition_types {
    id          id_deposition PK
    cat         catu_deposition FK                "Table 11.13"
    txt         txt_deposition_name               "Table 11.13"
    txt         txt_deposition_desc               "Table 11.13"
  }  
  
  deposition_severity {
    id          id_deposition_severity PK
    cat         catu_deposition_severity FK       "Table 11.14"
    txt         txt_deposition_severity_name      "Table 11.14"
    txt         txt_deposition_severity_desc      "Table 11.14"
  }  
  
  deposition_activity {
    id          id_deposition_activity PK
    cat         catu_deposition_activity FK       "Table 11.15"
    txt         txt_deposition_activity_name      "Table 11.15"
    txt         txt_deposition_activity_desc      "Table 11.15"
  }  
  
  water_depths {
    id          id_depth_water PK
    cat         catu_depth_water FK        "Table 12.4"
    txt         txt_depth_water_name       "Table 12.4"
    txt         txt_depth_water_desc       "Table 12.4"  
  }
 
  luc_vegetation_codes {
    id          id_vegetation_luc PK
    cat         catu_vegetation_luc FK        "Section 7.1"
    txt         txt_vegetation_luc_name       "Section 7.1"
    txt         txt_vegetation_luc_desc       "Section 7.1"  
  } 
```

## Horizon overview {#sec-erd-horizono}

The horizon overview table stores boundary definitions, soil moisture status at the time of examination, and parent material origin. 

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon }|--|{ boundary_shapes : "code comes from"
  horizon }|--|{ boundary_widths : "code comes from"
  horizon }|--|{ soil_moistures : "code comes from"
  horizon }|--|{ nzsc_pmo : "code comes from"
  horizon }|--|{ nzsc_pmo_mod : "code comes from"
  horizon }|--|{ weathering : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon }|--|{ boundary_shapes : "code comes from"
  horizon }|--|{ boundary_widths : "code comes from"
  horizon }|--|{ soil_moistures : "code comes from"
  horizon }|--|{ nzsc_pmo : "code comes from"
  horizon }|--|{ nzsc_pmo_mod : "code comes from"
  horizon }|--|{ weathering : "code comes from"
  
  %% data %%
  horizon {
  id         id_site PK
  id         id_horizon PK
  num[0-Inf] amt_upper_depth_cm      "Section 12.2"
  num[0-Inf] amt_upper_depth_med_cm  "Section 12.2"
  num[0-Inf] amt_upper_depth_min_cm  "Section 12.2"
  num[0-Inf] amt_upper_depth_max_cm  "Section 12.2"
  num[0-Inf] amt_lower_depth_cm      "Section 12.2"
  num[0-Inf] amt_lower_depth_med_cm  "Section 12.2"
  num[0-Inf] amt_lower_depth_min_cm  "Section 12.2"
  num[0-Inf] amt_lower_depth_max_cm  "Section 12.2"
  cat        catu_boundary_shape FK  "Section 12.4"
  num[0-Inf] amt_boundary_cm         "Section 12.5"
  cat        cato_boundary_width FK  "Section 12.5" 
  cat        cato_soil_moisture FK   "Section 12.6"
  %% pmo %%
  cat        catu_parent_material_origin FK    "Section 12.8"
  cat        catu_anthropic_origin_modifier FK "Section 12.8"
  %% weathering %%
  cat        cato_horizon_weathering FK  "Section 12.9"
  }
  
  boundary_shapes {
    id          id_boundary_shape PK
    cat         catu_boundary_shape FK       "Table 12.1"
    txt         txt_boundary_shape_name      "Table 12.1"
    txt         txt_boundary_shape_desc      "Table 12.1"
  }
 
  boundary_widths {
    id          id_boundary_width PK
    cat         cato_boundary_width FK       "Table 12.2"
    txt         txt_boundary_width_name      "Table 12.2"
    txt         txt_boundary_width_desc      "Table 12.2"
  } 
  
  soil_moistures {
    id          id_soil_moisture PK
    cat         cato_soil_moisture FK       "Table 12.3"
    txt         txt_soil_moisture_name      "Table 12.3"
  }
  
  nzsc_pmo {
    id          id_pmo PK
    cat         catu_pmo FK             "Table 24.5"
    txt         txt_pmo_name            "Table 24.5"
    txt         txt_pmo_desc            "Table 24.5"  
  }
  
  nzsc_pmo_mod {
    id          id_pmo_mod PK
    cat         catu_pmo_mod FK         "Table 12.5"
    txt         txt_pmo_mod_name        "Table 12.5"
    txt         txt_pmo_mod_desc        "Table 12.5"  
  }
  
  weathering {
    id          id_weathering PK
    cat         catu_weathering FK         "Table 12.6"
    txt         txt_weathering_name        "Table 12.6"
    txt         txt_weathering_desc_rock   "Table 12.6"  
    txt         txt_weathering_desc_seds   "Table 12.6" 
  }
  
```

## Horizon architecture {#sec-erd-horizona}

The horizon architecture table stores data about pedality, fragments, roots, voids and pores. This table can optionally extend the horizon overview table (@sec-erd-horizono) as it has a 1:1 relationship.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon architecture - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram 
  horizon_arch ||--|{ horizon_structures : "may have"
  horizon_structures }|--|{ structure_types : "code comes from"
  horizon_structures }|--|{ structure_grades : "code comes from"
  horizon_structures }|--|{ structure_shapes : "code comes from"
  horizon_arch ||--|{ horizon_rock_fragments : "may have"
  horizon_rock_fragments }|--|{ fragment_sizes_simple : "code comes from"
  horizon_rock_fragments }|--|{ rock_fragment_shapes : "code comes from"
  horizon_rock_fragments }|--|{ nzsc_lithologies : "code comes from"
  horizon_arch ||--|{ horizon_other_fragments : "may have"
  horizon_other_fragments }|--|{ fragment_sizes_simple : "code comes from"
  horizon_other_fragments }|--|{ other_fragment_types : "code comes from"
  horizon_arch ||--|{ horizon_artefacts : "may have"
  horizon_artefacts }|--|{ fragment_sizes_simple : "code comes from"
  horizon_artefacts }|--|{ artefact_types: "code comes from"
  horizon_arch ||--|{ horizon_plastics : "may have"
  horizon_plastics }|--|{ plastic_types: "code comes from"
  horizon_plastics }|--|{ plastic_fixations: "code comes from"
  horizon_plastics }|--|{ plastic_shapes: "code comes from"
  horizon_plastics }|--|{ plastic_degradations: "code comes from"
  horizon_arch ||--|{ horizon_roots : "may have"
  horizon_arch }|--|{ root_positions: "code comes from"
  horizon_roots }|--|{ root_positions: "code comes from"
  horizon_roots }|--|{ root_sizes_simple: "code comes from"
  horizon_arch ||--|{ horizon_voids : "may have"
  horizon_voids }|--|{ void_types : "code comes from"
  horizon_voids }|--|{ void_conns : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon architecture - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_arch ||--|{ horizon_structures : "may have"
  horizon_structures }|--|{ structure_types : "code comes from"
  horizon_structures }|--|{ structure_grades : "code comes from"
  horizon_structures }|--|{ structure_shapes : "code comes from"
  horizon_arch ||--|{ horizon_rock_fragments : "may have"
  horizon_rock_fragments }|--|{ fragment_sizes_simple : "code comes from"
  horizon_rock_fragments }|--|{ rock_fragment_shapes : "code comes from"
  horizon_rock_fragments }|--|{ nzsc_lithologies : "code comes from"
  horizon_arch ||--|{ horizon_other_fragments : "may have"
  horizon_other_fragments }|--|{ fragment_sizes_simple : "code comes from"
  horizon_other_fragments }|--|{ other_fragment_types : "code comes from"
  horizon_arch ||--|{ horizon_artefacts : "may have"
  horizon_artefacts }|--|{ fragment_sizes_simple : "code comes from"
  horizon_artefacts }|--|{ artefact_types: "code comes from"
  horizon_arch ||--|{ horizon_plastics : "may have"
  horizon_plastics }|--|{ plastic_types: "code comes from"
  horizon_plastics }|--|{ plastic_fixations: "code comes from"
  horizon_plastics }|--|{ plastic_shapes: "code comes from"
  horizon_plastics }|--|{ plastic_degradations: "code comes from"
  horizon_arch ||--|{ horizon_roots : "may have"
  horizon_arch }|--|{ root_positions: "code comes from"
  horizon_roots }|--|{ root_positions: "code comes from"
  horizon_roots }|--|{ root_sizes_simple: "code comes from"
  horizon_arch ||--|{ horizon_voids : "may have"
  horizon_voids }|--|{ void_types : "code comes from"
  horizon_voids }|--|{ void_conns : "code comes from"
  
  horizon_arch {
    id         id_site PK
    id         id_horizon PK
    %% structures are in subtable %%
    %% all frags super rapid (record this OR the next three) %%
    num[0-100] amt_total_frags_p                "Section 4.2"
    %% rock frags rapid (detailed in subtable) %%
    num[0-100] amt_rockfrags_p                  "Section 13.2"
    %% notrock frags rapid %%
    num[0-100] amt_otherfrags_p                 "Section 13.2.1.1"
    %% artefacts rapid %%
    num[0-100] amt_artefacts_p                  "Section 13.2.1.2"
    %% roots rapid %%
    num[0-100] amt_total_roots_p                "Section 13.3.1"
    cat        catu_total_roots_pos FK          "Section 13.3.2"
    %% void rapid %%
    num[0-100] amt_total_voids_p                "Section 13.4"
    %% pores rapid %%
    num[0-10]  amt_total_pores_p                "Section 13.5"
    %% pores detailed (could also spin out to subtable) %%
    num[0-10]  amt_macropores_p                 "Section 13.5"
    num[0-10]  amt_micropores_p                 "Section 13.5"
                  
  }

  structure_types {
    id          id_structure PK      
    cat         catu_structure FK    "Table 13.1"
    txt         txt_structure_name   "Table 13.1"
    txt         txt_structure_desc   "Table 13.1"
  }

  structure_grades {
    id          id_structure_grade PK      
    cat         cato_structure_grade FK    "Table 13.2"
    txt         txt_structure_grade_name   "Table 13.2"
    txt         txt_structure_grade_desc   "Table 13.2"
  }

  structure_shapes {
    id          id_structure_shape PK     
    cat         catu_structure_shape FK        "Table 13.3"
    txt         txt_structure_shape_name       "Table 13.3"
    txt         txt_structure_shape_desc       "Table 13.3"
  }

  structure_sizes_rapid {
    id          id_structure_size_rapid PK      "Section 13.1.4"
    cat         cato_structure_size_rapid FK    "C, F"
    txt         txt_structure_size_rapid_name   "Coarse, Fine"
  }
  
  %% data %%
  horizon_structures {
    id id_site PK
    id id_horizon PK
    id id_structure PK
    cat        catu_structure_type FK     "Section 13.1.1"
    cat        cato_structure_grade FK    "Section 13.1.2"
    cat        cato_structure_shape FK    "Section 13.1.3"
    %% rapid size %%
    cat        cato_structure_size FK     "Section 13.1.4"
    %% routine size %%
    num[0-Inf] amt_structure_size_med_mm  "Section 13.1.4" 
    %% add for detailed size %%
    num[0-Inf] amt_structure_size_min_mm  "Section 13.1.4" 
    num[0-Inf] amt_structure_size_max_mm  "Section 13.1.4"
  }
  
  %% data %%
  horizon_rock_fragments {
    id         id_site PK
    id         id_horizon PK
    id         id_rock_fragment PK
    cat        cato_rock_fragment_size FK       "Section 13.2.1"
    cat        catu_rock_fragment_shape FK      "Section 13.2.1"
    num[0-100] amt_rock_fragment_p         
    cat        catu_rock_fragment_lithology FK  "Section 24.1.2"
  }
  
  fragment_sizes_simple {
    id          id_rockfrag_size PK    
    cat         catu_rockfrag_size FK         "Table 13.4"
    txt         txt_rockfrag_size_name        "Table 13.4"
    txt         txt_rockfrag_size_desc        "Table 13.4"
  }
  
  rock_fragment_shapes {
    id          id_rockfrag_shape PK    
    cat         catu_rockfrag_shape FK        "Table 13.5"
    txt         txt_rockfrag_shape_name       "Table 13.5"
    txt         txt_rockfrag_shape_desc       "Table 13.5"
  }
  
  nzsc_lithologies {
    id          id_nzsc_lithology PK     
    cat         catu_nzsc_lithology FK        "Table 24.2"
    txt         txt_nzsc_lithology_name       "Table 24.2"
  }
  
  %% data %%
  horizon_other_fragments {
    id         id_site PK
    id         id_horizon PK
    id         id_other_fragment PK
    cat        cato_other_fragment_size FK      "Table 13.4"
    cat        cato_other_fragment FK           "Table 13.6"
    num[0-100] amt_rock_fragment_p              "Section 13.2.1"
  }  
  
  other_fragment_types {
    id          id_other_fragment PK
    cat         cato_other_fragment FK        "Table 13.6"
    txt         txt_other_fragment_name       "Table 13.6"
    txt         txt_other_fragment_desc       "Table 13.6"
  }

  %% data %%
  horizon_artefacts {
    id         id_site PK
    id         id_horizon PK
    id         id_artefact PK
    cat        cato_artefact_size FK           "Table 13.4"
    cat        cato_artefact FK                "Table 13.7"
    num[0-100] amt_artefact_p                  "Section 13.2.2"
  } 
  
  artefact_types {
    id          id_artefact PK
    cat         catu_artefact FK             "Table 13.7"
    txt         txt_artefact_type_name       "Table 13.7"
    txt         txt_artefact_type_desc       "Table 13.7"
  }
  
  %% %%
  horizon_plastics {
    id         id_site PK
    id         id_horizon PK
    id         id_plastic PK
    cat        catu_plastic FK          "Table 13.8"
    cat        cato_plastic_fixation    "Table 13.9"
    cat        catu_plastic_shape       "Table 13.10"
    cat        cato_plastic_degradation "Table 13.11"
    num[0-100] amt_plastic_p            "Section 13.2.4"
  } 
  
  plastic_types {
    id          id_plastic PK
    cat         catu_plastic FK        "Table 13.8"
    txt         txt_plastic_name       "Table 13.8"
    txt         txt_plastie_desc       "Table 13.8"
  }

  plastic_fixations {
    id          id_plastic_fixation PK
    cat         catu_plastic_fixation FK        "Table 13.9"
    txt         txt_plastic_fixation_name       "Table 13.9"
    txt         txt_plastic_fixation_desc       "Table 13.9"
  }  
  
  plastic_shapes {
    id          id_plastic_shape PK
    cat         catu_plastic_shape FK        "Table 13.10"
    txt         txt_plastic_shape_name       "Table 13.10"
    txt         txt_plastic_shape_desc       "Table 13.10"
  }    
  
  plastic_degradations {
    id          id_plastic_degradations PK
    cat         catu_plastic_degradations FK        "Table 13.11"
    txt         txt_plastic_degradations_name       "Table 13.11"
    txt         txt_plastic_degradations_desc       "Table 13.11"
  }   
  
  %% data %%
  horizon_roots {
    id         id_site PK
    id         id_horizon PK
    id         id_root PK
    cat        cato_root_size FK           "Table 13.12"
    cat        cato_root_pos FK            "Table 13.13"
    num[0-100] amt_root_p                  "Section 13.3.1"
  } 

  root_sizes_simple {
    id          id_root_size PK     
    cat         catu_root_size FK         "Table 13.12"
    txt         txt_root_size_name        "Table 13.12"
    txt         txt_root_size_desc        "Table 13.12"
  }
  
  root_positions {
    id          id_root_pos PK     
    cat         catu_root_pos FK         "Table 13.13"
    txt         txt_root_pos_name        "Table 13.13"
    txt         txt_root_pos_desc        "Table 13.13"
  }

  %% data %%
  horizon_voids {
    id         id_site PK
    id         id_horizon PK
    id         id_void PK
    cat        catu_void FK                 "Table 13.14"
    cat        cato_void_conn FK            "Table 13.15"
    num[0-100] amt_void_p                   "Section 13.4"
  } 
  
  void_types {
    id          id_void PK 
    cat         catu_void FK              "Table 13.14"
    txt         txt_void_name             "Table 13.14"
    txt         txt_void_desc             "Table 13.14"
  }  

  void_conns {
    id          id_void_conn PK 
    cat         cato_void_conn FK         "Table 13.15"
    txt         txt_void_conn_name        "Table 13.15"
    txt         txt_void_conn_desc        "Table 13.15"
  }  
  
```

## Horizon colour {#sec-erd-colour}

These tables store matrix colours (@sec-col-matrix) and colour patterns (@sec-col-patt).

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon colour - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon ||--|{ horizon_matrix_colours : "has one or more"
  horizon ||--|{ horizon_colour_patterns : "has one or more"
  horizon_matrix_colours }|--|{ munsell_colours : "code comes from"
  horizon_colour_patterns }|--|{ munsell_colours : "code comes from"
  horizon_colour_patterns }|--|{ short_colours : "code comes from"
  horizon_colour_patterns }|--|{ pattern_types : "code comes from"
  horizon_colour_patterns }|--|{ pattern_shapes : "code comes from"
  horizon_colour_patterns }|--|{ pattern_contrasts : "code comes from"
  horizon_colour_patterns }|--|{ pattern_edges : "code comes from"
```
:::

```{mermaid}
%%| fig-cap: "Horizon colour - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon ||--|{ horizon_matrix_colours : "has one or more"
  horizon ||--|{ horizon_colour_patterns : "has one or more"
  horizon_matrix_colours }|--|{ munsell_colours : "code comes from"
  horizon_colour_patterns }|--|{ munsell_colours : "code comes from"
  horizon_colour_patterns }|--|{ short_colours : "code comes from"
  horizon_colour_patterns }|--|{ pattern_types : "code comes from"
  horizon_colour_patterns }|--|{ pattern_shapes : "code comes from"
  horizon_colour_patterns }|--|{ pattern_contrasts : "code comes from"
  horizon_colour_patterns }|--|{ pattern_edges : "code comes from"
  
  %% data %%
  horizon_matrix_colours {
    id          id_site PK
    id          id_horizon PK
    id          id_colour_matrix PK        "Section 14.2"
    %% can also split into hue/value/chroma with subtables %%
    cat         cato_matrix_munsell        "Section 14.2"
    %% no simple colours on the matrix!! %%
    cat         catu_colour_moisture       "Section 14.2"
  }
  
  %% data %%
  horizon_colour_patterns {
    id          id_site PK
    id          id_horizon PK
    id          id_colour_pattern PK
    %% either %%
    cat         cato_pattern_munsell FK        "Section 14.2"
    %% or, %%
    cat         catu_pattern_short_colour FK   "Section 1r.1.1"
    cat         catu_pattern_type FK           "Section 14.3.1"
    num[0-100]  amt_pattern_p                  "Section 14.3.2"
    cat         catu_pattern_shape  FK         "Section 14.3.3"
    num[0-Inf]  amt_pattern_size_mm            "Section 14.3.4"
    cat         cato_pattern_contrast FK       "Section 14.3.5"
    cat         cato_pattern_edge FK           "Section 14.3.6"
   }
 
  munsell_colours {
    id          id_munsell PK
    cat         cato_munsell              "See charts for allowable values"
    %% optional but handy: %%
    txt         txt_colour_hex
  }  
   
  short_colours {
    id          id_colour PK
    cat         catu_colour FK            "Table 14.2"
    txt         txt_colour_name           "Table 14.2"
    txt         txt_colour_desc           "Table 14.2"
  } 
  
  colour_moistures {
    id          id_colour_moisture
    cat         cato_colour_moisture      "M or D"
    txt         txt_colour_moisture_name  "Moist or Dry"
  }

  pattern_types {
    id          id_pattern PK
    cat         catu_pattern FK           "Table 14.3"
    txt         txt_pattern_name          "Table 14.3"
    txt         txt_pattern_desc          "Table 14.3"
  } 

  pattern_shapes {
    id          id_pattern_shape PK
    cat         catu_pattern_shape FK     "Table 14.4"
    txt         txt_pattern_shape_name    "Table 14.4"
    txt         txt_pattern_shape_desc    "Table 14.4"
  }

  pattern_contrasts {
    id          id_pattern_contrast PK
    cat         catu_pattern_contrast FK  "Table 14.5"
    txt         txt_pattern_contrast_name "Table 14.5"
    txt         txt_pattern_contrast_desc "Table 14.5"
  }
  
  pattern_edges {
    id          id_pattern_edge PK
    cat         catu_pattern_edge FK      "Table 14.6"
    txt         txt_pattern_edge_name     "Table 14.6"
    txt         txt_pattern_edge_desc     "Table 14.6"
  }
```

## Horizon texture {#sec-erd-texture}

The horizon texture table stores texture category as well as field estimates of sand and clay, and optionally lithology and mineralogy of the fine earth fraction. This table can optionally extend the horizon overview table (@sec-erd-horizono) as it has a 1:1 relationship.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon texture - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_texture }|--|{ simple_textures : "code comes from"
  horizon_texture }|--|{ mineral_textures : "code comes from"
  horizon_texture }|--|{ sand_sizes : "code comes from"
  horizon_texture }|--|{ organic_levels : "code comes from"
  horizon_texture }|--|{ organic_types : "code comes from"
  horizon_texture }|--|{ organic_decomps : "code comes from"
  horizon_texture }|--|{ nzsc_lithologies : "code comes from"
  horizon_texture }|--|{ nzsc_mineralogies : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon texture - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_texture }|--|{ simple_textures : "code comes from"
  horizon_texture }|--|{ mineral_textures : "code comes from"
  horizon_texture }|--|{ sand_sizes : "code comes from"
  horizon_texture }|--|{ organic_levels : "code comes from"
  horizon_texture }|--|{ organic_types : "code comes from"
  horizon_texture }|--|{ organic_decomps : "code comes from"
  horizon_texture }|--|{ nzsc_lithologies : "code comes from"
  horizon_texture }|--|{ nzsc_mineralogies : "code comes from"
  
  %% data %%
  horizon_texture {
    id          id_site PK
    id          id_horizon PK
    cat         cato_texture_simple FK    "Section 15.1.1"
    cat         cato_texture FK           "Section 15.1"
    cat         cato_texture_sandmod FK   "Section 15.2.1"
    cat         catu_texture_orgmod FK    "Section 15.2.2"
    cat         cato_organic_type FK      "Section 15.4"
    cat         catu_organic_decomp FK    "Section 15.4.1"
    cat         catu_organic_minmod FK    "Section 15.4.2"
    num[0-100]  amt_sand_est_med_p        "Section 15.3"
    num[0-100]  amt_sand_est_min_p        "Section 15.3"
    num[0-100]  amt_sand_est_max_p        "Section 15.3"
    num[0-100]  amt_clay_est_med_p        "Section 15.3"
    num[0-100]  amt_clay_est_min_p        "Section 15.3"
    num[0-100]  amt_clay_est_max_p        "Section 15.3"
    cat         catu_lithology            "Section 5.2.2"
    cat         catu_mineralogy           "Section 5.3"
  }
   
  simple_textures {
    id          id_simple_texture PK
    cat         cato_simple_texture FK    "Table 15.3"
    txt         txt_simple_texture_name   "Table 15.3"
    txt         txt_simple_texture_desc   "Table 15.3"
  } 

  mineral_textures {
    id          id_texture PK
    cat         cato_texture FK           "Table 15.2"
    txt         txt_texture_name          "Table 15.2"
    txt         txt_texture_desc          "Table 15.2"
  } 

  sand_sizes {
    id          id_sand_size PK
    cat         catu_sand_size FK         "Table 15.4"
    txt         txt_sand_size_name        "Table 15.4"
    txt         txt_sand_size_desc        "Table 15.4"
  }

  organic_levels {
    id          id_organic PK
    cat         catu_organic FK           "Table 15.5"
    txt         txt_organic_name          "Table 15.5"
    txt         txt_organic_desc          "Table 15.5"
  }

  organic_types {
    id          id_organic_type PK
    cat         catu_organic_type FK    "Table 15.6"
    txt         txt_organic_type_name   "Table 15.6"
    txt         txt_organic_type_desc   "Table 15.6"
  }
  
  organic_decomps {
    id          id_organic_decomp PK
    cat         catu_organic_decomp FK    "Table 15.7"
    txt         txt_organic_decomp_name   "Table 15.7"
    txt         txt_organic_decomp_desc   "Table 15.7"
  }
  
  nzsc_lithologies {
    id          id_nzsc_lithology PK    
    cat         catu_nzsc_lithology FK        "Table 24.2"
    txt         txt_nzsc_lithology_name       "Table 24.2"
  }

  nzsc_mineralogies {
    id          id_nzsc_mineralogy PK    
    cat         catu_nzsc_mineralogy FK        "Table 5.2"
    txt         txt_nzsc_mineralogy_name       "Table 5.2"
    txt         txt_nzsc_mineralogy_desc       "Table 5.2"
  }
```

## Horizon secondary features {#sec-erd-secf}

The secondary features tables store essential information in a parent table and more detailed optional descriptions in child tables. The parent table can optionally extend the horizon overview table (@sec-erd-horizono) as it has a 1:1 relationship.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon secondary features - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_secondary_features ||--o{ "horizon_concentrations" : "may have some"
  horizon_secondary_features ||--o{ "horizon_coatings" : "may have some"
  horizon_secondary_features ||--o{ "horizon_bio_features" : "may have some"
  horizon_secondary_features }|--|{ concentration_forms : "code comes from"
  horizon_secondary_features }|--|{ concentration_forms : "code comes from"
  horizon_secondary_features }|--|{ concentration_types : "code comes from"
  horizon_secondary_features }|--|{ pan_types : "code comes from"
  horizon_secondary_features }|--|{ pan_continuities : "code comes from"
  horizon_secondary_features }|--|{ pan_structures : "code comes from"
  horizon_secondary_features }|--|{ stress_types : "code comes from"
  horizon_concentrations }|--|{ concentration_forms : "code comes from"
  horizon_concentrations }|--|{ concentration_types : "code comes from"
  horizon_coatings }|--|{ coating_types : "code comes from"
  horizon_bio_features }|--|{ bio_feature_types : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon secondary features - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_secondary_features ||--o{ "horizon_concentrations" : "may have some"
  horizon_secondary_features ||--o{ "horizon_coatings" : "may have some"
  horizon_secondary_features ||--o{ "horizon_bio_features" : "may have some"
  horizon_secondary_features }|--|{ concentration_forms : "code comes from"
  horizon_secondary_features }|--|{ concentration_forms : "code comes from"
  horizon_secondary_features }|--|{ concentration_types : "code comes from"
  horizon_secondary_features }|--|{ pan_types : "code comes from"
  horizon_secondary_features }|--|{ pan_continuities : "code comes from"
  horizon_secondary_features }|--|{ pan_structures : "code comes from"
  horizon_secondary_features }|--|{ stress_types : "code comes from"
  horizon_concentrations }|--|{ concentration_forms : "code comes from"
  horizon_concentrations }|--|{ concentration_types : "code comes from"
  horizon_coatings }|--|{ coating_types : "code comes from"
  horizon_bio_features }|--|{ bio_feature_types : "code comes from"
  
  %% data %%
  horizon_secondary_features {
    id          id_site PK
    id          id_horizon PK
    %% concentrations_rapid %%  
    cat         catu_concentration_form FK       "Section 16.1"
    num[0-100]  amt_concentration_p              "Section 16.1"
    %% coatings rapid still has to go in a subtable, see below %%
    %% rapid pans (you can only have one) %%
    cat         catu_pan_type FK                 "Section 16.3"
    %% add this stuff for details %%
    cat         cato_pan_continuity  FK          "Section 16.3"
    cat         catu_pan_structure FK            "Section 16.3"
    %% stress features (NB you get one or the other because Physics) %%
    cat         catu_stress_type FK              "Section 16.4"
    num[0-100]  amt_stress_p                     "Section 16.4"
  }
   
  %% data %%
  horizon_concentrations {
    id          id_site PK
    id          id_horizon PK
    id          id_concentration PK
    cat         catu_concentration_form FK       "Table 16.1"
    cat         catu_concentration_type FK       "Table 16.2"
    num[0-Inf]  amt_concentration_size_med_mm    "Section 16.1"
    num[0-Inf]  amt_concentration_size_min_mm    "Section 16.1"
    num[0-Inf]  amt_concentration_size_max_mm    "Section 16.1"
    num[0-100]  amt_concentration_med_p          "Section 16.1"
    num[0-100]  amt_concentration_min_p          "Section 16.1"
    num[0-100]  amt_concentration_max_p          "Section 16.1"
  } 

  concentration_forms {
    id          id_conc_form PK
    cat         catu_conc_form FK    "Table 16.1"
    txt         txt_conc_form_name   "Table 16.1"
    txt         txt_conc_form_desc   "Table 16.1"
  }

  concentration_types {
    id          id_conc PK
    cat         catu_conc FK         "Table 16.2"
    txt         txt_conc_form_name   "Table 16.2"
    txt         txt_conc_form_desc   "Table 16.2"
  }

  pan_types {
    id          id_pan PK
    cat         catu_pan FK         "Table 16.4"
    txt         txt_pan_name        "Table 16.4"
    txt         txt_pan_desc        "Table 16.4"
  }

  pan_continuities {
    id          id_pan_cont PK
    cat         catu_pan_cont FK    "Table 16.5"
    txt         txt_pan_cont_name   "Table 16.5"
    txt         txt_pan_cont_desc   "Table 16.5"
  }

  pan_structures {
    id          id_pan_structure PK
    cat         catu_pan_structure FK  "Table 16.6"
    txt         txt_pan_structure_name "Table 16.6"
    txt         txt_pan_structure_desc "Table 16.6"
  }
  
  stress_types {
    id          id_stress PK
    cat         catu_stress FK           "Table 16.7"
    txt         txt_stress_name          "Table 16.7"
    txt         txt_stress_desc          "Table 16.7"
  }

  %% data %%
  horizon_coatings {
    id          id_site PK
    id          id_horizon PK
    id          id_coating PK
    cat         catu_coating FK           "Section 16.2"
    num[0-100]  amt_coating_p             "Section 16.2"
  } 

  coating_types {
    id          id_coating PK
    cat         catu_coating FK         "Table 16.3"
    txt         txt_coating_name        "Table 16.3"
    txt         txt_coating_desc        "Table 16.3"
  }

  %% data %%
  horizon_bio_features {
    id          id_site PK
    id          id_horizon PK
    id          id_bio_feature PK
    cat         catu_bio_feature FK           "Section 16.5"
    num[0-100]  amt_bio_feature_p             "Section 16.5"
  }

  bio_feature_types {
    id          id_bio_feature PK
    cat         catu_bio_feature FK         "Table 16.8"
    txt         txt_bio_feature_name        "Table 16.8"
    txt         txt_bio_feature_desc        "Table 16.8"
  }

```

## Horizon consistence tests {#sec-erc-cons}

The consistence tests table can optionally extend the horizon overview table (@sec-erd-horizono) as it has a 1:1 relationship.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon consistence tests - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  consistence_tests }|--|{ strengths : "code comes from"
  consistence_tests }|--|{ sensitivities : "code comes from"
  consistence_tests }|--|{ plasticities : "code comes from"
  consistence_tests }|--|{ stickinesses : "code comes from"
  consistence_tests }|--|{ penrests : "code comes from"
  consistence_tests }|--|{ packings : "code comes from"
  consistence_tests }|--|{ failures : "code comes from"
  consistence_tests }|--|{ fluidities : "code comes from"
  consistence_tests }|--|{ indurations : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon consistence tests - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  consistence_tests }|--|{ strengths : "code comes from"
  consistence_tests }|--|{ sensitivities : "code comes from"
  consistence_tests }|--|{ plasticities : "code comes from"
  consistence_tests }|--|{ stickinesses : "code comes from"
  consistence_tests }|--|{ penrests : "code comes from"
  consistence_tests }|--|{ packings : "code comes from"
  consistence_tests }|--|{ failures : "code comes from"
  consistence_tests }|--|{ fluidities : "code comes from"
  consistence_tests }|--|{ indurations : "code comes from"
  
  consistence_tests {
    id          id_site PK
    id          id_horizon PK
    bool        ind_cohesive                "Section 17.1.1"
    cat         cato_strength_unconfined FK "Section 17.2.1"
    cat         cato_strength_ped FK        "Section 17.2.2"
    cat         cato_strength_remoulded FK  "Section 17.2.3"
    cat         cato_strength_plastic FK    "Section 17.2.4"
    cat         cato_sensitivity FK         "Section 17.3"
    cat         cato_plasticity FK          "Section 17.4"
    cat         cato_stickiness FK          "Section 17.5"
    bool        ind_dilatent                "Section 17.6"
    cat         cato_penetration_rest FK    "Section 17.7"
    cat         cato_packing FK             "Section 17.8"
    cat         catu_failure FK             "Section 17.9"
    cat         cato_fluidity FK            "Section 17.10"
    cat         cato_induration FK          "Section 17.11"
  }

  strengths {
    id          id_strength PK
    cat         catu_strength FK         "Table 17.1"
    txt         txt_strength_name        "Table 17.1"
    txt         txt_strength_desc        "Table 17.1"
  }

  sensitivities {
    id          id_sensitivity PK
    cat         catu_sensitivity FK         "Table 17.2"
    txt         txt_sensitivity_name        "Table 17.2"
    txt         txt_sensitivity_desc        "Table 17.2"
  }

  plasticities {
    id          id_plasticity PK
    cat         catu_plasticity FK         "Table 17.3"
    txt         txt_plasticity_name        "Table 17.3"
    txt         txt_plasticity_desc        "Table 17.3"
  }

  stickinesses {
    id          id_stickiness PK
    cat         catu_stickiness FK         "Table 17.4"
    txt         txt_stickiness_name        "Table 17.4"
    txt         txt_stickiness_desc        "Table 17.4"
  }

  penrests {
    id          id_penrest PK
    cat         catu_penrest FK         "Table 17.5"
    txt         txt_penrest_name        "Table 17.5"
    txt         txt_penrest_desc        "Table 17.5"
  }

  packings {
    id          id_packing PK
    cat         catu_packing FK         "Table 17.6"
    txt         txt_packing_name        "Table 17.6"
    txt         txt_packing_desc        "Table 17.6"
  }

  failures {
    id          id_failure PK
    cat         catu_failure FK         "Table 17.7"
    txt         txt_failure_name        "Table 17.7"
    txt         txt_failure_desc        "Table 17.7"
  }
  
  fluidities {
    id          id_fluidity PK
    cat         catu_fluidity FK         "Table 17.8"
    txt         txt_fluidity_name        "Table 17.8"
    txt         txt_fluidity_desc        "Table 17.8"
  }  

  indurations {
    id          id_induration PK
    cat         cato_induration FK         "Table 17.9"
    txt         txt_induration_name        "Table 17.9"
    txt         txt_induration_desc        "Table 17.9"
  }    
  
```

## Field tests {#sec-erd-fts}

Field tests are conducted from samples taken at set depths so in this example are connected to sites rather than horizons. It is also possible to nest them within horizons.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon field tests - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  field_tests }|--|{ pH_raupachs : "code comes from"
  field_tests }|--|{ h2o2_rxns : "code comes from"
  field_tests }|--|{ hcl_rxns : "code comes from"
  field_tests }|--|{ naf_rxns : "code comes from"
  field_tests }|--|{ slaking_rxns : "code comes from"
  field_tests }|--|{ dispersion_rxns : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon field tests - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  field_tests }|--|{ pH_raupachs : "code comes from"
  field_tests }|--|{ h2o2_rxns : "code comes from"
  field_tests }|--|{ hcl_rxns : "code comes from"
  field_tests }|--|{ naf_rxns : "code comes from"
  field_tests }|--|{ slaking_rxns : "code comes from"
  field_tests }|--|{ dispersion_rxns : "code comes from"
  
  field_tests {
    id          id_site PK
    num[0-Inf]  amt_test_depth_cm
    cat         cato_pH_raupach FK  "Section 18.1.1"
    num[0-14]   val_pH_water        "Section 18.1.2"
    num[0-14]   val_pH_h2o2         "Section 18.1.3"
    cat         cato_h2o2_rxn FK    "Section 18.1.3"
    num[0-Inf]  amt_ec_dSm          "Section 18.1.4"
    cat         cato_hcl_rxn FK     "Section 18.1.5"
    bool        ind_mn_rxn          "Section 18.1.6"
    cat         cato_naf_rxn FK     "Section 18.1.7"
    bool        ind_22bip_rxn       "Section 18.1.8"
    bool        ind_water_repellent "Section 18.1.9"
    cat         cato_slaking FK     "Section 18.1.10"
    cat         cato_dispersion FK  "Section 18.1.10"
  }

  pH_raupachs {
    id          id_raupach PK
    cat         catu_raupach FK          "2.0, 2.5 etc"
  }

  h2o2_rxns {
    id          id_h2o2_rxn PK
    cat         catu_h2o2_rxn FK        "Table 18.1"
    txt         txt_h2o2_rxn_name       "Table 18.1"
    txt         txt_h2o2_rxn_desc       "Table 18.1"
  }

  hcl_rxns {
    id          id_hcl_rxn PK
    cat         catu_hcl_rxn FK         "Table 18.2"
    txt         txt_hcl_rxn_name        "Table 18.2"
    txt         txt_hcl_rxn_desc        "Table 18.2"
  }

  naf_rxns {
    id          id_naf_rxn PK
    cat         catu_naf_rxn FK         "Table 18.3"
    txt         txt_naf_rxn_name        "Table 18.3"
    txt         txt_naf_rxn_desc        "Table 18.3"
  }

  slaking_rxns {
    id          id_slaking_rxn PK
    cat         catu_slaking_rxn FK     "Table 18.4"
    txt         txt_slaking_rxn_name    "Table 18.4"
    txt         txt_slaking_rxn_desc    "Table 18.4"
  }
  
  dispersion_rxns {
    id          id_dispersion_rxn PK
    cat         catu_dispersion_rxn FK  "Table 18.5"
    txt         txt_dispersion_rxn_name "Table 18.5"
    txt         txt_dispersion_rxn_desc "Table 18.5"
  }
  
```

## Interpretation data {#sec-erd-interp}

### NZSC {#sec-erd-nzsc}

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "NZSC - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  nzsc }|--|{ nzsc_orders : "code comes from"
  nzsc }|--|{ nzsc_groups : "code comes from"
  nzsc }|--|{ nzsc_subgroups : "code comes from"
  nzsc }|--|{ nzsc_fam_profile_materials : "code comes from"
  nzsc }|--|{ nzsc_lithologies : "code comes from"
  nzsc }|--|{ nzsc_fam_textures : "code comes from"
  nzsc }|--|{ nzsc_fam_permeabilities : "code comes from"
  nzsc }|--|{ nzsc_fam_pm_origins : "code comes from"
  nzsc }|--|{ nzsc_sib_depths : "code comes from"
  nzsc }|--|{ nzsc_sib_tsstones : "code comes from"
  nzsc }|--|{ nzsc_sib_textures : "code comes from"
  nzsc }|--|{ nzsc_sib_drainages : "code comes from"
  nzsc }|--|{ nzsc_sib_drainage_mods : "code comes from"
  nzsc }|--|{ rooting_barriers : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "NZSC - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  nzsc }|--|{ nzsc_orders : "code comes from"
  nzsc }|--|{ nzsc_groups : "code comes from"
  nzsc }|--|{ nzsc_subgroups : "code comes from"
  nzsc }|--|{ nzsc_fam_profile_materials : "code comes from"
  nzsc }|--|{ nzsc_lithologies : "code comes from"
  nzsc }|--|{ nzsc_fam_textures : "code comes from"
  nzsc }|--|{ nzsc_fam_permeabilities : "code comes from"
  nzsc }|--|{ nzsc_pm_origins : "code comes from"
  nzsc }|--|{ nzsc_sib_depths : "code comes from"
  nzsc }|--|{ nzsc_sib_tsstones : "code comes from"
  nzsc }|--|{ nzsc_sib_textures : "code comes from"
  nzsc }|--|{ nzsc_sib_drainages : "code comes from"
  nzsc }|--|{ drainage_mods : "code comes from"
  nzsc }|--|{ rooting_barriers : "code comes from"
  
  nzsc {
    id          id_site PK
    cat         catu_nzsc_order FK                "Hewitt et al, 2024"
    cat         catu_nzsc_group FK                "Hewitt et al, 2024"
    cat         catu_nzsc_subgroup FK             "Hewitt et al, 2024"
    cat         catu_nzsc_fam_profile_material FK "Section 24.1.1"
    cat         catu_nzsc_fam_stones_rock FK      "Section 24.1.2"
    cat         catu_nzsc_fam_fines_rock FK       "Section 24.1.2"
    cat         catu_nzsc_fam_texture FK          "Section 24.1.3"
    cat         catu_nzsc_fam_permeability FK     "Section 24.1.4"
    cat         catu_nzsc_fam_pm_origin FK        "Section 24.1.5"
    cat         catu_nzsc_sib_depth FK            "Section 24.2.1"
    cat         catu_nzsc_sib_tsstone FK          "Section 24.2.2"
    cat         catu_nzsc_sib_texture FK          "Section 24.2.3"
    cat         catu_nzsc_sib_drainage FK         "Section 24.2.4"
    cat         catu_nzsc_sib_drainage_mod FK     "Section 24.2.4.1"
    cat         catu_rooting_barrier FK           "Section 24.3"
    int[0-Inf]  val_rooting_barrier_horizon       "Section 24.3.1"
  }

  nzsc_orders {
    id          id_nzsc_order PK    "Hewitt et al, 2024"
    cat         catu_nzsc_order FK  "Hewitt et al, 2024"
    txt         txt_nzsc_order_name "Hewitt et al, 2024"
    }

  nzsc_groups {
    id          id_nzsc_group PK    "Hewitt et al, 2024"
    cat         catu_nzsc_group FK  "Hewitt et al, 2024"
    txt         txt_nzsc_group_name "Hewitt et al, 2024"
    }

  nzsc_subgroups {
    id          id_nzsc_subgroup PK    "Hewitt et al, 2024"
    cat         catu_nzsc_subgroup FK  "Hewitt et al, 2024"
    txt         txt_nzsc_subgroup_name "Hewitt et al, 2024"
    }

  nzsc_fam_profile_materials {
    id          id_nzsc_fam_pm PK
    cat         catu_nzsc_fam_pm FK          "Table 24.1"
    txt         txt_nzsc_fam_pm_name         "Table 24.1"
    txt         txt_nzsc_fam_pm_desc         "Table 24.1"
    }

  nzsc_lithologies {
    id          id_nzsc_lithology PK    
    cat         catu_nzsc_lithology FK        "Table 24.2"
    txt         txt_nzsc_lithology_name       "Table 24.2"
  }
  
  nzsc_fam_textures {
    id          id_nzsc_fam_texture PK    
    cat         catu_nzsc_fam_texture FK        "Table 24.3"
    txt         txt_nzsc_fam_texture_name       "Table 24.3"
  }

  nzsc_fam_permeabilities {
    id          id_nzsc_fam_perm PK     
    cat         catu_nzsc_fam_perm FK        "Table 24.4"
    txt         txt_nzsc_fam_perm_name       "Table 24.4"
    txt         txt_nzsc_fam_perm_desc       "Table 24.4"
  }  
  
  nzsc_pm_origins {
    id          id_nzsc_pm_origin PK    
    cat         catu_nzsc_pm_origin FK        "Table 24.5"
    txt         txt_nzsc_pm_origin_name       "Table 24.5"
    txt         txt_nzsc_pm_origin_desc       "Table 24.5"

}

  nzsc_sib_depths {
    id          id_nzsc_sib_depth PK    
    cat         catu_nzsc_sib_perm FK        "Table 24.6"
    txt         txt_nzsc_sib_perm_name       "Table 24.6"
    txt         txt_nzsc_sib_perm_desc       "Table 24.6"
  }

  nzsc_sib_tsstones {
    id          id_nzsc_sib_tsstone PK    
    cat         catu_nzsc_sib_tsstone FK        "Table 24.7"
    txt         txt_nzsc_sib_tsstone_name       "Table 24.7"
    txt         txt_nzsc_sib_tsstone_desc       "Table 24.7"
  }

  nzsc_sib_textures {
    id          id_nzsc_sib_texture PK    
    cat         catu_nzsc_sib_texture FK        "Table 24.8"
    txt         txt_nzsc_sib_texture_name       "Table 24.8"
  }
  
  nzsc_sib_drainages {
    id          id_nzsc_sib_drainage PK    
    cat         catu_nzsc_sib_drainage FK       "Table 24.9"
    txt         txt_nzsc_sib_drainage_name      "Table 24.9"
    txt         txt_nzsc_sib_drainage_desc      "Table 24.9"
  }

  drainage_mods {
    id          id_drainage_mod PK 
    cat         catu_drainage_mod FK       "Table 24.10"
    txt         txt_drainage_mod_name      "Table 24.10"
    txt         txt_drainage_mod_desc      "Table 24.10"
  }
  
  rooting_barriers {
    id          id_rooting_barrier  PK  
    cat         catu_rooting_barrier  FK      "Table 24.11/12"
    txt         txt_rooting_barrier_name      "Table 24.11/12"
    txt         txt_rooting_barrier_desc      "Table 24.11/12"
  }
```

### Horizon names {#sec-erd-names}

The horizon names tables stores conventional and functional names. This table can optionally extend the horizon overview table as it has a 1:1 relationship (@sec-erd-horizono). Horizon drainage class is also stored here.

::: {.content-hidden when-format='docx'}
```{mermaid}
%%| fig-cap: "Horizon names - Overview"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_names }|--|{ horizon_names_primary : "code comes from"
  horizon_names }|--|{ horizon_names_secondary : "code comes from"
  horizon_names }|--|{ horizon_suffix_redox : "code comes from"
  horizon_names ||--o{ horizon_names_suffix_other : "may have some"
  horizon_names_suffix_other }|--|{ horizon_suffix_other : "code comes from"
  horizon_names }|--|{ horizon_names_functional : "code comes from"
  horizon_names }|--|{ functional_horizon_permeabilities : "code comes from"
  horizon_names }|--|{ horizon_drainage_classes : "code comes from"

```
:::

```{mermaid}
%%| fig-cap: "Horizon names - Details"

%%{ init: { 'er': { 'layoutDirection': 'LR' } } }%%
erDiagram
  horizon_names }|--|{ horizon_names_primary : "code comes from"
  horizon_names }|--|{ horizon_names_secondary : "code comes from"
  horizon_names }|--|{ horizon_suffix_redox : "code comes from"
  horizon_names ||--o{ horizon_names_suffix_other : "may have some"
  horizon_names_suffix_other }|--|{ horizon_suffix_other : "code comes from"
  horizon_names }|--|{ horizon_names_functional : "code comes from"
  horizon_names }|--|{ functional_horizon_permeabilities : "code comes from"
  horizon_names }|--|{ horizon_drainage_classes : "code comes from"
  
  %% data %%
  horizon_names {
    id          id_site PK
    id          id_horizon PK
    %% for very rapid assessment %%
    cat         catu_horizon_primary FK      "Section 20.1.1"
    cat         catu_horizon_secondary FK    "Section 20.1.2"
    cat         catu_horizon_suffix_redox FK "Section 20.1.3"
    %% 'other' 1:n so in subtable see below %%
    int[2-Inf]  val_lith_discont             "Section 20.1.5"
    int[2-Inf]  val_duplicate                "Section 20.1.6"
    bool        ind_buried                   "Section 20.1.7"
    %% for edge cases %%
    txt         txt_horizon_name_full
    %% FH
    cat         catu_functional_name FK      "Section 20.2"
    cat         cato_functional_perm FK      "Section 20.2.1.8"
    bool        ind_topsoil                  "Table 20.1"
    bool        ind_tephra_acid              "Table 20.2"
    bool        ind_tephra_basic             "Table 20.2"
    cat         cato_horizon_drainage        "Section 21.3"
  }
  
  %% data %%
  horizon_names_suffix_other {
    id          id_site PK
    id          id_horizon PK
    id          id_suffix
    cat         catu_horizon_suffix          "Section 20.1.4"
  }

  horizon_names_primary {
    id          id_horizon_primary PK     
    cat         catu_horizon_primary FK        "Table 20.1"
    txt         txt_horizon_primary_name       "Table 20.1"
    txt         txt_horizon_primary_desc       "Table 20.1"
  }

  horizon_names_secondary {
    id          id_horizon_secondary PK     
    cat         catu_horizon_secondary FK      "Tables 20.2-20.7"
    txt         txt_horizon_secondary_name     "Tables 20.2-20.7"
    txt         txt_horizon_secondary_desc     "Tables 20.2-20.7"
  }

  horizon_suffix_redox {
    id          id_horizon_suffix_redox PK    
    cat         catu_horizon_suffix_redox FK        "Tables 20.8"
    txt         txt_horizon_suffix_redox_name       "Tables 20.8"
    txt         txt_horizon_suffix_redox_desc       "Tables 20.8"
  }
  
  horizon_suffix_other {
    id          id_horizon_suffix_other PK    
    cat         catu_horizon_suffix_other FK        "Tables 20.9"
    txt         txt_horizon_suffix_other_name       "Tables 20.9"
    txt         txt_horizon_suffix_other_desc       "Tables 20.9"
  }

  horizon_names_functional {
    id          id_horizon_name_func PK   
    cat         catu_horizon_name_func FK        "Table 20.18"
    txt         txt_horizon_name_func_desc       "Table 20.18"
  }

  functional_horizon_permeabilities {
    id          id_horizon_perm PK  
    cat         cato_functional_perm FK        "Table 20.17"
    txt         txt_functional_perm_name       "Table 20.17"
    txt         txt_horizon_name_func_desc     "Table 20.17"
  }
  
  horizon_drainage_classes {
    id          id_horizon_drainage PK
    cat         cato_horizon_drainage FK     "Table 21.5"
    txt         txt_horizon_drainage_name    "Table 21.5"
  }
  
```

::: {.content-visible when-format='docx'}
delete me 
:::

::: {#nte-hzn .callout-note collapse="true" appearance="minimal" title="Storing conventional horizon names"}
Conventional horizon names in the New Zealand system are difficult to store in a structured manner due to the large number of ways the various components can be combined (@sec-hzn-conv). The above implementation can store most of the possible variations in a structured manner, leaving a free-text field for edge cases. 
:::

## Helper tables

### Measurement type

Some numeric parameters require a separate specification for types of missing data (e.g. aspect at the site, @sec-pr-aspect). These codes are built in to most categorical code lists in this standard (where it makes sense to include them). For numeric data, a companion text column is required. It is generally expected that it will be populated in a semi-automated manner - for each parameter, it should default to [NR]{.ceg}, and be automatically updated to [NM]{.ceg} if a value is supplied. Users may also update to [ND]{.ceg} as needed.

```{r}
#| label: tbl-missing
#| tbl-cap: "Data record type"

dat_ss_eventt <-
  tribble(~Code, ~Name,
            'NM', 'Parameter was recorded normally',
            'ND', 'Parameter could not be reliably determined or does not apply',
            'NR', 'Parameter was not recorded')

tbl_ss_eventt <- gt(dat_ss_eventt) |>
   tab_options(
    column_labels.font.weight = 'bold', 
    heading.title.font.weight = 'bold',
    table.align = 'center',
    table.width = '85%'
  ) |>
  tab_style(style = list(cell_text(color  = 'red', 
                                   weight = 'bold',
                                   align  = 'center')),
            locations = cells_body(columns = 1)) |>
  cols_width(Code ~pct(10))

tbl_ss_eventt
```