This project is a comprehensive relational MySQL database designed to centralize, organize, and manage CMS National Coverage Determinations (NCDs) and their associated rules, codes, and policy information. National Coverage Determinations are medical coverage policies issued by the Centers for Medicare & Medicaid Services (CMS) that define whether Medicare will cover a specific medical service, procedure, device, or diagnostic test nationwide.
The database consolidates information that is otherwise dispersed across multiple CMS resources into a single accessible and structured system. It enables efficient retrieval of CPT/HCPCS codes, ICD-10 diagnosis and procedure codes, CARC/RARC codes, transmittals, modifiers, MSN messages, rule descriptions, and other NCD-related policy data.
This project demonstrates healthcare database design, relational modeling, SQL querying, indexing, reporting, and healthcare informatics concepts.
National Coverage Determinations (NCDs) are publicly available through CMS; however, the information is distributed across multiple documents, spreadsheets, and web resources. Retrieving and consolidating the related codes and policy rules manually can be time-consuming and error-prone.
Current challenges include:
- Fragmented CMS policy resources
- Manual lookup of associated diagnosis and procedure codes
- Redundant data storage
- Difficulty generating customized reports
- Limited centralized access to NCD policy information
This project was developed to address these challenges by creating a centralized relational database system capable of storing and retrieving all NCD-related policy components efficiently.
The primary objectives of this project were:
- Develop a normalized relational database for CMS NCD policies
- Centralize NCD-related codes and policy information
- Reduce redundancy and streamline data retrieval
- Enable custom SQL reporting and querying
- Store CPT/HCPCS, ICD-10-CM, ICD-10-PCS, CARC/RARC, MSN, TOB, modifier, and transmittal data
- Create SQL views to simplify reporting
- Support future expansion and automation
The database enables users to:
- Retrieve all diagnosis and procedure codes linked to an NCD
- Search for NCDs using modifiers or CARC/RARC combinations
- Consolidate related policy information
- Generate NCD-specific reports
- Join transmittals and revision history data
- Retrieve rules and billing requirements
The database follows a relational design using MySQL 5.7 and consists of:
- 1 master table
- 11 related entity tables
- Multiple views and indexes
The central table is the NCD table, which stores the primary NCD policy information. Other tables reference the NCD_No field using foreign key relationships.
- One-to-many relationships
- Foreign key constraints
- Relational normalization
- SQL indexing
- Query optimization
- Reusable SQL views
- One NCD can have multiple CPT codes
- One NCD can have multiple ICD-10-CM codes
- One NCD can have multiple ICD-10-PCS codes
- One NCD can have multiple CARC/RARC combinations
- One NCD can have multiple transmittals and revisions
| Table Name | Description |
|---|---|
| NCD | Master table containing primary NCD information |
| CPT_NCD | CPT/HCPCS procedure codes linked to NCDs |
| ICD10CM_NCD | ICD-10-CM diagnosis codes linked to NCDs |
| ICD10PCS_NCD | ICD-10-PCS procedure codes linked to NCDs |
| CARC_RARC_NCD | CARC and RARC messages associated with NCDs |
| Modifier_NCD | Billing modifiers linked to NCDs |
| MSN_NCD | MSN messages associated with NCDs |
| Responsibility_NCD | Contractor responsibility categories |
| Rule_Desc_NCD | Rule descriptions and frequency limitations |
| TOB_NCD | Type of Bill codes and facility information |
| Transmittals_NCD | CMS transmittals and CR numbers |
| Revision_History_NCD_CR | NCD revision and change history |
- carc_rarc_ncd_view
- cpt_ncd_short_view
- icd10pcs_ncd_short_view
- transmittals_ncd_short_view
- ncd_full_info
This project was implemented using MySQL 5.7.
- Designed normalized relational schema
- Created tables with primary and foreign key constraints
- Imported tab-delimited CMS data files using:
LOAD DATA LOCAL INFILE- Created indexes for optimization
- Developed SQL queries and reporting views
- Validated table relationships and outputs
- MySQL 5.7
- SQL
- MySQL Command Line Client
- Table creation
- Data import
- Index creation
- SQL joins
- Aggregation queries
- View creation
- Reporting queries
SELECT
CPT_HCPCS_Code AS 'CPT Code',
CPT_HCPCS_Description AS 'CPT Description'
FROM CPT_NCD
WHERE NCD_No = '110.4';SELECT
ICD10CM_Code AS 'ICD-10-CM Code',
ICD10CM_Description AS 'ICD-10 CM Description'
FROM ICD10CM_NCD
WHERE NCD_No = '110.4';SELECT
ICD10_PCS AS 'ICD-10-PCS Code',
ICD10PCS_Desc AS 'ICD-10 PCS Description'
FROM ICD10PCS_NCD
WHERE NCD_No = '110.4';SELECT
CONCAT('NCD: ', NCD.NCD_No, '\n',
'NCD Title: ', NCD.NCD_Title, '\n',
'CR Nos: ', GROUP_CONCAT(Transmittals_NCD.CR_No ORDER BY Transmittals_NCD.CR_No SEPARATOR ', ')) AS Formatted_Output
FROM NCD
LEFT JOIN Transmittals_NCD
ON NCD.NCD_No = Transmittals_NCD.NCD_No
WHERE NCD.NCD_No = '110.4'
GROUP BY NCD.NCD_No;SELECT
rule.Responsibility,
rule.Rule_Description,
GROUP_CONCAT(DISTINCT tob.Type_of_Bill) AS TOB,
GROUP_CONCAT(DISTINCT m.Modifier) AS Modifier
FROM Rule_Desc_NCD rule
LEFT JOIN TOB_NCD tob ON rule.NCD_No = tob.NCD_No
LEFT JOIN Modifier_NCD m ON rule.NCD_No = m.NCD_No
WHERE rule.NCD_No = '110.4'
GROUP BY rule.Rule_Description;The project successfully:
- Built a normalized healthcare relational database
- Linked CPT, ICD-10-CM, ICD-10-PCS, MSN, TOB, CARC/RARC, modifiers, and transmittals to NCDs
- Enabled efficient SQL querying and reporting
- Reduced redundancy through relational modeling
- Centralized CMS NCD policy resources
- Improved accessibility and organization of policy information
The database supports advanced reporting and can serve as a foundation for future healthcare analytics and automation projects.
Potential future enhancements include:
- Development of a graphical user interface (GUI)
- Integration with live CMS updates
- Automation of quarterly maintenance reports
- API integration
- Support for Part A and Part B distinctions
- Enhanced search and filtering capabilities
- Integration with AI and NLP systems
- Dashboard and reporting visualization
| File | Description |
|---|---|
| cms_ncd_spreadsheet_database.sql | Full MySQL database export |
| NCD Database project By Divya Verma.pptx | Project presentation |
| README.md | Project documentation |
| LICENSE | MIT License |
| ER_diagram.png | Entity relationship diagram |
| query_results.png | Example SQL outputs |
Divya Verma
MS Health Informatics
Rutgers University
dv441@shp.rutgers.edu