HIM 220 Module 3 Databases and Standards Short Paper Example

Tables, Keys and Shared Meaning: How Kettle River Health Stores and Standardizes Data

[Student Name]

Southern New Hampshire University

HIM 220: Healthcare Data Management

Module Three 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.

Tables, Keys and Shared Meaning: How Kettle River Health Stores and Standardizes Data

Behind Kettle River Health's readmission dashboard sit millions of rows of data in tables, and behind many of those rows sit standard codes that tell a computer what each value means. Understanding both layers explains why some reports can be trusted and others cannot. This paper describes how relational databases organize health data, how a simple query works and how three clinical standards give data shared meaning.

Tables, Rows and Columns

A relational database stores data in tables. Each table describes one kind of thing, such as patients, encounters or laboratory results. Each row is one instance, such as one hospital stay, and each column is one attribute, such as the admission date. Keeping each kind of thing in its own table avoids repeating the same information many times; a patient's birth date is stored once in the patient table rather than on every encounter.

Keys and Relationships

A primary key uniquely identifies each row in a table, such as a patient identifier in the patient table or an encounter number in the encounter table. A foreign key is a column that refers to another table's primary key, such as the patient identifier stored in each encounter row. Keys create relationships. One patient can have many encounters, a one-to-many relationship, and one encounter can have many laboratory results. When duplicate patient records exist, one person has two primary keys, and their encounters split between them, which is why the duplicate problem from the last module distorts readmission counts.

Table 1. Simplified Tables Behind the Readmission Dashboard

Note. Simplified by the author from the warehouse design.

A Readmission Query in Plain Language

A query asks the database a question. Counting readmissions requires joining the encounter table to itself: for each inpatient discharge, look for another inpatient admission for the same patient identifier that begins within 30 days after the discharge time. In structured query language, that means selecting discharges, joining them to later admissions on patient identifier and filtering by the date difference and encounter type. Each choice in the query, such as whether to include observation encounters, is a definition decision, which is why the quality and finance dashboards disagreed.

Data Types and Allowed Values

Each column also has a data type and, often, a list of allowed values. Dates must be stored as dates, not text, so the database can calculate a length of stay. Encounter type draws from a short list such as inpatient, observation, emergency and outpatient. Allowed values are where many conformance problems begin: when one hospital records observation stays as a separate type and the other records them as outpatient, any query that filters by type will treat the two hospitals differently without anyone noticing.

Operational Databases and Warehouses

The electronic record's database is built for transactions: registering a patient, placing an order, recording a vital sign, quickly and reliably. Running large analytic queries against it could slow clinical work. Kettle River therefore copies data nightly into a data warehouse organized for analysis, combining the record with billing, the reference laboratory and patient satisfaction data. The trade-off is that the warehouse is a day behind and depends on each feed being mapped correctly.

Why Standards Matter

A database can store the text "HbA1c," "A1C" or "glycohemoglobin," but a computer cannot know these mean the same test unless each carries a common code. Standards solve this by assigning codes to concepts. Classifications such as ICD-10-CM group conditions into categories for statistics and billing. Terminologies such as SNOMED CT capture clinical meaning in much finer detail for use in care. Both are needed, for different purposes.

LOINC for Laboratory Tests

McDonald et al. (2003) traced LOINC's growth as a shared naming system for laboratory observations, designed so that results from different laboratories and instruments could be recognized as the same test. Each code specifies what is measured, the property, timing, specimen type, scale and sometimes method. At Kettle River, 12% of reference laboratory results lack LOINC codes, so they fall out of any report that selects by code.

SNOMED CT for Clinical Concepts

SNOMED CT is a large clinical terminology whose concepts are linked by relationships, so that, for example, a specific type of pneumonia is recorded as a kind of lung infection. Problem lists in many record systems store SNOMED CT concepts behind the friendly terms clinicians select, with mappings to ICD-10-CM for billing. Lee et al. (2014) reviewed published literature on SNOMED CT and found that most studies described its design or potential rather than its use in working clinical systems, suggesting its benefits depend heavily on implementation.

RxNorm for Medications

Medications are named many ways: brand names, generic names, package codes and strengths. Nelson et al. (2011) described RxNorm, developed by the National Library of Medicine, which gives each clinical drug one standard name and number and ties that entry to the many drug vocabularies pharmacies and drug databases already use. RxNorm allows a medication list from one system to be matched to another, supporting medication reconciliation and exchange.

Standards in the Warehouse

Kettle River's warehouse depends on these standards. Laboratory measures in the diabetes dashboard select by LOINC code, the problem-list registry selects by SNOMED CT concepts and medication adherence reports rely on RxNorm. When a feed arrives without standard codes, or with local codes mapped incorrectly, the data may be present in the warehouse yet invisible to reports.

Table 2. Standards and Their Uses at Kettle River

Note. Compiled by the author from warehouse documentation.

Conclusion

Relational tables and keys determine how data connect, queries turn definitions into numbers and standards determine whether a computer recognizes what the data mean. Duplicate keys, unmapped codes and undocumented query choices explain many of Kettle River's reporting problems. The next project builds a data dictionary that makes those definitions explicit.

References

Lee, D., de Keizer, N., Lau, F., & Cornet, R. (2014). Literature review of SNOMED CT use. Journal of the American Medical Informatics Association, 21(e1), e11-e19. https://doi.org/10.1136/amiajnl-2013-001636

McDonald, C. J., Huff, S. M., Suico, J. G., Hill, G., Leavelle, D., Aller, R., Forrey, A., Mercer, K., DeMoor, G., Hook, J., Williams, W., Case, J., & Maloney, P. (2003). LOINC, a universal standard for identifying laboratory observations: A 5-year update. Clinical Chemistry, 49(4), 624-633. https://doi.org/10.1373/49.4.624

Nelson, S. J., Zeng, K., Kilbourne, J., Powell, T., & Moore, R. (2011). Normalized names for clinical drugs: RxNorm at 6 years. Journal of the American Medical Informatics Association, 18(4), 441-448. https://doi.org/10.1136/amiajnl-2011-000116