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, usegeom
for the default geometry column andgeom_{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 selectingProperties
) 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_sanctuariesIt 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 tabletable_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 tablecolumn
: name of columncolumn_type
: data type of columncolumn_description
: description of column (possibly in markdown)
- api.calcofi.io
Fetch and display these descriptions into an interactive table with
calcofi4r::
cc_db_catalog()
.
5.3 Relationships between tables
TODO:
add calcofi/apps: db to show latest tables, columns and relationsips
5.4 Spatial Tips
- Use
ST_Subdivide()
when running spatial joins on large polygons.