Design a SQLite Schema from Existing Requirements

Learning outcomes

The FieldNotes requirements now have enough history to become a real SQLite schema. The goal is not to use every feature available. The goal is to make each choice explainable: which facts need durable identity, which rules belong inside the database, which table form fits the key, and how you will inspect the result.

Requirements first: what must FieldNotes represent?

Return to the domain used since Chapter 1. A site is a physical location. A device belongs to one site and has a stable device code. A maintenance note records an event for one device. A device can have zero or more reusable text tags, and the same tag text can appear on many devices.

Choose rowid tables where integer surrogate identity fits naturally

site, device, and maintenance_note all need stable application identity that is naturally represented by a named integer surrogate. In SQLite, INTEGER PRIMARY KEY is an efficient, simple fit because it aliases the rowid.

PRAGMA foreign_keys = ON;CREATE TABLE site (    site_id    INTEGER PRIMARY KEY,    site_code  TEXT NOT NULL UNIQUE,    site_name  TEXT NOT NULL,    active     INTEGER NOT NULL DEFAULT 1               CHECK (active IN (0,1)));CREATE TABLE device (    device_id    INTEGER PRIMARY KEY,    site_id      INTEGER NOT NULL,    device_code  TEXT NOT NULL UNIQUE,    device_name  TEXT NOT NULL,    status       TEXT NOT NULL DEFAULT 'active'                 CHECK (status IN ('active','inspection_due','retired')),    FOREIGN KEY (site_id) REFERENCES site(site_id));CREATE TABLE maintenance_note (    note_id      INTEGER PRIMARY KEY,    device_id    INTEGER NOT NULL,    occurred_at  TEXT NOT NULL,    severity     TEXT NOT NULL DEFAULT 'info'                 CHECK (severity IN ('info','warning','critical')),    note_text    TEXT NOT NULL,    FOREIGN KEY (device_id) REFERENCES device(device_id));

Foreign-key enforcement is enabled per connection with PRAGMA foreign_keys=ON in this lab. Chapter 5 explains enforcement timing, actions, indexing, and deployment habits. Chapter 4 will revisit the declared types and whether STRICT is appropriate.

Choose WITHOUT ROWID where the composite key is the identity

A device tag assignment has no requirement for an additional numeric ID. The pair (device_id, tag) is compact, stable, and is how the row is logically identified. That is a reasonable candidate for a WITHOUT ROWID table.

CREATE TABLE device_tag (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag),    FOREIGN KEY (device_id) REFERENCES device(device_id)) WITHOUT ROWID;

This is an intentional choice, not a universal rule. If later profiling shows a different table form is better for the real workload, the design can change through a migration. The important point is that there is no redundant “assignment_id” merely because every other table has an integer key.

Database constraints protect invariants; applications improve interaction

An application can validate before sending SQL, but another script, migration, import tool, or future application can bypass that code. Rules that define valid database state belong in the database when SQLite can express them reliably. Application validation still matters for friendly error messages and rules that need external context.

Create the complete schema reproducibly

Use a new file named chapter03_fieldnotes.db. Put the DDL in sql/chapter03_schema.sql if you are following the project layout from Chapter 1, then run it through the repeatable scripting workflow learned in Chapter 2.

-- sql/chapter03_schema.sqlPRAGMA foreign_keys = ON;DROP TABLE IF EXISTS device_tag;DROP TABLE IF EXISTS maintenance_note;DROP TABLE IF EXISTS device;DROP TABLE IF EXISTS site;CREATE TABLE site (    site_id    INTEGER PRIMARY KEY,    site_code  TEXT NOT NULL UNIQUE,    site_name  TEXT NOT NULL,    active     INTEGER NOT NULL DEFAULT 1               CHECK (active IN (0,1)));CREATE TABLE device (    device_id    INTEGER PRIMARY KEY,    site_id      INTEGER NOT NULL,    device_code  TEXT NOT NULL UNIQUE,    device_name  TEXT NOT NULL,    status       TEXT NOT NULL DEFAULT 'active'                 CHECK (status IN ('active','inspection_due','retired')),    FOREIGN KEY (site_id) REFERENCES site(site_id));CREATE TABLE maintenance_note (    note_id      INTEGER PRIMARY KEY,    device_id    INTEGER NOT NULL,    occurred_at  TEXT NOT NULL,    severity     TEXT NOT NULL DEFAULT 'info'                 CHECK (severity IN ('info','warning','critical')),    note_text    TEXT NOT NULL,    FOREIGN KEY (device_id) REFERENCES device(device_id));CREATE TABLE device_tag (    device_id INTEGER NOT NULL,    tag       TEXT NOT NULL,    PRIMARY KEY (device_id, tag),    FOREIGN KEY (device_id) REFERENCES device(device_id)) WITHOUT ROWID;

The DROP order follows dependencies from child to parent. In a production migration you would not casually drop durable tables; this is a disposable Chapter 3 lab. Chapter 17 replaces destructive reset scripts with migrations and compatibility tests.

Seed just enough data to exercise the design

Use recognizable rows so failed constraints are easy to diagnose.

