Centralized-Decentralized Health Management System (CDHMS): A Federated Database Architectural Prototype for Healthcare Interoperability

1. Introduction

1.1. Mission and Vision

The CDHMS's mission is to address one of the most pervasive and costly challenges in American healthcare—how to share patient information securely, accurately, and safely from varying health information systems without infringing on patient privacy. Independent health networks are joined together in the system using a Master Patient Index (MPI) and a runtime Interoperability Mapping layer. Unlike the current disjointed approaches, CDHMS is built to work with and enhance already available standards like HL7 FHIR (HL7 International, 2023) and other standards, as it is intended to form a federated coordination layer that does not replace them. CDHMS strives to create a national health data infrastructure in which all patients have one, private, individual longitudinal data record. Personally identifiable information will not be shared with public health agencies, and there is explicit control over who accesses the data by the patient.

1.2. Problem Statement and Solution

The current situation of healthcare data in the United States is highly fractured, with thousands of incompatible data silos that contain data with different access policies and data models (ONC, 2020). Such a systemic split poses serious patient safety concerns: In an emergency, patients may be admitted into care with an incomplete medical history, resulting in treatments being given that are potentially contraindicated or missed diagnoses (National Academies of Sciences, Engineering, and Medicine, 2018). On top of this, there is no universal patient identifier, meaning that patient records are duplicated and care histories are broken. In addition, critical interoperability gaps may exist which prevent data sharing seamlessly even if there is an agreement, with each facility potentially using different clinical coding standards that need dedicated mapping layers to translate between the standards (WHO, 2023; HL7 International, 2023).

In addition to its impact on patient care, this fragmentation can disrupt public health initiatives, such as epidemiological surveillance and can produce bias in disease outbreak detection (CDC, 2023). It also leaves behind significant security and privacy risks, with inadequate logging of access to records and the continued existence of unencrypted or paper records leading to the loss of tens of millions of healthcare records every year (IBM Security, 2023). It is technically and ethically impractical to create a single, centralized database, so systems such as CDHMS suggest a federated, decentralized structure. This approach relies on a coordinating index to ensure secure interoperability, rigorous auditing and access control and local institutional autonomy.

Figure 1. Federated Architecture Overview - Federated Layer, Health Centers, Interoperability Layer, and Emergency Pull Mechanism.

Figure 1. Federated Architecture Overview - Federated Layer, Health Centers, Interoperability Layer, and Emergency Pull Mechanism.

2. Related Work

CDHMS is an extension of the existing research on federated healthcare architectures, design of Master Patient Index, access control models and patient consent frameworks. This section contextualizes CDHMS in each of these research threads and highlights the gaps which inspire the current design

2.1. Federated Healthcare Architectures and FHIR-Based Interoperability

The need for decentralization of health information systems from central to federated has been both technically necessary and prescribed by regulation. Heryawan et al. (2025) studied FHIR based interoperability design patterns and came up with a federated server architecture that enables distributed load management and resilient tracking of patients, where a resurgent pandemic like COVID-19 has emphasized the need for federated interoperable patient tracking systems. Likewise, Adelusi et al. (2025) introduced an interoperability gateway, consent management modules, and distributed repositories-based federated FHIR model for achieving seamless data exchange across various hospitals.

A systematic scoping review by Tabari et al. (2024) looked at current FHIR data model implementation research and identified federated approaches that were successful in reconciling multi-site data during COVID-19 clinical trials. The Vasileiou et al. (2026) also showed a federated FHIR use case across five distinct clinical environments to monitor heart failure, with a successful validation of semantic harmonization in a real-world environment. In addition to these approaches at the server level, Bossenko et al. (2024) suggested that legacy CDA documents can be converted to FHIR using the FHIR Mapping Language, thus enabling federated semantic interoperability without the need for a single data format, a concept that is echoed in CDHMS's Interoperability Mapping layer.

While most FHIR-centric efforts are on the API layer, CDHMS brings the normalized relational schema layer as a structured coordination substrate underneath the API, making consent, audit, and identity resolution available as queryable, transactionally consistent tables, not in the message payload.

