Backend Databases · Notes

Backend Databases — Deep Dive Notes

PostgreSQL · Schema Design · Migrations · Indexes · Triggers
Video walkthrough → exam-ready notes

Contents
1. Why Databases? (Persistence)
2. What is a Database? (Broad vs. Backend definition)
3. Disk vs RAM — why DBs are disk-based
4. DBMS — Responsibilities
5. Why not just text files?
6. Relational vs Non-Relational
7. Why PostgreSQL (over MySQL, Mongo, etc.)
8. PostgreSQL Data Types (full tour)
9. Database Migrations (up/down, dbmate)
10. Designing the Project Management Schema
10.1 ENUMs (data integrity + documentation)
10.2 Users table + constraints
10.3 One-to-One: user_profiles
10.4 Projects + Referential Integrity (CASCADE/RESTRICT/SET NULL)
10.5 One-to-Many: tasks
10.6 Many-to-Many: project_members (linking table, composite PK)
11. Seeding test data
12. Building queries for APIs
12.1 GET /users — joins, JSONB embedding
12.2 GET /users/:id — parameterized queries & SQL injection
12.3 Dynamic filter / sort / pagination
12.4 POST /users — insert + RETURNING
12.5 PATCH /users/:id — partial update
13. Indexes — what, why, when
14. Triggers — auto-update updated_at
15. Backend Engineer Cheat-Sheet

1. Why Do We Need Databases?

At its core, a database is a way to persist information across sessions.
Persistence = data survives even after the program that created it has stopped running.

STORY Example: A to-do list app. You add tasks, tick some off, close the app. Open it
again next week — everything is still there, in the same state. That is persistence.
Without it, every time you reopen the app you’d start from zero — all progress
lost.

Page 1 of 17
 Backend Databases · Notes

What counts as a database? (Broad definition)

In the simplest sense, any structured storage is a database. Examples:
• Phone contact list
• Browser localStorage / sessionStorage / cookies (key-value stores you can see in DevTools)
• A plain .txt file you take notes in
Pattern across all of these:
• Some persistent system
• That supports CRUD — Create, Read, Update, Delete

NOTE In backend context, "database" almost always means disk-based database (HDD
or SSD), accessed through a DBMS.

2. Why Disk-Based? (Disk vs RAM)

CPU can access two layers of memory:
Aspect RAM (Primary) Disk (Secondary)

Speed Very fast Slower

Cost Expensive per GB Cheap per GB

Typical size 8 / 16 / 32 / 64 / 128 GB 512 GB → 2 TB+

Volatile? Yes — lost on power off No — persists

Best for Caching (Redis, in-memory caches) Long-term storage (Postgres, Mongo,
MySQL)

Databases prioritize space over speed → disk-based by default. Caches (Redis, etc.) live in RAM because speed >
capacity for them.

EXAM One-liner: Caches → RAM (speed). Databases → Disk (capacity, persistence,

cost).

3. DBMS — Database Management System

Just dumping data on disk is not enough. We need software that does it efficiently. That software is the DBMS.

Four core responsibilities

# Responsibility What it means

1 Data Organization Efficient storage layout so CRUD is fast

2 Access Provide methods for CRUD (Create, Read, Update, Delete)

Page 2 of 17
 Backend Databases · Notes

# Responsibility What it means

3 Integrity Guarantee data is accurate, valid, not corrupt (type checks,

constraints)

4 Security Protect data from unauthorized access (users, roles, permissions)

STORY Integrity example: E-commerce orders table has a payment_amount column as

a number. If someone tries to insert "something" (a string) into that field, the
DBMS must reject it. That guarantee = integrity.

4. Why Not Just Use Text Files?

Before DBMS software existed, people did store data in text files. It breaks down badly. Four big problems:
Problem Why it hurts

Parsing For every query you write app code to read, split lines, compare fields. Slow +
error-prone. Languages like JS / Python are even slower at this.

No structure Text files have no schema. You cannot enforce "this column must be a number".
Data corruption is silent.

Concurrency Two users updating the same record at the same time → whoever writes last
wins; the other update is lost. No locking, no transactions.

