Why Must a Pump Move Be Edited on Every Job?

The maintenance spreadsheet repeats each pump’s model and site on every job. When P7 moves, one row is updated and another still says the old site. Which row should Botie trust?

We can separate facts about an asset from facts about an inspection. The useful question is what determines each value, not how many tables we can create.

Group site values by asset identity, check whether each group has one value, and retain the conflicting IDs.

An asset’s current site belongs to its current register entry. Its site at the time of an inspection is a historical fact about that event. Moving an asset should not rewrite history.

We assume the repeated site column in this lesson means current site. That makes its disagreement a problem. If the intended meaning were site at inspection, the design would need an event-time snapshot or a versioned history instead. Normalisation cannot resolve an ambiguous business definition for us.

Name the temporal meaning before deciding whether values are redundant.

Within our stated rules, asset_id determines model and current site. We write that dependency as asset_id → model, site. Job_id determines asset_id and minutes. These are rules about all permitted records, not claims established by a lucky small sample.

The workshop groups site values by asset ID. Several distinct values refute the current-site dependency in that sample. Finding only one value does not prove the dependency for future data; the domain contract and constraints still matter.

A dependency explains why changing one asset fact should not require editing several unrelated job rows.

Create assets with one row per asset and jobs with one row per job. Store asset_id in jobs as a foreign key. A join reconstructs the current display when it is needed.

The exercise copies distinct consistent asset facts from flat_jobs, then copies the job-specific facts. If source rows disagree about an asset, blindly choosing MIN(site) would conceal the disagreement. Repair or quarantine the conflict before migration. A primary-key violation during the copy is a useful signal that the assumption needs investigation.

The decomposition must reconstruct every original job without inventing combinations or losing rows.

CREATE TABLE flat_jobs(job_id TEXT,asset_id TEXT,model TEXT,site TEXT,minutes INTEGER);
INSERT INTO flat_jobs VALUES('J1','P7','M2','north',20),('J2','P7','M2','north',30),('J3','P8','M3','south',15);

SELECT asset_id,COUNT(DISTINCT site) AS site_values
FROM flat_jobs GROUP BY asset_id ORDER BY asset_id;

An update anomaly occurs when repeated copies disagree. An insertion anomaly occurs when an asset cannot be recorded without inventing a job to hold it. A deletion anomaly occurs when removing its final job also removes the only record of the asset.

Separate asset and job tables address those problems in this example. The asset can exist before its first job and after its last one. Its current model and site have one maintained location.

The benefit is clearer ownership of facts, not a ritual of splitting every column into a table.

First normal form asks for a relation with values in the chosen domains rather than repeating groups of job columns. With a composite key, second normal form addresses non-key facts depending on only part of that key. Third normal form also addresses inappropriate transitive dependencies; its formal condition allows a dependency when its determinant is a superkey or its dependent attribute is prime. Boyce-Codd normal form requires every nontrivial determinant to be a superkey.

For our job table, asset_id is not the job key, yet it determines current site. Moving site into assets removes that dependency from jobs. Complex schemas need candidate keys and all relevant dependencies before a normal-form claim is justified.

Do not infer a normal form merely by counting tables or noticing an integer primary key.

Imagine splitting a job into one table containing job_id and asset_id, and another containing job_id and minutes. If job_id is unique in both, joining on it can reconstruct the original job. If we instead join tables using a repeated status such as open, unrelated jobs can combine and invent records. This is why a decomposition needs a lossless join, not merely fewer repeated words.

For our assets/jobs split, the shared asset_id identifies one asset row. Each job therefore finds exactly its own asset facts, provided the foreign key is enforced. Also ask whether the original dependencies can still be checked in the new tables. A decomposition that requires a large join to enforce a common write rule may introduce a practical cost even if it removes redundancy.

Reconstructing the current sample is a useful test. Keys and dependencies explain why reconstruction remains correct for future permitted records.

An analytical feature table can deliberately repeat asset information to avoid repeated joins or to freeze a training snapshot. Document who builds that table, when it refreshes, and which source/version it represents.

A derived snapshot should not become another independently edited source of truth. Materialised views, caches and warehouse tables need refresh and reconciliation rules. The cost of repetition is acceptable when its maintenance and temporal meaning are explicit.

The later feature and incremental lessons connect this design decision to reproducible model data.