2.2. Master Patient Index and Probabilistic Record Linkage

An inability to recognize a universal patient identifier is one of the most basic challenges for accurate patient cross-facility matching in the U.S. health system. There are two algorithmic families: deterministic where pairs of candidate records are matched against fixed identifiers, and probabilistic, in which pairs of candidate records are scored on various demographic attributes (Nagels et al., 2019). The latter are required in a place where the quality of data is not consistent.

Nelson et al. (2023) showed that machine learning tuning via Bayesian optimization of the Fellegi-Sunter empirical scores on the matching weights can boost both sensitivity and specificity in the deployments of SantéMPI in low- and middle-income countries. Their open-source tooling has been tested with more than 21 million patient records from multiple source systems, and is the state-of-the-art scalable probabilistic patient identity resolution.

CDHMS's architecture of MPI design – MPI_Local_Link and MPI_Remote_Link tables along with a Match_Confidence score – closely resembles this. The present prototype is based on fixed confidence values for verification purposes, but as mentioned in future work, it is a major production requirement to replace them with a Fellegi-Sunter or supervised-learning engine. Unlike standalone MPI systems, CDHMS provides a schema that integrates identity resolution and consent and audit tables so that it allows queries to gather identity confidence and access-control state, which are not available in standalone MPI systems.

2.3. Access Control Models for Electronic Health Records

The research on the access control in EHR systems has been going on for more than 20 years. A systematic review of EHR access control solutions (Cobrado et al, 2024) compiled 20 journal articles and concluded that while RBAC is still the most popular access control solution deployed due to its ease of administration, it falls short in clinical contexts with varying authorization needs, given that its role-centric nature is difficult to adapt to different contexts. ABAC was found as an attractive option for fine-grained, contextual access because it can fuse the attributes of the subject, resource, action, and environment.

Relevant to the emergency access design, Oliveira et al. (2023) proposed an Acute Care ABAC model (AC-ABAC), which dynamically grants access to and revokes access to EHR data, depending on contextual attributes like on call status and life cycle of the care episode, with a validation performed at Amsterdam University Medical Center. They conclude that fine-grained ABAC is feasible to perform in time-critical clinical environments as the most complex policy evaluation averaged 194.89 ms.

In more recent times, Atlam and Yang (2025) have proposed a unified risk-aware RBAC+ABAC, which combines a Risk-Based Access Control (RiBAC) layer to dynamically adjust access privileges based on real-time threat information. This work highlights the maturity of RBAC+ABAC as a design pattern as well as its current shortcomings: neither RBAC nor ABAC nor the combination of both can overcome the problem of the accountability gap during emergencies where overrides are issued. CDHMS directly addresses this by using the Break_Glass_Event table and an append-only Audit_Log that directly records a tamper-proof trail, that is not found in most access-control driven EHR studies.

2.4. Patient Consent and Privacy Frameworks

In recent years, patient-controlled consent regimes have become more prominent after the enactment of the 21st Century Cures Act (ONC, 2020), and the recent growing body of literature on the many difficulties associated with binary opt-in/opt-out consent approaches. Cobrado et al (2024)’s EHR access control review observed that current consent systems generally do not work on the data-type or grantee-role level, which limits patients' capacity to control access, such as a distinction between emergency access and research access.

CDHMS' Patient_Consent table fills this need by defining consent as a tuple of (Patient, Grantee_Role, Access_Scope, Consent_Status, Valid_Until) — allowing patients to explicitly set the consent they wish to grant for one or more roles (provider, public health, or insurance) and one or more data scope. This design is based on the principle of granular, revocable, auditable consent (U.S. HHS, 2023), and is more than any of the federated FHIR frameworks detailed above, which tend to have the consent logic at the application layer or in external identity providers, but not built directly into the database schema.

CDHMS's value over previous efforts is the integration of these four attributes (MPI, Interoperability mapping, RBAC+ABAC access control, granular consent management, and tamper-evident auditing) into a unified normalized relational schema that to the authors' best knowledge has not been previously shown to be a deployable proof-of-concept in the academic literature.

3. Methods

3.1. Technologies, Tools, and Algorithms

