NUR 7010 Module 2 Database Design Paper: A Wound Registry Built on the Relational and Entity-Relationship Models Example

One Row per Wound, Not per Resident: Designing a Pressure Injury Registry for Six Monmouth County Nursing Homes

Student Name

M.S. in Nursing, Nursing Informatics, Thomas Edison State University

NUR-7010: Nursing Informatics: Databases and Knowledge Management

Mentor Name

Month Day, Year

One Row per Wound, Not per Resident: Designing a Pressure Injury Registry for Six Monmouth County Nursing Homes

The Spreadsheet Problem

The organization in this example is invented. A group of six skilled nursing facilities in Monmouth County, together caring for about 780 residents, tracks pressure injuries for its quality committee in a shared spreadsheet. Each row is a resident. Columns hold the resident's name, facility, admission date and then a long run of columns for wounds: wound 1 location, wound 1 stage, wound 1 date found, wound 1 measurements, wound 2 location and so on up to wound 6. Measurements from later assessments are typed into a notes column. Stages are sometimes written as numbers, sometimes as words, and sometimes as "unstageable" or "DTI." When a resident moves between facilities, a second row is created. The quality committee wants to know how many pressure injuries began in each facility, how long they take to heal and which treatments are used, and the spreadsheet cannot answer any of these questions reliably.

The group asked its informatics nurse to design a proper database.

Modeling the Domain

Chen (1976) argued that designers should first describe data as the real world presents it, in entities and relationships, and only then decide on storage. Entities are things that exist and can be distinguished, such as a resident or a wound. Relationships connect entities, such as a resident having a wound. Attributes describe entities and relationships, such as a wound's location or the date a relationship began. Chen argued that this model gives designers and users a shared picture of the data and can be translated into different kinds of databases.

The informatics nurse identified five entities. A facility has many residents over time. A resident may have many stays across facilities. A resident may have many wounds. A wound has many assessments over its life, each recording stage, size and appearance. A wound may receive many treatments, each with start and end dates. Drawn as a diagram, these form a chain from facility to stay to resident to wound to assessment and treatment.

The Relational Model

Codd (1970) proposed storing data as relations, which users experience as tables of rows and columns, and separating how users see the data from how the computer stores it. Each row is identified by a key, and relationships between tables are expressed by shared key values rather than by physical pointers. Codd also introduced normalization, the process of arranging data so that each fact is stored once, which prevents the inconsistencies that arise when the same fact is repeated in many places.

The spreadsheet broke these principles in several ways. It repeated wound columns, so a seventh wound had nowhere to go. It stored assessment history as text, so healing could not be calculated. It duplicated residents who moved, so counts were wrong. And it mixed codes and words for stage, so a simple count by stage gave different answers depending on how the cell was typed.

The Tables

Stage is stored as a code from a fixed list that matches the staging system the facilities use, including unstageable and deep tissue injury, rather than as free text. Measurements are numbers with units fixed in centimeters. "Present on admission" is a required yes or no on each wound, which lets the committee separate wounds that began in a facility from those that arrived with the resident.

Standard Data Elements

Werley et al. (1991) described the Nursing Minimum Data Set, a set of sixteen essential elements in three groups meant to make nursing data standardized and comparable across settings. Nursing care elements are nursing diagnosis, nursing intervention, nursing outcome and intensity of nursing care. Patient elements include personal identification, date of birth, sex, race and ethnicity and residence. Service elements include a unique facility number, a unique health record number, the principal registered nurse provider, admission and discharge dates, disposition and expected payer.

The registry uses the patient and service elements directly in the Resident and Stay tables, which means its data can be combined with other data sets built on the same standard. The nursing care elements map onto the wound, assessment and treatment tables: the wound is a nursing problem, the treatment an intervention and healing an outcome.

Rules That Protect Quality

The database enforces rules that the spreadsheet could not. A wound cannot exist without a resident and a stay. An assessment date cannot fall before the wound's identification date. Stage must come from the code list. Length, width and depth must be positive numbers within plausible ranges. Each facility can see and edit only its own residents' records, while the quality committee sees all six facilities in reports without names.

Reports the Design Makes Possible

The design was tested against the committee's three questions before any data were loaded. Where do injuries begin? Count wounds with present on admission marked no, grouped by the facility of the stay in which they were found. How long do they take to heal? For each wound, subtract the identification date from the date of the first assessment recording it as healed, then summarize by stage and facility. Which treatments are used? Count treatment rows by type, linked to the wound's stage at the time the treatment started. Each question became a short query that runs in seconds, and each would have required hours of hand counting in the spreadsheet. Writing the queries early also exposed one gap: the original design did not record the date a wound healed directly, so a healed status was added to the assessment code list.

Migrating the Old Data

The informatics nurse moved two years of spreadsheet data into the new tables with a nurse from each facility checking the results. Free-text stages were mapped to codes with a lookup table, and 37 entries that could not be mapped were reviewed by hand. Duplicate residents were merged by matching date of birth, name and facility history. Assessment notes were parsed where possible; the rest were kept as historical notes and excluded from healing calculations.

Limits

A registry built for pressure injuries may not fit other wound types without changes. Data entry still depends on nurses assessing and recording accurately. Migrated historical data are less reliable than new data, and early reports will flag that difference.

Conclusion

The spreadsheet failed because it forced a complex domain into one row per resident. Chen's model described the domain in real-world terms, Codd's relational model and normalization stored each fact once and the Nursing Minimum Data Set supplied standard elements. The registry can now answer the committee's questions about where injuries begin, how long they take to heal and which treatments are used.

References

Chen, P. P.-S. (1976). The entity-relationship model: Toward a unified view of data. ACM Transactions on Database Systems, 1(1), 9-36. https://doi.org/10.1145/320434.320440

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

Werley, H. H., Devine, E. C., Zorn, C. R., Ryan, P., & Westra, B. L. (1991). The Nursing Minimum Data Set: Abstraction tool for standardized, comparable, essential data. American Journal of Public Health, 81(4), 421-426. https://doi.org/10.2105/AJPH.81.4.421