Which Records Does the Technician Need Together?

Botie already has an asset register and a table of inspections. A technician opens P1 and expects its recent visits. Another screen needs the latest temperature for P1. The weekly report needs totals across every pump at a site. Those requests use the same information in different ways.

Before choosing another database, write down exactly what each request knows and what it must return. That gives us something to judge a design against. A convenient record layout for one screen can make another question harder.

Build a keyed collection, look up the requested asset, and supply an empty list when it has no visits.

[
  {
    "asset_id": "P1",
    "visit": "V1"
  }
]

An access pattern is a particular way the application reads or changes data. “Show recent visits for this pump” tells us the lookup identity and the records we need together. Add the required ordering, the largest plausible result and how fresh it must be.

Ask about writes too. A new inspection arrives while somebody is viewing the history. Does the next refresh need to show it immediately? Can the screen tolerate a short delay? Those are application requirements, and a data model cannot choose them for us.

A useful design note names an operation and its constraints. “We need something scalable” leaves the important questions unanswered.

The asset table has one row per asset. The inspection table has one row per visit. If we create a dictionary keyed by asset ID and assign a single visit to each entry, a later visit replaces the earlier one. The code has changed the meaning of the data.

A bucket of visits is different: the asset ID selects a list, and that list retains separate visit identities. This gives the pump screen what it asked for without pretending each pump has only one inspection.

Row grain from SQL still matters when the container is a dictionary or a document.

A Python dictionary maps a key to a value. Here each value is a list. Read a visit, find or create its asset’s list, and append the visit. We perform the grouping once in this demonstration, then retrieve a selected list.

Constructing the grouping scans the input. Keeping an equivalent structure in a real service also costs storage and update work. We have made the read simple by doing work elsewhere.

This dictionary is an inspectable model of grouping, not an implementation of distributed storage.

visits = [("P1", "V1"), ("P2", "V2"), ("P1", "V3")]
by_asset = {}
for asset, visit in visits:
    by_asset.setdefault(asset, []).append(visit)
print(by_asset["P1"])
print(by_asset.get("P9", []))

['V1', 'V3']
[]

A key-value store starts with a key and returns its associated value. A document database can inspect fields inside a stored record. Wide-column systems organise records around keys and related columns, often with query patterns tied closely to their partitioning. Graph databases make relationships and traversals central to their interfaces. Products vary within each family.

Relational databases can also store JSON and maintain indexes for targeted lookups. A JSON-shaped request alone does not establish that we need a document database. Compare the integrity rules and operational burden of the actual candidates.

NoSQL names a broad collection of approaches. It does not promise a particular consistency model, transaction boundary or performance result.

Suppose visits remain in the SQL system of record while a service keeps a pump-history document. Now an inspection write may need to reach both representations. A failed second write can leave them disagreeing. We need a rebuildable projection, a reliable change feed, or another explicit update process.

A screen-specific copy is useful when its freshness contract permits that process. The same copy is a poor authority for a decision that requires the newest committed safety status. Say which store owns each fact and how other representations catch up.

Before adding a second representation, write down what happens when only one copy changes.

The workshop first contains one visit per pump, so assigning a single record might appear sufficient. Then a second visit for the requested pump arrives. Your grouping must retain both. A request for an unseen asset needs an explicit empty response.

Use this failure to challenge a proposed model before measuring speed. Once the answers are correct, investigate the workload with a real database and realistic record sizes. Our in-memory counts cannot predict network latency or server capacity.

The next lesson follows the lookup key itself. Two sites can both call their pump P1.