MySQL Workbench 8.0 CE is the main tool used to deploy schema, populate data, and test queries. The decision to choose MySQL instead of other options was driven by the following factors: MySQL is a very widely used database in the healthcare IT field, it has an extensive support for the AES_ENCRYPT/AES_DECRYPT function family, and it has easy-to-use role-based access control primitives which immediately met the needs of CDHMS. Advanced Encryption Standard (AES) algorithm is used for security at rest to store Patient Social Security Numbers (SSNs) as ciphertext. While probabilistic record linkage is simulated with static Match_Confidence now, the architecture will be extended to eventually support other algorithms for resolving patient identities with high confidence, like the Fellegi-Sunter algorithm or supervised machine-learning classifiers.

3.2. Database Design and Justification

The CDHMS architecture reduces the risks and loss of local autonomy of handling a single monolithic database and uses a fully normalized relational schema. This middle-ground approach uses the relational model due to its strict referential integrity, transactional consistency, and highly queryable relationships as required for healthcare data (Codd, 1970; Elmasri & Navathe, 2015; Ramakrishnan & Gehrke, 2003). The core infrastructure builds the network's foundation by monitoring participating facilities, clinicians, and the standardized diagnosis reference codes, with a patient table ensuring sensitive information is secured with AES encryption.

A primary encounter fact table is used, with an associative entity that is used to easily accommodate many-to-many relationships between patient visits and many diagnosis codes. To keep these records on multiple decentralized networks uniform, the system uses a Master Patient Index (MPI) to ensure that globally unique patient IDs are correlated with a local ID and a match confidence score. An Interoperability Layer, which acts as a translator between different local coding systems and ICD-10 codes, supports this in order to ensure data consistency and not having different translations in the same node (HL7 International, 2023).

The system has a strict security and privacy guardrail courtesy of dedicated audit and consent layers. All database interactions are stored in an append only Audit Log, including a specific tracking system for data overrides in case of an emergency or "break-glass" situation, which will ensure complete compliance review. Patient privacy is maintained through comprehensive consent tables that control the level of access to different types of data, as well as opt-ins to public health research, and through a granular control over access to clinical data by providers, explicitly limiting the types of data that can be accessed outside of the scope of their medical specialty.Patient privacy is ensured with granular consent tables that control the different kinds of data shared, and with an Attribute-Based Access Control (ABAC) system that explicitly restricts access to clinical data outside of the scope of the provider's medical specialty.

Figure 2. Entity Relationship (ER) Diagram - complete CDHMS schema showing all 15 tables and their relationships.

Figure 2. Entity Relationship (ER) Diagram - complete CDHMS schema showing all 15 tables and their relationships.

3.3. Relational Schema

The relational schema is organized across six groups. Primary keys (PK) are underlined and foreign keys (FK) reference parent tables. Core Infrastructure entities include:

• Federated_Node (Node_ID PK, Node_Name, Node_Type, State, Address, Is_Active, Joined_Date)
• Patient (Patient_ID PK, SSN, First_Name, Last_Name, DOB, Blood_Type, Node_ID FK)
• Health_Center (Center_ID PK, Node_ID FK, Name, Center_Type, State, Address)
• Provider (Provider_ID PK, Name, Specialty, License_No, Center_ID FK, Is_Active)
• Diagnosis_Ref (ICD_Code PK, Description, Risk_Level)
• Encounter (Encounter_ID PK, Patient_ID FK, Provider_ID FK, Date_Time, Visit_Type, Clinical_Notes, Is_Emergency, Node_ID FK)
• Encounter_Diagnosis (Entry_ID PK, Encounter_ID FK, ICD_Code FK, Severity_Notes).
• MPI_Local_Link (MPI_Local_ID PK, Patient_ID FK, Node_ID FK, Match_Confidence, Linked_Date)
• MPI_Remote_Link (MPI_Remote_ID PK, Patient_ID FK, Node_ID FK, Remote_Patient_ID, Match_Confidence, Linked_Date).

3.4. Views

