HIM 400 Module 2 Database Structures Short Paper Example

From One Wide Workbook to Seven Related Tables: Redesigning Cold Brook Health's Diabetes Registry

[Student Name]

Southern New Hampshire University

HIM 400: Communication and Technologies II

Module Two Short Paper

[Instructor Name]

[Date]

The organization, setting and figures below are a composite written as a model document. No real employer, client, colleague or patient is described.

From One Wide Workbook to Seven Related Tables: Redesigning Cold Brook Health's Diabetes Registry

Cold Brook Health's diabetes registry began as a spreadsheet kept by a nurse care manager. It now holds about 6,800 patients, one row each, with columns named A1c_1 through A1c_14 because every new result was added to the right of the last. The practices depend on it to find patients due for tests or outreach, but no one fully trusts it. This paper explains why a flat file cannot support the registry's work and proposes a relational design with seven tables, defined keys and relationships, a normalized structure and a data dictionary.

Why the Spreadsheet Fails

A flat file stores everything about an entity in one row, which works for a small list but breaks down as data accumulate. Our workbook shows three classic problems. The first is repeating groups: the fourteen A1c columns cannot be queried as one field, so finding each patient's most recent value requires a formula that often picks the wrong column. The second is redundancy: each row repeats the practice name, address and care manager, so when a practice moved last year, 612 rows had to be edited and 41 were missed. The third is lost detail: a result stored as a number in a cell has no date, source laboratory or units attached, so a value from an outside lab looks identical to one from our own.

Deciding Who Belongs in the Registry

Before designing tables, the team had to define a patient with diabetes. Hripcsak and Albers (2013) note that electronic record data reflect how care was delivered and billed as much as the patient's condition, so identifying a cohort reliably usually means combining several kinds of evidence. The registry therefore includes adults with a type 1 or type 2 diabetes diagnosis code on two or more encounters, or one diagnosis plus a diabetes medication order, or two HbA1c results of 6.5% or higher. Storing diagnoses, medications and results in their own tables makes that rule possible to run, audit and change.

The Seven Tables

The design uses seven tables. PATIENT holds one row per person with demographic fields. PRACTICE and CLINICIAN describe the 14 practices and their providers. ENCOUNTER records each visit, linked to a patient, a clinician and a practice. DIAGNOSIS stores ICD-10-CM codes per encounter. LAB_RESULT stores each laboratory value with its date, units, LOINC code and source. MEDICATION_ORDER stores diabetes prescriptions with RxNorm codes. Table 1 shows each table's primary key and the foreign keys that connect it to others.

Table 1. Registry Tables, Primary Keys and Foreign Keys

Note. Designed by the author; field names follow Cold Brook Health's naming standard.

Keys and Relationships

Every table uses a surrogate primary key, a system-assigned number with no meaning of its own, rather than a natural key such as the medical record number. Record numbers change when duplicate charts are merged, and our master patient index merged 118 charts last year, so a key built on them would break links. The medical record number is still stored in PATIENT as an attribute with a unique constraint.

Most relationships are one-to-many: one patient has many encounters, and one encounter can carry many diagnoses. The link between patients and clinicians is different. A patient may see several clinicians, and each clinician sees many patients, which is a many-to-many relationship that relational tables cannot hold directly. An eighth, associative table, ATTRIBUTION, resolves it with a row for each patient-clinician pair, a start date, an end date and a flag marking the primary care provider of record.

Normalization

Normalization organizes tables to remove redundancy and the update errors it causes. First normal form requires atomic values and no repeating groups, which replaces the fourteen A1c columns with one LAB_RESULT row per test. At second normal form, no attribute may rely on just part of a combined key, a rule that matters for a table such as ATTRIBUTION, whose rows are identified by a patient and a clinician together. Third normal form removes fields that depend on other non-key fields: a practice's address depends on the practice, not the patient, so it lives only in PRACTICE. When a practice moves, one row changes instead of 612.

Reporting sometimes favors speed over purity. The team will build a denormalized view that joins each patient to their latest HbA1c, practice and care manager for the outreach list, but that view is generated from the normalized tables each night and is never edited by hand.

Data Dictionary and Vocabularies

A data dictionary defines every field so that analysts, clinicians and vendors read the data the same way. Table 2 shows an excerpt for LAB_RESULT. Coded vocabularies do much of the work. LOINC identifies the test, so HbA1c results from our lab and outside labs share a code even when their local names differ, and ICD-10-CM codes in DIAGNOSIS let the inclusion rule find type 1 and type 2 diabetes consistently.

Table 2. Data Dictionary Excerpt: LAB_RESULT

Note. Excerpt; the full dictionary covers all eight tables.

Building Quality Into the Structure

Weiskopf and Weng (2013) reviewed how researchers judge the quality of electronic record data and found that most assessments address a handful of recurring questions: whether data are present, whether they are true, whether sources agree, whether values are believable and whether they are current. The design answers several of these through constraints. Required fields enforce presence, the range rule rejects an HbA1c of 71 typed for 7.1, foreign keys prevent a result from being attached to a patient who does not exist and collection dates allow the registry to show how recent each value is. Agreement between sources still needs periodic review, since two labs may report different values for the same week.

What the New Design Enables

Bates et al. (2014) argued that analytics add the most value when they help organizations find high-risk patients and act on that information. The relational registry makes that practical. A single query can return every patient whose latest HbA1c is above 9%, the date of that result, the lab that ran it and the patient's attributed clinician, and the same query will still work after the next thousand results arrive.

Conclusion

The spreadsheet registry fails because its structure mixes entities, repeats data and strips results of context. The proposed design separates patients, practices, clinicians, encounters, diagnoses, results and medications into related tables with stable keys, resolves the patient-clinician relationship through attribution, normalizes to third normal form and documents every field. Module Three builds on this structure by writing the queries that extract data from it.

References

Bates, D. W., Saria, S., Ohno-Machado, L., Shah, A., & Escobar, G. (2014). Big data in health care: Using analytics to identify and manage high-risk and high-cost patients. Health Affairs, 33(7), 1123-1131. https://doi.org/10.1377/hlthaff.2014.0041

Hripcsak, G., & Albers, D. J. (2013). Next-generation phenotyping of electronic health records. Journal of the American Medical Informatics Association, 20(1), 117-121. https://doi.org/10.1136/amiajnl-2012-001145

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