What Does One Equipment Record Promise?

Botie receives a spreadsheet of equipment. One file uses asset labels, another uses row positions, and the cost column has no unit. We can write a SELECT over those values, but it cannot decide what the authors meant.

A schema records what a row represents and what its fields promise. We will design an equipment table, then use the database to reject records that violate that promise.

Connect the field contract before deciding which record IDs need repair.

Our equipment table has one row per asset. A visit table would instead have one row per visit. Putting several visits into extra columns on an equipment row would mix those grains and make another visit require a schema change.

Choose a key that identifies the asset across imports. A spreadsheet row number changes when rows are sorted. A serial number may be a useful natural key if the business guarantees its uniqueness and stability. A generated surrogate key can provide stable internal identity while a separate unique constraint preserves a business identifier.

A generated key does not remove the need to define which real-world duplicates are forbidden.

Cost in cents can be stored as an integer when the business works in one currency and fixed minor units. A currency field is needed if that assumption changes. A temperature needs a unit and a measurement time. Neither choice follows from calling a column REAL.

Decide how unknown values differ from zero, and which values may be absent. A retired asset is still an asset, so retaining its row preserves references from earlier inspections. Our retired flag records that state without reusing its identity for another pump.

The schema is an agreement about meaning as well as storage.

In an ordinary SQLite table, a declared type usually gives the column an affinity that guides conversion. It does not generally forbid another storage class. STRICT tables narrow the allowed declared types and require compatible values, although lossless conversions may still occur.

Use TEXT for our identifiers, INTEGER for the flag and cents, and explicit NOT NULL where absence is invalid. REAL is floating point, so it does not provide exact decimal-money arithmetic. PostgreSQL has different type rules and offers NUMERIC for exact decimal quantities. Porting a schema requires reviewing those differences.

We use STRICT here because the exercise asks the database to reject incompatible values. The workshop checks already-decoded values without simulating SQLite conversion.

CREATE TABLE asset_example(id TEXT NOT NULL, cost_cents INTEGER NOT NULL) STRICT;
INSERT INTO asset_example VALUES('P3',850);
SELECT id,typeof(id),cost_cents,typeof(cost_cents) FROM asset_example;

CREATE TABLE lists each column and its constraints. PRIMARY KEY identifies a row; NOT NULL forbids a missing required field. CHECK adds the domain rule that the retired flag is either zero or one and that cost is nonnegative.

SQLite has historical exceptions around nullable primary keys in ordinary rowid tables. Use explicit NOT NULL for portable intent rather than depending on that behaviour. INTEGER PRIMARY KEY has special rowid behaviour in SQLite and can generate an ID when NULL is inserted. Our text asset key is supplied by the application.

Use an explicit INSERT column list. It documents the mapping and makes later schema changes less likely to reorder values silently.

PRAGMA table_info(equipment) reports declared columns in SQLite, while sqlite_schema contains schema definitions. Other engines provide information_schema or their own catalogues. These are useful when diagnosing an import that worked against an older schema.

CREATE TABLE AS SELECT can be convenient for an analytical copy, but it does not preserve the original table’s keys and constraints in SQLite. A copied dataset and a durable application table can therefore have very different guarantees even when their visible rows match.

The challenge tests future inserts as well as the initial displayed rows.

Adding a required field to a populated table raises a question: what value belongs on existing rows? A default is only appropriate when it represents a defensible meaning. Inventing zero to satisfy NOT NULL can corrupt later analysis.

Plan a migration that adds or rebuilds the structure, converts and checks existing data, and updates readers and writers in a controlled order. Keep a recovery path and test with representative old records. A fresh empty exercise database avoids deployment concerns; it does not demonstrate a safe migration on a live service.

Botie can now reject an incompatible record, but we still need rules connecting one table to another.