The four SQL views abstract the raw tables to be used by downstream roles. View_Public_Health_Trends combines the results from seven tables, groups them by Month, State, Diagnosis and Number of Cases, and filters them from Research_Consent to exclude patients who did NOT consent to research - leaving out all personally identifiable information. The same Four SQL views abstract from the raw tables are used for downstream roles in View_Insurance_Validation. View_Public_Health_Trends combines seven tables, aggregates results by month, state, diagnosis, and case count and filters results from Research_Consent that exclude patients who have not consented to participate - thereby removing all personally identifiable information from the results. View_Insurance_Validation is another clinical view that joins to the same tables with additional filters to only include the records that have been filtered by Patient_Consent where Grantee_Role = ‘cdhms_insurance_role’ and Consent_Status = ‘GRANTED’. View_Patient_Own_Records provides a complete history of the patients' encounters without showing any other patients' information. View_Patient_Access_Log displays the subset of Audit_Log that pertains to accesses of the Encounter, Patient, and Encounter_Diagnosis tables, so that patients can find out who has accessed their records.

3.5. Access Control Implementation

Role-Based Access Control (RBAC) is combined with Attribute-Based Access Control (ABAC) to help achieve granular data governance. There are six database roles defined, one role for each class of stakeholders. The roles, users, and the level of access along with the key privileges are summarized in Table 1.

3.5.1. ABAC Workflows

The roles are supplemented dynamically by ABAC. The Patient Consent Filter denies a provider's ability to read the patient's records unless there is a properly configured CONSENT row for that pair of patient-provider. This can be circumvented by the Break-Glass Override for the purposes of a life-threatening emergency and is automatically recorded on the system for subsequent compliance checks.

3.6. Test Query Design and Relational Algebra

The test queries exercise every functional layer of the schema, each run from the viewpoint of one of the six user roles in the schema. The six of the twelve are documented below with their formal relational algebra expression, where the standard notation is used for selection (σ) and projection (π).

Workflow 1: Database Administrator (Security Audit)

The DBA reviews the raw Audit Log to monitor system security and track who used the Break-Glass protocol.

SQL:  

Relational Algebra:${\mathsf{\pi }}_{\mathrm{Log}\text{\_}\mathrm{ID},\mathrm{DB}\text{\_}\mathrm{User},\mathrm{Action}\text{\_}\mathrm{Type},\mathrm{Target}\text{\_}\mathrm{Table},\mathrm{Is}\text{\_}\mathrm{Break}\text{\_}\mathrm{Glass},\mathrm{Logged}\text{\_}\mathrm{At}}\left(\mathrm{Audit}\text{\_}\mathrm{Log}\right)$

Workflow 2: Emergency Doctor (Break-Glass Access)

The ER physician performs an emergency "Break-Glass" read on the master patient table to fetch life-saving data.

SQL:  

Relational Algebra:${\mathsf{\pi }}_{\mathrm{SSN},\mathrm{First}\text{\_}\mathrm{Name},\mathrm{Last}\text{\_}\mathrm{Name}}\left({\mathsf{\sigma }}_{\mathrm{Patient}\text{\_}\mathrm{ID}=10016}\left(\mathrm{Patient}\right)\right)$

Workflow 3: General Provider (Standard Read)

A standard physician reads specific demographic and clinical data for an admitted patient.

SQL: 

Relational Algebra:${\mathsf{\pi }}_{\mathrm{First}\text{\_}\mathrm{Name},\mathrm{Last}\text{\_}\mathrm{Name},\mathrm{DOB},\mathrm{Blood}\text{\_}\mathrm{Type}}\left({\mathsf{\sigma }}_{\mathrm{Patient}\text{\_}\mathrm{ID}=10001}\left(\mathrm{Patient}\right)\right)$

Workflow 4: CDC / Public Health Analyst (Anonymized Trends)

The public health analyst queries regional disease trends exclusively for patients who opted into research.

SQL: 

Relational

