What Does One Row Actually Describe?

The maintenance team gives Botie a spreadsheet. P1 appears on several lines because it has been inspected several times. Does that mean the team owns several pumps called P1, or that one pump has a history?

We need an answer before counting anything. A table becomes useful when each row has a defined meaning and the fields describe that same kind of thing. We will separate the asset from the visits made to it, then give each its own identity.

Choose the asset ID and kind, then order the result by ID. Several pumps may share a kind without becoming the same asset.

[
  {
    "asset_id": "P1",
    "kind": "pump"
  },
  {
    "asset_id": "P2",
    "kind": "fan"
  },
  {
    "asset_id": "P3",
    "kind": "pump"
  },
  {
    "asset_id": "P4",
    "kind": "pump"
  }
]

Grain means what one row represents. Here an assets row describes one asset; an inspections row describes one visit. Write that sentence before choosing columns. A minutes field belongs to a visit, because a later visit to the same pump can take a different amount of time.

If a table mixes one row per asset with occasional rows per visit, counting its rows has no consistent meaning. The problem exists even if every cell is neatly formatted.

Ask what event or object caused this row to exist.

A primary key identifies one row and cannot duplicate another key in the same table. We explicitly declare text asset IDs NOT NULL as well, avoiding SQLite’s historical nullable-primary-key exceptions. The inspection key identifies the visit, while its asset_id refers to the asset table.

A foreign key records that relationship. With enforcement enabled, an inspection cannot name a missing asset. It does not prevent several inspections from naming the same asset; that repetition is the intended one-to-many relationship.

A key identifies a record. A descriptive label such as pump describes it.

SELECT names the fields we want back. FROM names their source table. The database returns a result table; reading it does not change the stored asset register. ORDER BY asset_id asks for a reproducible display order, which we will examine more closely later.

Use single quotes for text values such as north. Column names in these examples are simple identifiers. SQL keywords are shown in capitals for readability, not because they must be capitalised.

Follow the source name to the visible initial table before running the query.

CREATE TABLE assets (
  asset_id TEXT PRIMARY KEY NOT NULL,
  site TEXT NOT NULL,
  kind TEXT NOT NULL,
  active INTEGER NOT NULL CHECK (active IN (0, 1))
);
CREATE TABLE inspections (
  inspection_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  minutes INTEGER CHECK (minutes >= 0),
  finding TEXT
);
CREATE TABLE readings (
  reading_id INTEGER PRIMARY KEY,
  asset_id TEXT NOT NULL REFERENCES assets(asset_id),
  temperature REAL
);
INSERT INTO assets VALUES
 ('P1','north','pump',1), ('P2','north','fan',1),
 ('P3','south','pump',0), ('P4','south','pump',1);
INSERT INTO inspections VALUES
 (101,'P1',30,'loose'), (102,'P1',NULL,NULL),
 (103,'P2',0,'clear'), (104,'P3',20,'clear'),
 (105,'P2',10,'loose'), (106,'P1',15,'loose');
INSERT INTO readings VALUES
 (1,'P1',70.0), (2,'P1',80.0), (3,'P2',NULL), (4,'P3',65.0);

SELECT asset_id, site
FROM assets
ORDER BY asset_id;

An identifier such as P1 is text. Inspection minutes are whole numbers. Temperature can have a fractional value. NULL is a marker for a missing value, which we will study separately; it is not a string spelling NULL.

SQLite usually associates a storage class with the value and gives columns an affinity that influences conversion. Other databases commonly enforce declared column types more strictly. SQLite also supports STRICT tables, but our ordinary tables make the distinction worth noticing. Constraints and validation remain necessary when an import can contain unexpected values.

A number stored as text can behave differently in sorting or arithmetic. Inspect actual values rather than inferring their type from how they look.

We can answer which kinds of assets exist from the register. To ask how much time each one consumed, we need visits from the inspection log. Keeping those facts separate avoids repeating and potentially disagreeing about the asset’s site on every visit.

Later joins will reconnect the records by their keys. For now, preserve the key when projecting the register. A report containing only pump, fan, pump, pump has lost the information needed to distinguish the three pumps.

The first workshop asks you to retain identity even when descriptions repeat.