Keys & relations

Nested JSON is a lie we tell the browser. Underneath it's ids pointing at other ids.

Primary keys

Every table needs a stable identity:

id INTEGER PRIMARY KEY

Sometimes natural keys exist (email UNIQUE) - still keep a surrogate id. URLs, FKs, and merges get easier.

Foreign keys

A foreign key says "this column must point at a real row over there" (or be NULL).

PRAGMA foreign_keys = ON;  -- SQLite: turn enforcement on per connection

CREATE TABLE companies (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);

CREATE TABLE contacts (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  email TEXT NOT NULL UNIQUE,
  company_id INTEGER,
  FOREIGN KEY (company_id) REFERENCES companies(id)
);

Comments for the code above:

• company_id references companies(id)
• Inserting company_id = 999 when no company 999 exists → error (if FK pragma is on)
• SQLite defaults foreign keys OFF - always PRAGMA foreign_keys = ON in app connections

Relationship shapes

One-to-many (1:N)

One company, many contacts. FK lives on the "many" side.

companies 1 ──< contacts

{
  "company": { "id": 10, "name": "Acme" },
  "contacts": [ { "id": 1 }, { "id": 2 } ]
}

Many-to-many (N:N)

Contacts can have many tags; tags apply to many contacts. You need a junction table:

CREATE TABLE tags (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL UNIQUE
);

CREATE TABLE contact_tags (
  contact_id INTEGER NOT NULL,
  tag_id INTEGER NOT NULL,
  PRIMARY KEY (contact_id, tag_id),
  FOREIGN KEY (contact_id) REFERENCES contacts(id),
  FOREIGN KEY (tag_id) REFERENCES tags(id)
);

One-to-one (1:1)

Rare-ish. Same as 1:N but with a UNIQUE on the FK so only one child row points at the parent. Or split columns into a profile table for optional fat data.

ON DELETE behavior

FOREIGN KEY (company_id) REFERENCES companies(id)
  ON DELETE SET NULL
-- or ON DELETE CASCADE
-- or ON DELETE RESTRICT (default-ish)

Action	Vibe
RESTRICT	can't delete parent while children exist
CASCADE	delete parent → delete children
SET NULL	delete parent → child FK becomes NULL

Product decision. Deleting a company that still has deals? Probably restrict. Deleting a contact's notes when the contact goes? Maybe cascade.

FE takeaway

When the API returns nested data, ask: "which table owns the FK?" That question alone demystifies half of backend PRs.

Next: stitching rows together at read time - JOINs.