Algebra:${\mathsf{\pi }}_{\mathrm{Month},\mathrm{State},\mathrm{Diagnosis}\text{\_}\mathrm{Description},\mathrm{Case}\text{\_}\mathrm{Count}}\left({\mathsf{\sigma }}_{\mathrm{ICD}\text{\_}\mathrm{Code}{=}^{\text{'}\mathrm{E}11.9\text{'}}}\left(\mathrm{View}\text{\_}\mathrm{Public}\text{\_}\mathrm{Health}\text{\_}\mathrm{Trends}\right)\right)$

Workflow 5: Insurance Auditor (Claim Validation)

The auditor queries a permitted view to validate a claim. The system automatically masks the SSN and filters for active patient consent.

SQL: 

Relational Algebra:

${\mathsf{\pi }}_{\mathrm{Masked}\text{\_}\mathrm{SSN},\mathrm{Provider}\text{\_}\mathrm{Name},\mathrm{Diagnosis}\text{\_}\mathrm{Description}}\left({\mathsf{\sigma }}_{\mathrm{Encounter}\text{\_}\mathrm{ID}=9001}\left(\mathrm{View}\text{\_}\mathrm{Insurance}\text{\_}\mathrm{Validation}\right)\right)$

Workflow 6: Patient (Self-Audit)

The patient securely checks their own medical history and who has accessed it.

SQL: 

Relational Algebra:${\mathsf{\pi }}_{\mathrm{Date}\text{\_}\mathrm{Time},\mathrm{Provider}\text{\_}\mathrm{Name},\mathrm{Diagnosis}\text{\_}\mathrm{Description}}\left({\mathsf{\sigma }}_{\mathrm{Patient}\text{\_}\mathrm{ID}=10001}\left(\mathrm{View}\text{\_}\mathrm{Patient}\text{\_}\mathrm{Own}\text{\_}\mathrm{Records}\right)\right)$

4. Results

4.1. Schema Deployment

The schema was successfully deployed to a MySQL Workbench 8.0 CE instance that is running the database called US_Centralized_Health_DB. There are no constraints violated on any of the fifteen tables. Mock data with five federated nodes, six health centers, twelve providers, twenty-two patients, twenty-five encounters and twenty-eight encounter diagnoses was inserted and confirmed using SELECT queries at the table level.

4.2. Data Population and Table Verification

Some dummy data were added to all tables. There are 5 nodes across Texas (Private), New York (Public), California (Private), Florida (Public), and Illinois (Private) all in the Federated_Node table. The Health_Center table links six centers to their corresponding nodes, ranging from the Texas Lone Star Private Clinic to the Illinois Community Health Clinic. The Diagnosis_Ref table has 15 ICD-10 codes with varying risk levels from Low to Critical. Twenty-two patients are present in the Patient table with AES encrypted SSN fields, meeting the encryption-at-rest requirement. Records for 12 physicians (in fields such as General Practice, Emergency Medicine, Psychiatry, Cardiology, Nephrology, Orthopedics, Pulmonology and Gastroenterology) were successfully added to the provider's records.

4.3. Query and Access Control Testing

All twelve test queries were run and returned to the desired result. Table 2 is a summary of each of the queries by objective and each of the six user roles by result.

All the inter-table linkages, audit mechanisms and consent filtering were successfully tested throughout the query. Multi-table joins of patient encounters with diagnoses was done correctly. The system automatically protected patients' privacy preferences, excluding patients who did not consent from public health aggregations and insurance validations. Break-glass emergency access events were simulated and recorded and a complete audit trail was obtained. Role based testing of access control also enabled to test access correctly: Roles were able to access the tables outside their scope of access (for example the emergency role accessing clinical notes, but not able to modify them), as shown in Figure 5.

Figure 5. Schema Deployment SQL Testing - Database Administrator test session (left) and Emergency Doctor user test session (right) showing success and failure test cases.

Figure 5. Schema Deployment SQL Testing - Database Administrator test session (left) and Emergency Doctor user test session (right) showing success and failure test cases.

5. Discussion

The CDHMS prototype illustrates the technical potential for a federated consented approach to a healthcare database, using a normalized relational approach. The twelve test queries successfully passed, ensuring that the six-layer schema can enforce access control, audit logging, patient consent preferences and cross-node identity resolution simultaneously. The findings address each of the five key failure points in the problem statement: patient identity fragmentation, interoperability issues, unchecked access to data, lack of an audit trail and lack of patient control over health records.

