Schema design checklist

You have covered a lot of ground in this course. Tables, relationships, normalization, indexes, pragmas, full-text search, migrations, node:sqlite, Litestream backups, and a handful of SQLite quirks along the way. It is a lot to keep in your head when you sit down to design a new schema. This lesson condenses everything into a checklist you can run through before shipping a schema. If you can check every box, your schema is solid. If you cannot, you know exactly what to fix. Then in the next lesson, we will put it all together in a real design exercise.

The checklist

Tables

• Each table represents one entity (noun)
• Table names are plural, snake_case (users, book_tags)
• Every table has a primary key
• No comma-separated values in any column (1NF)

Columns

• Column names are singular, snake_case (name, created_at)
• Most columns are NOT NULL (NULLs are intentional, not default)
• Booleans use is_ or has_ prefix (is_active, has_verified)
• Dates stored as TEXT in ISO 8601 format
• Defaults provided for columns that need them (DEFAULT 0, DEFAULT (datetime('now')))

Primary keys

• UUIDs (TEXT) for API-exposed resources (prevents IDOR)
• INTEGER AUTOINCREMENT for internal tables (sessions, logs)
• Composite keys for junction tables (PRIMARY KEY (book_id, tag_id))

Relationships

• Foreign keys on the “many” side of one-to-many relationships
• Junction tables for many-to-many relationships
• ON DELETE behavior specified (CASCADE, RESTRICT, SET NULL)
• Foreign key columns indexed (not automatic in SQLite)

Normalization

• No repeated data across rows (3NF)
• No columns that depend on non-key columns (3NF)
• Denormalization is intentional and documented (with triggers or periodic recomputation)

Performance

• Indexes on columns used in WHERE, JOIN ON, ORDER BY
• Foreign key columns indexed
• EXPLAIN QUERY PLAN shows SEARCH, not SCAN, for important queries
• No N+1 queries (use JOINs instead of loops)
• Bulk operations wrapped in transactions

SQLite

• journal_mode = WAL enabled
• foreign_keys = ON on every connection
• busy_timeout set for concurrent access
• FTS5 used for text search (not LIKE with leading wildcard)

Schema evolution

By this point in the course, this section should feel familiar. You have been writing numbered migration files since Section 3, and every change to your schema has gone through the same discipline. This is the review, not the introduction:

• Migrations tracked in a schema_migrations table
• Migrations are atomic (wrapped in transactions)
• New NOT NULL columns have defaults
• Backwards-incompatible changes are sequenced (deploy code first, then migrate)

Production

• App opens the database through node:sqlite (or an equivalent built-in client) with the three essential pragmas on every connection
• Prepared statements cached at module scope, not recompiled in hot loops
• Litestream (or equivalent) streams the WAL to object storage continuously
• Container entrypoint restores from backup on boot if the database file is missing, then runs migrations, then starts replication, then starts the app
• A scheduled restore-test pulls the replica, runs PRAGMA integrity_check, and alerts on failure

Common mistakes

Comma-separated values. Storing tags as "fiction,classic" instead of using a junction table. Cannot be queried, indexed, or constrained. Violates 1NF.

Missing NOT NULL. Every column allows NULL by default. A user without a name? A book without a title? Add NOT NULL unless the value is genuinely optional.

No index on foreign keys. Primary keys are indexed automatically. Foreign keys are not. Every JOIN on an unindexed foreign key results in a full table scan.

Using LIKE for search. LIKE '%query%' scans every row and cannot use an index. Use FTS5 for text search.

N+1 queries. Fetching a list and then querying related data in a loop. Use JOINs.

No WAL mode. Without WAL, every write locks the entire database. Concurrent web requests queue behind each other.

No migration tracking. You know this one by now. Running SQL scripts by hand against the database is how schemas drift between environments. Every change goes through a numbered migration file, tracked in schema_migrations, wrapped in a transaction. If you ever feel tempted to just open a prod shell and fix something, write a migration instead.

Premature denormalization. Adding review_count before measuring a performance problem. Normalize first. Denormalize only when you have evidence that the joins are too slow.

Exercises

Exercise 1: Take the schema from a project you have worked on (or a previous course). Run it through the checklist. How many items does it satisfy?

Exercise 2: Find the items it does not satisfy. What would you change?

Exercise 3: Open a SQLite database and run EXPLAIN QUERY PLAN on the most common queries. Are they using indexes?

You have the checklist. Now it is time to put everything together. In the capstone, we will design a full database from scratch and encode every decision in the same migration workflow you have been using all course.