PostgreSQL

Naming conventions

• Write keywords such as SELECT, WHERE, AND, OR etc. as all uppercase letters. Following PostgreSQL's website e.g. https://www.postgresql.org/docs/15/tutorial-populate.html

Foreign-key relationships

It’s best to avoid using foreign-key relationships in our database schemas where possible. This is to maintain flexibility and scalability in our systems. Foreign-key constraints can introduce tight coupling between tables, which complicates schema changes and migrations as our applications evolve.

JSON vs JSONB

Numeric (13,2)

We use numeric(13,2) for all financial amounts e.g. rates, or charged_amount.

ORMS

For more in-depth knowledge on PostgreSQL see https://gitlab.bob.co.za/bob-tools/bobgroup-postgresql-lab