No security / no No roles, no fast lookup. Searching a million-line text file is O(n) every time.
indexes

STORY Concurrency demo: Amount = 40. User A wants +20, User B wants −20. Both
read 40 simultaneously. A writes 60, B writes 20. Final value depends purely on
which write hits disk last → 60 or 20, unpredictably. A real DBMS uses
transactions + locks to prevent this.

5. Relational vs Non-Relational

Relational (SQL)
• Data lives in tables (rows + columns).
• Relationships between tables defined via foreign keys.
• Predefined, strict schema — types and constraints declared up front.
• Query language: SQL (Structured Query Language).
• Examples: PostgreSQL, MySQL, SQL Server, SQLite.

Non-Relational (NoSQL)
• Flexible schema — each document can have a different shape.
Page 3 of 17
 Backend Databases · Notes

• Table → Collection, Row → Document (MongoDB terminology).

• Great for prototypes / unknown-shape content.
• Examples: MongoDB, DynamoDB, Cassandra, Firestore.

Side-by-Side
Dimension Relational (Postgres) Non-Relational (Mongo)

Schema Strict, predefined Flexible, per-document

Terminology Table / Row Collection / Document

Integrity Strong (DB-level) Weaker (must be enforced in app)

Relationships Foreign keys, joins Embedded docs / refs (no real joins)

Best use case CRM, banking, transactional systems CMS, unstructured content, fast
prototyping

Query language SQL (standard) Vendor-specific query APIs

STORY CRM example (relational fit): Customer records, sales, opportunities — needs
accuracy, joins, integrity → Postgres.
CMS example (non-relational fit): Blog articles with text + images + code blocks +
YouTube embeds — shape varies per article → MongoDB.

6. Why PostgreSQL? (The Default Choice)

When you must pick a single database, Postgres wins most of the time. Five reasons:
# Reason Why it matters

1 Open source & free No license fees, you can self-host, big community.

2 Sticks to the SQL standard Queries port to other SQL engines with minimal changes.

3 Extensible ~1400 pages of docs; rich extension ecosystem (PostGIS, pgvector,

etc).

4 Reliable & scalable Battle-tested at huge scale.

5 Strong JSON / JSONB Indexable JSON fields → kills most reasons to reach for MongoDB.
support

TIP Rule of thumb: Until you’re serving millions of users with a specific bottleneck,
"Should I use MySQL or Postgres?" doesn’t matter. Pick Postgres and move on.

Page 4 of 17
 Backend Databases · Notes

7. PostgreSQL Data Types — Quick Tour

Auto-increment IDs
Type Use when

SERIAL Auto-incrementing integer PK; smaller capacity

BIGSERIAL Same, larger capacity → preferred in production

Integers
Scale (smallest → largest):
SMALLINT < INTEGER < BIGINT

Decimal vs Floating Point

Type Behavior Use when

DECIMAL(p,s) / Exact precision. p = total digits, s = Money / price / anything where

NUMERIC(p,s) digits after point. 0.01 matters

REAL / DOUBLE PRECISION / Approximate (IEEE 754). Faster, less Scientific calc, sizes, sensor
FLOAT accurate. readings — small drift OK

EXAM DECIMAL(10,2) → total 10 digits, 2 after decimal point. Example:

12345678.90. Cannot store 123456789.00 (11 digits).

Strings — CHAR vs VARCHAR vs TEXT

Type Behavior When to use

CHAR(n) Pads with spaces to length n always. ONLY if length is truly fixed (e.g. day
codes "Mo", "Tu")

VARCHAR(n) Variable length, max n characters. Legacy. Mostly inherited from

MySQL convention.

TEXT Variable length, no enforced max. Default choice in Postgres.

Recommended by docs.

TRAP Anti-pattern: Using VARCHAR(255) "just because". 255 is meaningful in MySQL,

NOT in Postgres. People copy-paste it without thinking. New devs then assume
the number has meaning. It doesn’t.
Fix: Use TEXT in Postgres. Enforce length at the application layer. No
performance difference vs VARCHAR.

