Relational Model & Schema Design

What is it: The relational model organizes data into tables (relations): rows (tuples) and columns (attributes). Every row has the same set of columns. A primary key uniquely identifies each row. Relationships between tables are expressed through foreign keys — a column that references the primary key of another table.

Why we need it: Without structure, you cannot enforce that every order has a valid customer, that an email appears only once, or that a balance is never negative. The relational model lets you declare these invariants as constraints — the database enforces them on every write, so your application cannot accidentally violate them through a bug.

Primary key: Uniquely identifies a row. Automatically creates a unique B+Tree index. In PostgreSQL, a SERIAL or BIGSERIAL column uses a sequence. For distributed systems, UUID or Snowflake IDs are preferred to avoid coordination between nodes. A composite primary key uses multiple columns together to form a unique identifier (e.g., (user_id, post_id) for a likes table).

erDiagram CUSTOMERS ||--o{ ORDERS : "places" ORDERS ||--|{ ORDER_ITEMS : "contains" CUSTOMERS { bigint id PK text email } ORDERS { bigint id PK bigint customer_id FK timestamp created_at } ORDER_ITEMS { bigint id PK bigint order_id FK int quantity }

Foreign key: A column whose value must exist as a primary key in another table. Enforces referential integrity: you cannot insert an order for a non-existent customer, and you cannot delete a customer who has orders (unless ON DELETE CASCADE or ON DELETE SET NULL is specified).

Without foreign keys, referential integrity is left to the application. A race condition (delete customer, insert order) or a bug (wrong customer_id) silently corrupts data. Foreign key checks add overhead on insert/update to the referencing table and on delete/update to the referenced table — this is why bulk imports sometimes temporarily disable foreign key checks.

Constraints:

  • NOT NULL: column must have a value. Without it, queries on that column must handle NULL, which has three-valued logic (NULL != NULL, NULL OR true = true, NULL AND true = NULL).
  • UNIQUE: no two rows can have the same value in this column. Automatically creates a unique index. A unique constraint on NULL values is lenient — PostgreSQL allows multiple NULLs (NULL is not equal to NULL).
  • CHECK: arbitrary boolean expression. CHECK (balance >= 0) prevents negative balances at the DB level. Applied on every INSERT and UPDATE. Cannot reference other tables (use triggers for that).
  • DEFAULT: specifies a value used when the column is omitted on insert. Evaluated at insert time, so DEFAULT now() correctly stamps each row.

What happens without constraints: The application becomes the only enforcement layer. One missing validation in one code path corrupts data permanently. Constraints are cheaper to enforce at the DB level once than to validate in every application code path.

Real-world usage: In Proxel: jobs table with user_id BIGINT REFERENCES users(id) — no orphaned jobs. In ValkeyDB: no relational schema (in-memory key-value), but the same constraint philosophy applies to Redis data structure design.

Common pitfall

Skipping foreign keys "for performance" (a common instinct — they do add overhead on write, as noted above) without replacing them with anything is a far more expensive trade in practice: the overhead saved is a small, predictable, per-write cost; the cost of the referential-integrity bugs that follow (orphaned rows, dangling references after a bad delete) is unpredictable and often discovered much later, as corrupted data that's already been read, cached, or billed against. If foreign-key overhead genuinely matters at a specific hot path's scale, that's a decision to make deliberately, row by row, not a repo-wide default.