5  Database

5.1 Database naming conventions

We’re circling the wagons to come up with the best conventions for naming. Here are some ideas:

5.1.1 Name tables

  • Table names are plural and use all lower case.

5.1.2 Name columns

  • To name columns, use snake-case (i.e., lower-case with underscores) so as to prevent the need to quote SQL statements. (TIP: Use janitor::clean_names() to convert a table.)

  • Unique identifiers are suffixed with:

    • *_id for unique integer keys;
    • *_key for unique string keys;
    • *_seq for auto-incrementing sequence integer keys.

  • Suffix with units where applicable (e.g., *_m for meters, *_km for kilometers, degc for degrees Celsius). See units vignette.

  • Set geometry column to geom (used by PostGIS spatial extension). If the table has multiple geometry columns, use geom for the default geometry column and geom_{type} for additional geometry columns (e.g., geom_point, geom_line, geom_polygon).

5.2 Describe tables and columns

  • Use the COMMENT clause to add descriptions to tables and columns, either through the GUI pgadmin.calcofi.io (by right-clicking on the table or column and selecting Properties) or with SQL. For example:

    COMMENT ON TABLE public.aoi_fed_sanctuaries IS 'areas of interest (`aoi`) polygons for federal **National Marine Sanctuaries**; loaded by _workflow_ [load_sanctuaries](https://calcofi.io/workflows/load_sanctuaries.html)';

  • Note the use of markdown for including links and formatting (e.g., bold, code, italics), such that the above SQL will render like so:

    areas of interest (aoi) polygons for federal National Marine Sanctuaries; loaded by workflow load_sanctuaries

  • It is especially helpful to link to any workflows that are responsible for the ingesting or updating of the input data.

  • These descriptions can be viewed in the CalCOFI API api.calcofi.io as CSV tables (see code in calcofi/api: plumber.R):

    • api.calcofi.io/db_tables
      fields:
      • schema: (only “public” so far)
      • table_type: “table”, “view”, or “materialized view” (none yet)
      • table: name of table
      • table_description: description of table (possibly in markdown)
    • api.calcofi.io/db_columns
      fields:
      • schema: (only “public” so far)
      • table_type: “table”, “view”, or “materialized view” (none yet)
      • table: name of table
      • column: name of column
      • column_type: data type of column
      • column_description: description of column (possibly in markdown)

  • Fetch and display these descriptions into an interactive table with calcofi4r::cc_db_catalog().

5.3 Relationships between tables

5.4 Spatial Tips