Page 5 of 17
 Backend Databases · Notes

Other commonly used types

Type Stores

BOOLEAN true / false

DATE Date only (YYYY-MM-DD)

TIME Time only (HH:MM:SS)

TIMESTAMP Date + time

TIMESTAMPTZ Date + time + timezone (preferred for production)

INTERVAL Durations ("10 days", "1 week")

UUID Universally unique ID — great for primary keys

JSON JSON stored as text

JSONB JSON stored in binary format — indexable, faster queries → preferred

ARRAY Array of any other type (INT[], TEXT[], JSONB[]…)

INET / CIDR / MACADDR Network addresses

POINT / LINE / POLYGON Geometry primitives

XML XML documents

NOTE JSON vs JSONB: JSON stores raw text. JSONB parses and stores a binary
representation → faster lookups, supports indexing. Default to JSONB.

8. Database Migrations
You don’t run SQL by hand on production. You write migrations — versioned SQL files that a CLI tool applies in
order.

Folder structure
TREE

db/
migrations/
20260501120000_create_users_table.sql
20260501130000_seed_data.sql
20260502090000_add_indexes_and_triggers.sql

Page 6 of 17
 Backend Databases · Notes

Up / Down structure of a single migration file

SQL

-- migrate:up
CREATE TABLE users ( ... );

-- migrate:down
DROP TABLE users;

Section Purpose

migrate:up The change you want to apply (CREATE, ALTER, etc.)

migrate:down The reverse — undoes the up so you can roll back to a previous version

Why migrations?
• Track every schema change over time (committed to git like code).
• Roll back if something goes wrong in production.
• Reproduce same schema across dev / staging / prod environments.
• Tool maintains a hidden table (e.g. schema_migrations) tracking the current version, so it only applies
new files.

Popular migration tools

• dbmate (used in the video)
• golang-migrate
• Flyway, Liquibase (Java ecosystem)
• Knex / Sequelize / Prisma migrate ([Link] ORM-bound)

dbmate commands (cheat-sheet)

BASH

# Create a new migration file

dbmate new create_users_table

# Run all pending up migrations

dbmate up

# Roll back the last migration

dbmate down

# Check status
dbmate status

Page 7 of 17
 Backend Databases · Notes

9. Designing the Project-Management Schema

Five tables, three relationships:
• users ⟷ user_profiles → one-to-one
• users ⟶ projects (owner) → one-to-many
• projects ⟶ tasks → one-to-many
• users ⟷ projects (members) → many-to-many via project_members

9.1 ENUM Types — Integrity + Documentation

An ENUM is a custom type with a fixed, allowed set of string values. Insert anything outside the set → DB-level
error.
SQL

CREATE TYPE project_status AS ENUM ('active', 'completed', 'archived');

CREATE TYPE task_status AS ENUM ('pending', 'in_progress', 'completed',
'cancelled');
CREATE TYPE member_role AS ENUM ('owner', 'admin', 'member');

Two reasons to use ENUMs (not just TEXT)

# Reason Why it matters

1 Data integrity DB rejects invalid values automatically — defense in depth, not just
app-level validation.

2 Self-documentation New teammates can read the migration and immediately see the
allowed values without grepping the codebase.

9.2 users Table

SQL

