Chapter 4 — Data models
Lesson map
Lesson 04 published. Map, example, EX010.
flowchart TD
N0["Learning outcomes"]
N1["4.1 Tables, keys and attributes"]
N2["4.2 Relationships"]
N3["4.3 What not to store"]
N4["4.4 Data quality fields"]
N5["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5
N5 --> EX["Worked example"]
EX --> Q["Exercise EX010"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["4.1 Tables, keys and attributes"]
N2["4.2 Relationships"]
N3["4.3 What not to store"]
N4["4.4 Data quality fields"]
N5["Synthesis"]
N0 --> N1 --> N2 --> N3 --> N4 --> N5 --> Q["EX010"]
[Learning outcomes]
|
v
[4.1 Tables, keys and attributes]
|
v
[4.2 Relationships]
|
v
[4.3 What not to store]
|
v
[4.4 Data quality fields]
|
v
[Synthesis]
|
v
[Worked example] --> [Exercise EX010]
Learning outcomes
You will (1) design a simple asset table with keys and attributes; (2) choose identifiers that survive device replacement; (3) represent relationships such as "gateway serves loggers"; and (4) keep sensitive attributes out of general tables.
4.1 Tables, keys and attributes
An asset table holds one row per asset. The key must be unique and stable. IP addresses are poor keys because they change when a device is re-addressed; serial numbers change when a device is replaced. NCS uses an asset tag painted on the equipment (for example "MP-1/PLC-R1"), which stays with the function.
Priya's schema keys every table on that tag, which is how the asset, logger and flow tables join cleanly even after PLC-R2's documented firmware turned out to be wrong.
4.2 Relationships
Forty loggers report through GW-LOG. Storing "gateway" as an attribute of each logger row creates a one-to-many relationship that lets a query answer "which loggers go dark if GW-LOG fails?" in one line. Similarly, a flow table with source and destination keys links assets into the communication picture used in Chapter 12.
Priya's loggers table holds tag, room and gateway_tag. Her query select tag, room from loggers where gateway_tag = 'GW-LOG' lists all forty loggers with their rooms, and that list became the impact table in NCS's continuity plan.
4.3 What not to store
Passwords, private keys and full configuration backups do not belong in a shared asset table. Store a reference ("credential held in vault entry OT-17") and keep the secret in a system designed for it. A spreadsheet of passwords on a shared drive is a common finding in OT assessments.
In Priya's schema, the assets table has an owner column and a source column but no credential column. HMI-C's local administrator password, which she later finds on a sticky note, belongs in the vault with a reference such as OT-17 in the table, not in a cell anyone with the spreadsheet can read.
4.4 Data quality fields
Add "source" (documentation, network observation, walk-down) and "last verified" date to each row. An inventory without dates cannot be trusted after a year.
PLC-R2 shows why. Its documented firmware, 2.4, came from the contractor's commissioning records; the passive capture reported 2.1; and Priya's walk-down read 2.1 from the front panel. With a source column, the row now reads firmware 2.1, source walk-down, last verified on the visit date, and a reader can see that the older value came from documents that were never updated after commissioning. Without the date, nobody would know whether the walk-down reading was last week's or two years old.
Worked example (synthetic NCS)
Priya's schema: assets(tag, type, function, zone, firmware, owner, source, last_verified); loggers(tag, room, gateway_tag); flows(src_tag, dst_tag, protocol, port, approved). Her query "select tag, room from loggers where gateway_tag = 'GW-LOG'" lists all 40 loggers and their rooms, which became the impact list in the facility's continuity plan.
Synthetic case — Northgate Cold Storage. Schema and counts are invented.
Errors and limits
A model is only as good as its upkeep; assign an owner. Over-normalising a small facility's data can make it harder to use than a well-kept single table.
Exercises
- EX010 — Choosing a key. Why is the painted asset tag a better key than the IP address for PLC-R1? Rubric: 3 = stable across re-addressing and replacement; tied to function; 2 = partial; 1 = "easier"; 0 = none.
- EX011 — Query for an impact list. Write SQL listing loggers in room "Freezer-2" served by GW-LOG. Rubric: 3 = correct where clause on both columns; 2 = one condition; 1 = syntax only; 0 = none.
- EX012 — Handling a credential field. A colleague adds a "password" column. What do you replace it with? Rubric: 3 = vault reference, secret elsewhere, access-controlled; 2 = remove only; 1 = encrypt the sheet; 0 = keep.
Synthesis
A small, stable data model with keys, relationships and dates is what turns inventory into answers.
Continue studying — chapter 04 of 30
← Chapter 03 (draft) · Course chapter index · Chapter 05 (draft) →