Tables

A table is the relational unit for an entity or relationship: rows are instances, columns are attributes with a fixed type. Good schema design maps domain nouns to tables and enforces rules at the database layer.

| Element | Role | |---------|------| | Column type | `INTEGER`, `TEXT`, `TIMESTAMP`, `JSONB` — storage and comparison semantics | | `NOT NULL` | Rejects missing required facts | | `DEFAULT` | Safe insert-time value when app omits a column | | `CHECK` | Domain rule the DB rejects before commit | | `UNIQUE` | Alternate natural keys (email, slug) |

					CREATE TABLE orders (
  id          BIGINT PRIMARY KEY,
  customer_id BIGINT NOT NULL,
  status      TEXT NOT NULL DEFAULT 'pending'
              CHECK (status IN ('pending', 'paid', 'shipped')),
  total_cents INTEGER NOT NULL CHECK (total_cents >= 0),
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);
				

Nullable columns encode optional facts; overusing `NULL` makes predicates and joins harder. Pick types for range, precision, and timezone needs — do not store money as `FLOAT`.

On interviews: walk from a product concept (order, user, membership) to table boundaries, required columns, and which invariants belong in constraints versus application code.

Common pitfalls: one wide table for unrelated concepts; nullable columns that are logically required; wrong types (`FLOAT` for money, `TEXT` for enums without checks); no `created_at` / audit columns when the product needs history.

The trade-off is balancing normalization and constraint strictness against migration friction and application flexibility — push invariants into the schema when wrong data is expensive to fix later.

Checklist:

• Name the entity each table represents.
• Mark required facts with `NOT NULL` and sensible defaults.
• Choose types for precision, timezone, and range.
• Add `CHECK` or `UNIQUE` where the domain has hard rules.
• Explain why a column is nullable or not.