Ch. 5 Database
5.1 Relational Database Structure
5.1.1 Typography
-
{*}
: indicates variable substitution, e.g.{mdl_key}_mdls
would evaluate to the valueam_mdls
formdl_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.