- use as many constraints as possible
- prefer types to express constraints
- also encode "business logic" in the DB
- as manual SQL queries are done, too, don't assume that backend constraints are enforced anyway
- we don't use float, prefer enums over numbers, integers over text
- size/position is given in cm (integer), e.g.
15cm will be represented by 15
- rotation/scaling is given with scaling of
100 (integer with decimal fixed-point of 3 digits), e.g. 0.5° will be represented by 50
- use all features from PostgreSQL or PostGIS and all their guidelines
- exceptions: we don't use XML, JSON or JSONB in the DB (and obviously also nothing but text in
TEXT)
- use
snake_case for everything except database keywords, which should be UPPERCASE
- prefer
TEXT over VARCHAR(n), don't use other variants
- enum names should be singular
- on conflicts with other names, the types should get a
_type prefix
- table names should be plural (only the last word though, i.e.,
plant_traits)
- use
_props or _traits for properties or traits, e.g. free_line_points
- prefer
UUID v7 as primary keys, usually simply called id
- except for tables that are not subject to concurrent use (backend creates new items)
- foreign keys are called other table name in singular +
id, e.g. layer_id.
- prefer
BIGSEQUENCE over SEQUENCE