CREATE TABLE users (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
email TEXT NOT NULL UNIQUE,
full_name TEXT NOT NULL,
password_hash TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

Constraints — quick refresher

Constraint What it enforces

PRIMARY KEY Uniquely identifies a row. Implies NOT NULL + UNIQUE + indexed.

Page 8 of 17
 Backend Databases · Notes

Constraint What it enforces

NOT NULL Field cannot be NULL.

UNIQUE No two rows can share the same value.

DEFAULT Value used when INSERT omits the column.

CHECK Custom boolean condition that must be true (e.g. priority BETWEEN 1 AND 5).

FOREIGN KEY Value must exist as a PK in the referenced table.

EXAM Default to NOT NULL on most columns. Nullable should be the exception, not
the rule. Nullable fields silently let bugs into the data.

Convention notes
• Table names: plural (users, projects, tasks).
• Field names: snake_case lowercase. Postgres folds unquoted identifiers to lowercase — so camelCase
requires "double quotes" everywhere → avoid.

9.3 user_profiles — One-to-One Relationship

Why a separate table instead of cramming everything into users?
• Profiles grow over time (avatar, bio, phone, socials, websites…).
• Splitting it keeps the core users table small, frequently-read, and stable.
• Cleaner separation = fewer accidental migrations on the hot users table.
SQL

CREATE TABLE user_profiles (

user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
avatar_url TEXT,
bio TEXT,
phone TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

NOTE 1-to-1 pattern: The FK to the parent table doubles as the PK of the child table. No
separate id column needed.

9.4 projects — One-to-Many + Referential Integrity

SQL

CREATE TABLE projects (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
name TEXT NOT NULL,
description TEXT,

Page 9 of 17
 Backend Databases · Notes

status project_status NOT NULL DEFAULT 'active',

owner_id UUID NOT NULL REFERENCES users(id) ON DELETE RESTRICT,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ON DELETE — Referential Integrity Actions

Action Behavior when parent row is deleted

RESTRICT / NO ACTION Block the delete. Parent cannot be deleted while children exist.

CASCADE Delete all child rows automatically (chain reaction).

SET NULL Set the FK to NULL. Fails if column is NOT NULL.

SET DEFAULT Set the FK to its DEFAULT value.

STORY Cascading logic in our schema: projects.owner_id → ON DELETE RESTRICT

(can’t delete a user who owns projects). tasks.project_id → ON DELETE
CASCADE (deleting a project wipes its tasks). tasks.assigned_to → ON DELETE
SET NULL (task survives, just gets unassigned).

9.5 tasks — One-to-Many with CHECK constraint

SQL

CREATE TABLE tasks (

id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
title TEXT NOT NULL,
description TEXT,
priority INTEGER NOT NULL DEFAULT 1 CHECK (priority BETWEEN 1 AND 5),
status task_status NOT NULL DEFAULT 'pending',
due_date DATE,
assigned_to UUID REFERENCES users(id) ON DELETE SET NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

EXAM CHECK constraint: A custom boolean rule. Example above ensures priority is 1–5.
Any INSERT/UPDATE that violates the rule is rejected at DB level.

9.6 project_members — Many-to-Many via Linking Table

Many-to-many means: a user can be in many projects AND a project can have many users. You cannot model
this with a single FK — you need a third table.
SQL

Page 10 of 17
 Backend Databases · Notes

CREATE TABLE project_members (

project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
role member_role NOT NULL DEFAULT 'member',
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
PRIMARY KEY (project_id, user_id)
);

NOTE Composite primary key: (project_id, user_id) together form the PK. A user can
only appear in a project once. Implies UNIQUE + NOT NULL on the pair, and gets
indexed.

Three-relationship summary
Relationship Implementation pattern

One-to-One Child table’s PK is also an FK back to the parent (single column does both
jobs).

One-to-Many Many side has a foreign-key column referencing the one side.

Many-to-Many Linking table with two FKs as a composite PK. Relationship-specific fields (role,
joined_at) live here.

10. Seeding Test Data

Seeding = pushing predictable fake data into the DB so dev/test environments are usable. Convention: a
separate migration file.
SQL

-- Use CTEs so we can pipe inserted IDs into the next insert
WITH inserted_users AS (
INSERT INTO users (email, full_name, password_hash)
VALUES
('alice@[Link]', 'Alice Brown', 'hash1'),
('john@[Link]', 'John Doe', 'hash2'),
('jane@[Link]', 'Jane Smith', 'hash3')
RETURNING id, email
)
INSERT INTO user_profiles (user_id, avatar_url, bio, phone)
SELECT id,
'[Link]
CASE
WHEN email = 'alice@[Link]' THEN 'Designer & coffee fanatic'
WHEN email = 'john@[Link]' THEN 'Backend engineer'
ELSE 'Full-stack developer'

Page 11 of 17
 Backend Databases · Notes

END,
'+91-9999999999'
FROM inserted_users;

TIP Why CTEs (WITH …): RETURNING gives you newly-generated UUIDs in the same
statement, so dependent inserts (profiles, members) can use them without
separate round-trips.

11. Writing Queries for Real APIs

11.1 GET /v1/users — Embed profile via JSONB

We want each user row to come back with an embedded "profile" object → fewer round-trips for the frontend.
SQL

SELECT
u.*,
to_jsonb(up.*) AS profile
FROM users u
LEFT JOIN user_profiles up
ON [Link] = up.user_id
ORDER BY u.created_at DESC;

LEFT JOIN vs INNER JOIN

Join Type Behavior

INNER JOIN Returns rows only where both sides match. Users with no profile would be
missing → bad here.

LEFT JOIN Returns ALL rows from the left table, with NULL on the right if no match. Correct
choice when profile is optional.

RIGHT JOIN Mirror of LEFT JOIN. Rarely used in practice.

FULL JOIN All rows from both sides, NULLs where no match. Niche.

NOTE to_jsonb(up.*) turns the joined right-side row into a single JSONB object,
exposed under the alias profile. The frontend gets one clean nested structure.

11.2 GET /v1/users/:user_id — Parameterized Queries & SQL Injection

SQL

SELECT
u.*,
to_jsonb(up.*) AS profile

Page 12 of 17
 Backend Databases · Notes

FROM users u
LEFT JOIN user_profiles up ON [Link] = up.user_id
WHERE [Link] = $1;

The "$1" is a placeholder filled in at execution time by the driver. The driver sends the value separately, so it is
treated strictly as data, never as SQL.

TRAP SQL Injection (DO NOT DO THIS): Concatenating user input directly into SQL is
the classic vulnerability.
Example: If your code does "SELECT * FROM users WHERE id = '" +
userInput + "'", a malicious user sends ' OR '1'='1 — and now your
query returns every user. Worse: '; DROP TABLE users; -- can wipe the
table.
Fix: Always use parameterized queries ($1, $2, …) through your driver / ORM.
The value is escaped automatically.

11.3 Dynamic Filter / Sort / Pagination

Most list APIs need: filter conditions + sort column + sort order + page + limit. Query is built dynamically in the
backend code, not in raw SQL by hand.

Typical query string

HTTP

GET /v1/users?letter=j&sort_by=full_name&sort_order=desc&page=1&limit=10

Resulting SQL
SQL

SELECT u.*, to_jsonb(up.*) AS profile

FROM users u
LEFT JOIN user_profiles up ON [Link] = up.user_id
WHERE u.full_name ILIKE $1 || '%'
ORDER BY u.full_name DESC
LIMIT $2 OFFSET $3;

Para Used for Note

m

$1 ILIKE pattern ILIKE = case-insensitive LIKE. "j%" matches names starting with j.

$2 LIMIT Page size — e.g. 10.

$3 OFFSET (page - 1) × limit. Page 1 → offset 0. Page 2 → offset 10.

WARNING Whitelist sort columns. Never trust sort_by directly from the user — that
string ends up in the ORDER BY clause and CANNOT be parameterized (it’s an
identifier, not a value). Maintain an allow-list like ['email', 'full_name',

Page 13 of 17
 Backend Databases · Notes

'created_at'] in your code and reject anything else.

11.4 POST /v1/users — Create + RETURNING

SQL

INSERT INTO users (email, full_name, password_hash)

VALUES ($1, $2, $3)
RETURNING *;

NOTE RETURNING gives back the inserted row(s) — including DB-generated values like
UUIDs and timestamps — without a second SELECT. Postgres-specific (not
standard SQL).

11.5 PATCH /v1/users/:user_id — Partial Update

Frontend may send any subset of {bio, phone, avatar_url}. Backend should only update the fields actually sent —
and construct the SQL dynamically.
SQL

-- If user sent only bio and phone, the query you build is:
UPDATE user_profiles
SET bio = $1,
phone = $2
WHERE user_id = $3
RETURNING *;

EXAM Backend pattern: Iterate over the request body. For each allowed field present,
append "col = $N" to a SET fragment and push the value into the params
array. Then join and execute.

12. Indexes — The Performance Lever

Mental model
An index is a separate lookup structure (commonly a B-tree). Think of the index at the back of a textbook — for
each topic, it tells you the page number, so you don’t flip every page.
Without index With index

Full table scan — DB checks every row, comparing Lookup structure points directly to row locations.
field by field. O(n). O(log n).

Fine on 100 rows. Catastrophic on 10 million. Searches stay fast even as the table grows.

Page 14 of 17
 Backend Databases · Notes

When to create an index

Three triggers — if a column appears in:
• A JOIN condition (and it’s not already a PK)
• A WHERE clause filter
• An ORDER BY sort
…AND the query is called frequently → consider an index.

Cost side — indexes are not free

• Take extra disk space.
• Every INSERT / UPDATE / DELETE has to update the index too → small write overhead.
• More indexes = slower writes. Read vs write trade-off.

Indexes the video creates

SQL

-- Lookup users by email (login, joins)

CREATE INDEX idx_users_email ON users(email);

-- Default sort: newest first

CREATE INDEX idx_users_created_at ON users(created_at DESC);

-- FK joins on tasks
CREATE INDEX idx_tasks_project_id ON tasks(project_id);
CREATE INDEX idx_tasks_assigned_to ON tasks(assigned_to);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_created_at ON tasks(created_at DESC);

-- M:M lookups
CREATE INDEX idx_project_members_project ON project_members(project_id);
CREATE INDEX idx_project_members_user ON project_members(user_id);

NOTE Auto-indexed by Postgres: every PRIMARY KEY and every UNIQUE constraint.
You don’t need to create those manually.

WARNING Don’t index everything. Start without extra indexes. Measure (EXPLAIN
ANALYZE, slow-query log). Add indexes only where queries are slow AND
frequent. Drop indexes that aren’t used.

13. Triggers — Auto-Update updated_at

A trigger is DB-level logic that fires automatically on INSERT / UPDATE / DELETE. We use one to keep updated_at
honest without remembering to set it from app code.

Page 15 of 17
 Backend Databases · Notes

The function
SQL

CREATE OR REPLACE FUNCTION set_updated_at()

RETURNS TRIGGER AS $$
BEGIN
NEW.updated_at = NOW();
RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Attaching it to each table

SQL

CREATE TRIGGER trg_users_updated_at

BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

CREATE TRIGGER trg_user_profiles_updated_at

BEFORE UPDATE ON user_profiles
FOR EACH ROW
EXECUTE FUNCTION set_updated_at();

-- repeat for projects, tasks, project_members

EXAM Effect: Any UPDATE on any of these tables — from app, admin tool, migration,
anywhere — gets the right updated_at automatically. The app code no longer
has to remember.

14. Backend-Engineer Cheat-Sheet

Topic Default choice / rule

DB engine PostgreSQL

Primary key type UUID with DEFAULT gen_random_uuid()

Strings TEXT (not VARCHAR(255))

Money / prices NUMERIC(p, s) — never FLOAT

Timestamps TIMESTAMPTZ

JSON JSONB

Page 16 of 17
 Backend Databases · Notes

Topic Default choice / rule

Enums CREATE TYPE … AS ENUM — for fixed sets

Nullability NOT NULL by default; allow NULL only when actually optional

Schema changes Always via migration files; never ad-hoc in GUI

User input → SQL Parameterized queries always — never string concat

List APIs Always paginate (LIMIT + OFFSET) + sort + filter

JOINs LEFT JOIN when the right side is optional; INNER when both sides are
required

Sort columns Whitelist them server-side; identifiers can’t be parameterized

ON DELETE Pick deliberately: RESTRICT / CASCADE / SET NULL based on business rule

Indexes On FKs used in joins, columns used in WHERE / ORDER BY, when query is
hot

updated_at Maintain via trigger, not app code

Naming snake_case, plural table names

End of notes — happy building.

Page 17 of 17