INSERT INTO site(site_code, site_name) VALUES  ('NORTH', 'North Plant'),  ('HARBOR', 'Harbor Lab');INSERT INTO device(site_id, device_code, device_name, status) VALUES  ((SELECT site_id FROM site WHERE site_code='NORTH'), 'PUMP-007', 'Cooling Pump 7', 'active'),  ((SELECT site_id FROM site WHERE site_code='NORTH'), 'FAN-014',  'Ventilation Fan 14', 'inspection_due'),  ((SELECT site_id FROM site WHERE site_code='HARBOR'), 'SENS-003', 'Pressure Sensor 3', 'active');INSERT INTO maintenance_note(device_id, occurred_at, severity, note_text) VALUES  ((SELECT device_id FROM device WHERE device_code='PUMP-007'), '2026-08-12T08:30:00Z', 'info', 'Seal inspected.'),  ((SELECT device_id FROM device WHERE device_code='FAN-014'),  '2026-08-12T09:15:00Z', 'warning', 'Bearing noise noted.');INSERT INTO device_tag(device_id, tag) VALUES  ((SELECT device_id FROM device WHERE device_code='PUMP-007'), 'critical'),  ((SELECT device_id FROM device WHERE device_code='PUMP-007'), 'pump'),  ((SELECT device_id FROM device WHERE device_code='FAN-014'),  'fan'),  ((SELECT device_id FROM device WHERE device_code='SENS-003'), 'sensor');

Expected counts are 2 sites, 3 devices, 2 maintenance notes, and 4 device-tag assignments. Those counts become verification checkpoints rather than “it seemed to run.”

Inspect the schema from multiple angles

Now validate the concrete result. The DDL file expresses intent; metadata expresses the engine's current state.

.schemaSELECT type, name, tbl_name, sqlFROM sqlite_schemaWHERE name NOT LIKE 'sqlite_%'ORDER BY type, name;PRAGMA table_list;PRAGMA table_info('site');PRAGMA table_info('device');PRAGMA table_xinfo('maintenance_note');PRAGMA index_list('site');PRAGMA index_list('device');PRAGMA index_list('device_tag');SELECT COUNT(*) AS sites FROM site;SELECT COUNT(*) AS devices FROM device;SELECT COUNT(*) AS notes FROM maintenance_note;SELECT COUNT(*) AS tags FROM device_tag;

Look for wr=1 on device_tag and wr=0 on the three core rowid tables in modern table_list output. UNIQUE constraints on site_code and device_code normally produce automatic indexes, while the INTEGER PRIMARY KEY columns do not need separate primary-key indexes.

Make invalid states fail on purpose

A schema review is stronger when you test negative cases. Run these one at a time on the disposable lab, read the error, then continue.

-- Duplicate site code: should fail UNIQUE.INSERT INTO site(site_code, site_name)VALUES ('NORTH', 'Duplicate North');-- Invalid device status: should fail CHECK.INSERT INTO device(site_id, device_code, device_name, status)VALUES (1, 'TEST-999', 'Invalid status demo', 'broken-ish');-- Unknown site: should fail FOREIGN KEY when foreign_keys is ON.INSERT INTO device(site_id, device_code, device_name, status)VALUES (9999, 'TEST-998', 'Unknown site demo', 'active');-- Duplicate composite tag: should fail PRIMARY KEY.INSERT INTO device_tag(device_id, tag)SELECT device_id, 'pump'FROM device WHERE device_code='PUMP-007';

If the foreign-key test unexpectedly succeeds, diagnose connection state first with PRAGMA foreign_keys;. Do not conclude that the schema declaration was ignored. SQLite foreign-key enforcement is a per-connection runtime setting unless your application turns it on consistently; Chapter 5 handles this production requirement in depth.

Reusable SQLite schema design review checklist

Use this checklist at the end of every later schema change. It deliberately mixes modeling, SQLite implementation, and verification.

1. Identity: What makes each row the same real-world thing over time? Is the primary key natural, surrogate, or composite?
2. Table form: Is an ordinary rowid table the simplest fit? If WITHOUT ROWID is proposed, what specific key/storage/access pattern motivates it?
3. Nullability: Which attributes are genuinely optional? Are required key columns explicitly protected?
4. Uniqueness: Which business identifiers must never duplicate, regardless of which application writes?
5. References: Which relationships need FOREIGN KEY constraints, and will enforcement be enabled consistently?
6. Domains: Are CHECK/default/type choices expressing durable invariants or merely UI preferences?
7. Naming: Are table/column names clear, stable, and free of SQLite-reserved/internal-name collisions?
8. Introspection: Do sqlite_schema, table_list, table_info/xinfo, and index metadata match the declared intent?
9. Negative tests: Have you proved invalid duplicates, references, or domain values are rejected as intended?
10. Version assumptions: Have you recorded sqlite_version(), important compile options, and host-library differences before depending on newer behavior?

Chapter 3 completion checklist

• You can create tables in main or temp and inspect their catalog entries.
• You know that IF NOT EXISTS does not validate schema equivalence.
• You can explain why exact INTEGER PRIMARY KEY aliases rowid in ordinary tables.
• You can state what AUTOINCREMENT adds—and why it is usually unnecessary.
• You can compare rowid and WITHOUT ROWID organization without promising unmeasured speedups.
• You can distinguish a TEMP table from a :memory: main database.
• You can translate requirements into SQLite DDL and prove constraints with negative tests.
• You have a reusable design-review checklist for later chapters.

Summary and bridge to Chapter 4

Chapter 3 turned relational design into SQLite-specific structure. You inspected sqlite_schema, learned the hidden rowid model, saw the special meaning of INTEGER PRIMARY KEY, evaluated WITHOUT ROWID, separated TEMP from in-memory storage, and built a justified FieldNotes schema. Chapter 4 now tackles the part that most often surprises developers coming from stricter database servers: SQLite's storage classes, declared types, type affinity, conversions, and STRICT tables.

References

• SQLite — CREATE TABLE.
• SQLite — The Schema Table.
• SQLite — Rowid Tables.
• SQLite — WITHOUT ROWID Tables.
• SQLite — AUTOINCREMENT.
• SQLite — PRAGMA Statements.
• SQLite — Foreign Key Support.
• SQLite — In-Memory Databases.
• SQLite — Release History.