HI520 Unit 8 Seminar Reflection Example

The Column That Answered a Different Question: A Unit 8 Seminar Reflection on Storing a Latest Control Score Versus Deriving It for a Measurement Year

[Student Name]

Purdue University Global

HI520: Database Design and SQL

Unit 8 Seminar Reflection

[Instructor Name]

[Date]

Calder Bay Pediatrics, its dashboard and every count are synthetic; the sources cited are real.

What I Proposed

In my Unit 4 design note I wrote that no column would store a patient's latest score, because it could be derived. By this unit I had changed my mind. The practice's asthma dashboard shows each child's most recent control score for about 1,600 children on one screen, and deriving it means a sorted lookup across the encounter table every time the page loads. I proposed adding last_control_score to the patient table, updated by a trigger whenever a new encounter score was saved. My note said, "Store it once, read it a thousand times." I brought that proposal to seminar expecting a short discussion about trigger design.

The Case

The instructor's case was a composite quality measure for the 2025 measurement year: of children with persistent asthma, how many had their most recent control score in 2025 at 19 or below? The measure is calculated in February 2026, after the year closes. By then, many children have already had visits in January and February 2026.

What a Classmate Showed

Priya, who had built a similar column at her workplace, asked what my column would hold on the day the measure ran. The answer was obvious once she asked: it would hold each child's newest score, from 2026 if they had been seen since New Year's Day. The measure needs the newest score from 2025. She then ran the comparison on our shared synthetic data during the session. Of 370 children with any 2025 score, 61 had a newer 2026 score sitting in my column. For 23 of them, the 2026 score fell on the other side of the cutoff from their last 2025 score, so a measure built on my column would have counted 23 children in the wrong group, about 6 percent of the denominator. My column was not wrong; it answered "what is this child's latest score?" while the measure asked "what was this child's latest score as of December 31?"

Why I Changed My View

I had been thinking about the column as a cached copy of a fact, and caches are fine when the fact means the same thing to every reader. A latest score is not one fact; it depends on the date it is computed for. Every measurement window, every quarter, every look-back for a visit creates a different "latest." A single stored column can hold only one of them, and it will be the wrong one for most questions. The relational model's case for deriving values from base facts, rather than storing their consequences, is exactly about this: stored derivations go stale and drift from the data they summarize (Codd, 1970). Data quality frameworks call the resulting problem plausibility over time, a value that was true when written and is false when read (Weiskopf & Weng, 2013). The speed concern was real, but the seminar solved it differently. A second classmate suggested an index on encounter by patient and start time, which makes the derived lookup fast enough for the dashboard, and a materialized view refreshed nightly for screens that only need yesterday's picture. Both keep the base data as the single source of truth, and both can be rebuilt for any date. I also realized the trigger I had planned would have created a second problem. When an encounter score is corrected, for example because a nurse entered a score under the wrong child, the trigger would update the column on the save but would not know to restore the previous value for the child whose record was wrongly changed. Over months, a handful of these corrections would leave the column disagreeing with the encounter table, and nobody would notice until a report and the dashboard showed different numbers for the same child. Deriving the value removes that whole class of error, because there is nothing stored to fall out of step.

What I Will Do Differently

I removed the column from my design and replaced it with a function that returns a child's latest score as of any date passed to it, with the dashboard calling it with the current date and the measure calling it with December 31. Before I propose storing any derived value again, I will ask one question: is there more than one date anyone might want this value as of? If the answer is yes, I will derive it. Reflection works best when it surfaces an assumption built into one's own work (Rolfe et al., 2001), and mine was that "latest" has only one meaning.

References

Codd, E. F. (1970). A relational model of data for large shared data banks. Communications of the ACM, 13(6), 377-387. https://doi.org/10.1145/362384.362685

Rolfe, G., Freshwater, D., & Jasper, M. (2001). Critical reflection for nursing and the helping professions: A user's guide. Palgrave Macmillan.

Weiskopf, N. G., & Weng, C. (2013). Methods and dimensions of electronic health record data quality assessment: Enabling reuse for clinical research. Journal of the American Medical Informatics Association, 20(1), 144-151. https://doi.org/10.1136/amiajnl-2011-000681