Skip to main content

CalCOFI Docs

Ch. 5 Database

5.1 Relational Database Structure

5.1.1 Typography

  • {*}: indicates variable substitution, e.g. {mdl_key}_mdls would evaluate to the value am_mdls for mdl_id = "am" (AquaMaps)
  • [*]: optional value, such as [ply_grp] is an optional column in the {mdl_key}_mdls table
  • <*>: surrounds the columns used to uniquely identify (and index) each row
  • ...: additional columns, unique to the table

The format below is of the following format where the top line of a bulleted list item describes the table and the columns in that table are directly below, nested in hierarchical order:

  • {table name} ({description})
    <{column 1}, {column 2}>, {column 3}, ...

5.1.2 Database Naming Conventions

  • Use all lower-case column names with underscores (i.e. from using janitor::clean_names()) to prevent need to quote SQL statements.
  • For short unique identifiers use suffix *_id for integer and *_key for short text.

5.2 Spatial Tips and Conventions

  • Set PostGIS geometry fieldname to geom.
  • Use ST_Subdivide() when running spatial joins on large polygons.