Chapter 9 — Data and databases

Chapter 9 — Data and databases

Lesson map

Lesson 09 published. Map, example, EX025.

flowchart TD
  N0["Learning outcomes"]
  N1["9.1 Tables and keys"]
  N2["9.2 Querying safely"]
  N3["9.3 On-chain or off-chain"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4
  N4 --> EX["Worked example"]
  EX --> Q["Exercise EX025"]

Logical drawing

flowchart LR
  N0["Learning outcomes"]
  N1["9.1 Tables and keys"]
  N2["9.2 Querying safely"]
  N3["9.3 On-chain or off-chain"]
  N4["Synthesis"]
  N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX025"]

[Learning outcomes]
    |
    v
[9.1 Tables and keys]
    |
    v
[9.2 Querying safely]
    |
    v
[9.3 On-chain or off-chain]
    |
    v
[Synthesis]
    |
    v
[Worked example] --> [Exercise EX025]

Learning outcomes

You will (1) design tables and keys; (2) query data safely; (3) decide what belongs on-chain and off-chain.

9.1 Tables and keys

A relational database stores data in tables with rows and columns, and a primary key uniquely identifies each row. Sofia designs a members table with a code key, a shifts table with a shift key and a member code, and an approvals table recording who approved each shift. Foreign keys link the tables, so a shift cannot refer to a member who does not exist. The database enforces these rules itself. She learns that good data design prevents whole classes of error before any security control is needed, much as a well-designed contract prevents invalid states rather than detecting them afterwards.

9.2 Querying safely

Sofia writes queries to list approved shifts per week. She uses parameterised queries, where values are passed separately from the query text, so input can never change the meaning of the query. This prevents a well-known class of injection weakness listed by OWASP. She also limits which accounts may change data: coordinators can approve shifts, while the reporting account can only read. Separating read and write permissions means a mistake or misuse in reporting cannot alter records. These habits apply directly to the off-chain services that sit beside any blockchain system and feed it data.

9.3 On-chain or off-chain

Not everything belongs on a ledger. Sofia keeps names, contact details and shift notes in the database, which can be corrected and deleted under privacy law, and puts only voucher balances and transfers on the ledger, linked to pseudonymous codes. A hash of each week's approved shift list is recorded on-chain, so anyone can later check the list was not altered, without revealing its contents. This split, described in NIST IR 8202 as a common pattern, gives integrity where it matters and keeps personal data where it can be managed responsibly by the cooperative.

Worked example

Sofia links members, shifts and approvals with foreign keys, uses parameterised queries and records only a weekly hash of approvals on-chain.

Synthetic case. The database is synthetic; OWASP and NIST IR 8202 are real sources.

Errors and limits

Putting personal data on an immutable ledger makes later correction or deletion very difficult.

Exercises

• EX025 — Foreign key role. What does the foreign key prevent? Rubric: 3 = a shift referring to a non-existent member;; 2 = orphan rows;; 1 = slow queries;; 0 = none.
• EX026 — Injection defence. How does Sofia prevent injection? Rubric: 3 = parameterised queries;; 2 = separate values;; 1 = hiding the database;; 0 = none.
• EX027 — On-chain record. What is recorded on-chain about shifts? Rubric: 3 = a hash of each week's approved shift list;; 2 = a hash;; 1 = names;; 0 = none.

Synthesis

Careful data design and an on-chain/off-chain split give integrity without exposing personal data.

Continue studying — chapter 09 of 40

← Chapter 08 (draft) · Course chapter index · Chapter 10 (draft) →