Chapter 13 — Data systems
Lesson map
Lesson 13 published. Map, example, EX037.
flowchart TD
N0["Learning outcomes"]
N1["13.1 Schemas as rules"]
N2["13.2 Reference data"]
N3["13.3 Master data"]
N4["13.4 History and change"]
N0 --> N1 --> N2 --> N3 --> N4
N4 --> EX["Worked example"]
EX --> Q["Exercise EX037"]
Logical drawing
flowchart LR
N0["Learning outcomes"]
N1["13.1 Schemas as rules"]
N2["13.2 Reference data"]
N3["13.3 Master data"]
N4["13.4 History and change"]
N0 --> N1 --> N2 --> N3 --> N4 --> Q["EX037"]
[Learning outcomes]
|
v
[13.1 Schemas as rules]
|
v
[13.2 Reference data]
|
v
[13.3 Master data]
|
v
[13.4 History and change]
|
v
[Worked example] --> [Exercise EX037]
Learning outcomes
You will (1) design schemas that encode governance rules; (2) use constraints and reference data; (3) manage master data for customers and premises.
13.1 Schemas as rules
Kofi designs a relational schema for customers, premises, meters and readings. Primary keys make each entity unique, and foreign keys ensure that a reading refers to a real meter and a meter to a real premise. Not-null constraints enforce required fields. He explains that putting rules into the schema means the database itself refuses invalid data, rather than relying on every application to check. The schema becomes a precise, enforceable statement of part of Tidewater's data standard. Rejected inserts are logged with reasons.
13.2 Reference data
Tariff codes, meter types and district codes are reference data: small, controlled lists used across systems. Kofi finds three versions of the district list in different systems, with one district renamed in only two. He proposes a single reference table owned by the network planning team, with each change dated and approved. DAMA-DMBOK2 treats reference and master data as a distinct management area because inconsistencies in shared lists ripple into every report that uses them. Other systems now copy the table nightly.
13.3 Master data
Customer and premise records are master data, shared by billing, customer service and field operations. Kofi finds duplicate premises created when field staff register new connections without searching first. He designs a matching step that compares normalised addresses and coordinates before a new premise is created, and a steward role in customer operations who resolves possible duplicates. He notes that master data quality depends as much on process and ownership as on database design. The steward clears the possible duplicates queue every week.
13.4 History and change
Customers move, tariffs change and meters are replaced. Kofi designs tables that keep history with valid-from and valid-to dates, so past bills can be explained using the facts that applied at the time. Without history, a customer querying an old bill would see today's tariff and become confused. He tests the design with synthetic examples, including a meter replaced mid-month, and confirms that billing calculations match the period-by-period facts. Customer service staff confirm that the history view answers queries they previously escalated to billing.
Worked example
Kofi encodes keys and constraints in a schema, consolidates three district lists into owned reference data, adds duplicate-premise matching with a steward and keeps dated history.
Synthetic case. Schemas are synthetic; DAMA-DMBOK2 is real.
Errors and limits
Constraints can block urgent fixes; define a controlled override process.
Exercises
- EX037 — Foreign key purpose. What do foreign keys ensure here? Rubric: 3 = readings refer to real meters, meters to real premises;; 2 = partly correct;; 1 = vague;; 0 = none.
- EX038 — District lists. How many versions of the district list existed? Rubric: 3 = three;; 2 = partly correct;; 1 = wrong;; 0 = none.
- EX039 — History purpose. Why keep valid-from and valid-to dates? Rubric: 3 = to explain past bills with facts at the time;; 2 = mentions past;; 1 = vague;; 0 = none.
Continue studying — lesson 13 (chapter 13 of 50)
← Chapter 12 · Course chapter index · Chapter 14 →
Continue to the next authored lesson.