CDHMS is in a useful middle ground, compared to existing approaches. Monolithic EHR repositories, which are completely centralized systems, provide query efficiency, but reduce autonomy at the institutional level and create single points of failure (ONC, 2020). The fully decentralized peer-to-peer approaches maintain autonomy, but are not easily amenable to system-wide queries (National Academies of Sciences, Engineering, and Medicine, 2018). Both are accomplished by CDHMS's federated coordination layer, based on the MPI and Interoperability Mapping layer, which provides a common index without any loss of facilities' local databases.

One of the advantages of the design is the RBAC+ABAC hybrid. Role-based access (permission based on role) and attribute-based filtering (specialty-scoped and consent-scoped access at the row level) allow for fine-grained governance which can't be achieved via either model alone. The Break-Glass protocol also guarantees accessibility in case of emergency – without ever blocking – and guarantees to be accountable with tamper-evident logging.

Several factors on the other hand must be put in perspective of the current implementation. The Match_Confidence scores used in MPI linkage are also greatly simplified as they are static. Probabilistic matching engines (e.g., Fellegi-Sunter) or a trained supervised classification would be needed to cope with real world data quality problems (e.g., misspelled names in the production data, incorrect date of birth). Likewise, a security concern for ABAC at the application level as opposed to row-security at the database level will need to be addressed prior to clinical use. These are stakeholder negotiated compromises that are part of the prototype and outlined as high priority items on the future work road map.

The design's ethical implications could perhaps be the most notable, with a system of obtaining consent that prioritizes the patient's perspective. CDHMS is not a simple yes/no consent, as many EHR interoperability standards are, but rather granular consent at the data-type and grantee-role level—meaning that patients can consent to insurance access to encounter notes, while denying access to psychiatric notes. This reflects the growing consensus in the medical research community that granular consent systems for patients involving revocation, auditing, and meaningful agency is imperative to health data governance (U.S. HHS, 2023; ONC, 2020).

6. Conclusion

6.1. Limitations

The current system has restrictions on architecture, security and performance as compared to the production system. Architecturally, it emulates a federated system within a single instance of MySQL, but is not a true distributed system and does not implement a real probabilistic linkage algorithm for patient matching - it uses static mock data for patient matching. However, ABAC and consent filtering are implemented at the application level instead of being built-in in row level database security, making it a potential security threat (U.S. HHS, 2023). Encryption is now only available for SSNs, and clinical notes and other PHI are still in the clear, which doesn't provide a secure solution for HIPAA compliance. Lastly, the system has yet to be tested with a limited set of mock data and not been validated to the typical size of large medical centers (Li et al., 2021).

6.2. Future Work

The following are five directions that should be considered for future development.

• Implementation in a real multi-node-federated architecture with independent database servers with secure API gateway.
• Probabilistic matching engine (Fellegi-Sunter or supervised learning) integration for the further enhancement of MPI confidence scores.
• Enforcement of ABAC policies at the application level to stored-procedure gateways or row-level security policies in MySQL.
• All PHI columns encrypted with key management in accordance with the HIPAA Security Rule (U.S. HHS, 2023).
• Optimize the queries by load testing and checking query performance at national scale using millions of records (AHA, 2022).

6.3. Summary

The CDHMS project illustrates that it is possible to have a secure and scalable federated healthcare database in a normalized relational database. The 15-table design helps solve five of the biggest failure points in existing health care data systems: patient identity fragmentation, interoperability challenges, unchecked data access, poor audit trails and a lack of patient control over their health records. The challenges of building this system uncovered that the toughest design problems in health care data were not technical, but rather where technical and ethical issues converge. Architectural decisions must be made to balance the need for emergency access with privacy, or for automated code translation with clinical accuracy, and optimization does not always make the right decision. While the current version is an implemented solution on a single MySQL instance, it forms a principled approach towards a production deployment in multiple interdependent nodes in the healthcare industry, and it is a proof that federated interoperability has nothing to do with compromising local control or patient consent.