Modeling Categories: The _C_NAME Pattern

Purpose: To understand Epic’s denormalized approach to categorical data in the EHI export.

The Missing Lookup Tables

In a traditional database, you’d expect to find lookup tables—small tables mapping codes to descriptions. You might see appointment_status with rows like (1, 'Scheduled'), (2, 'Completed'), (3, 'Canceled'). But search Epic’s EHI export and you’ll find something surprising:

Prove that ZC_ lookup tables don't exist

View SQL Query

-- Traditional Epic systems use ZC_ tables for lookups
SELECT 
    COUNT(*) as zc_tables_found,
    CASE 
        WHEN COUNT(*) = 0 THEN '❌ No ZC_ lookup tables in EHI export!'
        ELSE 'Found lookup tables'
    END as finding
FROM sqlite_master 
WHERE type = 'table' 
  AND name LIKE 'ZC_%';

zc_tables_found	finding
0	❌ No ZC_ lookup tables in EHI export!

Instead, Epic uses the _C_NAME pattern—storing human-readable category names directly in each table.

The Scale of Denormalization

Discover the prevalence of the _C_NAME pattern

View SQL Query

SELECT 
    COUNT(DISTINCT column_name) as category_columns,
    COUNT(DISTINCT table_name) as tables_with_categories
FROM _metadata
WHERE column_name LIKE '%_C_NAME%';

category_columns	tables_with_categories
1664	268

With 1,695 category columns across the database, this pattern is fundamental to Epic’s data model.

How Categories Work

Let’s examine a typical category column:

See category values in practice

View SQL Query

-- First, see the distinct values
SELECT 
    APPT_STATUS_C_NAME as status,
    COUNT(*) as encounter_count
FROM pat_enc 
WHERE APPT_STATUS_C_NAME IS NOT NULL
GROUP BY APPT_STATUS_C_NAME
ORDER BY encounter_count DESC;

status	encounter_count
	94
Completed	13
Canceled	3
Scheduled	1

Notice the documentation reveals the hidden numeric codes: “1 - Scheduled, 2 - Completed, 3 - Canceled”. These codes exist in Chronicles but are translated to text for the export.

The Hidden Code System

Epic’s documentation often exposes the underlying numeric categories:

Find columns where documentation reveals numeric codes

View SQL Query

SELECT 
    table_name,
    column_name,
    SUBSTR(documentation, 1, 100) || '...' as shows_numeric_codes
FROM _metadata
WHERE column_name LIKE '%_C_NAME'
  AND documentation LIKE '%1 -%'
LIMIT 5;

table_name	column_name	shows_numeric_codes
ARPB_CHG_ENTRY_DX	DX_QUALIFIER_C_NAME	Qualifier for the diagnosis on this line. Indicates if diagnosis is: 1 - Active 2 - Acute 3 - Chro...
HSP_TRANSACTIONS	CE_HM_OFF_TXTYP_C_NAME	The type of transaction that took place. This will have a value of null unless the transaction's de...
ORDER_PROC_5	ACTV_EXCLUDE_FROM_CDS_REASON_C_NAME	The Exclude From Decision Support reason for the order. It will be either 1 - Unsuccessful Attempt r...

This proves that Chronicles uses numeric categories internally, but the EHI export provides only the human-readable names.

Why This Matters for Queries

The denormalized approach has significant implications:

1. Text Matching Instead of Codes

-- You must use text values, not numeric codes
SELECT * FROM pat_enc
WHERE APPT_STATUS_C_NAME = 'Completed';  -- ✓ Correct

SELECT * FROM pat_enc
WHERE APPT_STATUS_C_NAME = '2';  -- ✗ Won't work

2. Case and Whitespace Sensitivity

Check for case variations in category values

View SQL Query

-- Are category values consistent?
SELECT 
    APPT_STATUS_C_NAME,
    UPPER(APPT_STATUS_C_NAME) as uppercase,
    LOWER(APPT_STATUS_C_NAME) as lowercase,
    COUNT(*) as occurrences
FROM pat_enc
WHERE APPT_STATUS_C_NAME IS NOT NULL
GROUP BY APPT_STATUS_C_NAME
ORDER BY APPT_STATUS_C_NAME;

APPT_STATUS_C_NAME	uppercase	lowercase	occurrences
			94
Canceled	CANCELED	canceled	3
Completed	COMPLETED	completed	13
Scheduled	SCHEDULED	scheduled	1

3. No Referential Integrity Without lookup tables, there’s no database-enforced consistency:

Demonstrate lack of referential constraints

View SQL Query

-- Find all unique statuses across different status columns
WITH all_statuses AS (
    SELECT DISTINCT 'APPT_STATUS' as status_type, APPT_STATUS_C_NAME as status_value 
    FROM pat_enc WHERE APPT_STATUS_C_NAME IS NOT NULL
    
    UNION
    
    SELECT DISTINCT 'PROBLEM_STATUS', PROBLEM_STATUS_C_NAME 
    FROM PROBLEM_LIST WHERE PROBLEM_STATUS_C_NAME IS NOT NULL
)
SELECT * FROM all_statuses
ORDER BY status_type, status_value;

status_type	status_value
APPT_STATUS
APPT_STATUS	Canceled
APPT_STATUS	Completed
APPT_STATUS	Scheduled
PROBLEM_STATUS	Active

Common Category Types

Categories follow predictable patterns based on their suffix:

Analyze category naming patterns

View SQL Query

WITH category_types AS (
    SELECT 
        SUBSTR(column_name, 1, INSTR(column_name, '_C_NAME') - 1) as base_name,
        column_name,
        table_name
    FROM _metadata
    WHERE column_name LIKE '%_C_NAME%'
)
SELECT 
    base_name,
    COUNT(DISTINCT table_name) as table_count,
    GROUP_CONCAT(DISTINCT table_name) as appears_in_tables
FROM category_types
GROUP BY base_name
HAVING COUNT(DISTINCT table_name) > 3
ORDER BY table_count DESC
LIMIT 10;

base_name	table_count	appears_in_tables
RECORD_STATUS	9	ARPB_TRANSACTIONS2,BDC_INFO,BENEFITS,INVOICE,IP_DATA_STORE,MDL_MD_PRBLM_LIST,MYC_MESG,PAT_RELATIONSHIP_LIST,TIMEOUT
	8	BDC_INFO,CLARITY_EAP,CLARITY_LOC,CLARITY_MEDICATION,CLARITY_PRC,COVERAGE,DOCS_RCVD_ALG_REAC,PATIENT_5
STATUS	6	COVERAGE_2,EPISODE,EPISODE_ALL,MED_CVG_RESPONSE_RSLT,PT_GOALS_UPDATES,TIMEOUT_ANSWERS
DX_QUALIFIER	5	ARPB_CHG_ENTRY_DX,ORDER_DX_MED,ORDER_DX_PROC,PAT_ENC_DX,TX_DIAG
COUNTRY	4	ACCOUNT,PATIENT,PAT_ADDR_CHNG_HX,PAT_RELATIONSHIP_LIST
ADMISSION_SOURCE	4	CLAIM_INFO,HSP_ACCOUNT,HSP_ACCT_CLAIM_HAR,REFERRAL_5

The _C Without _NAME Mystery

Occasionally you’ll find columns ending in _C without the _NAME suffix:

Investigate _C columns without _NAME

View SQL Query

SELECT 
    column_name,
    table_name,
    documentation
FROM _metadata
WHERE column_name LIKE '%_C'
  AND column_name NOT IN (
      SELECT REPLACE(column_name, '_NAME', '') 
      FROM _metadata 
      WHERE column_name LIKE '%_C_NAME%'
  )
LIMIT 10;

column_name	table_name	documentation
BILLING_CYCLE_C	ACCOUNT	The category value associated with the billing cycle to which the guarantor belongs.
EXP_REIMB_DYNAMIC	ARPB_TX_MODERATE	The expected reimbursement will be updated whenever reimbursement is calculated. This amount cannot exceed the charge amount.
BC_HX_AUX_PROC	ARPB_TX_STMCLAIMHX	Claim Auxiliary Procedure. This item is only used by claims. This is populated when claim bundling grouping rules are used and there are procedures that are left over from the claim. This item is a semicolon delimited list of extra procedures. For example, if the bundling rule is set up to bundle and 99212 and a 99213 and there are two 99212s and one 99213, then the 99212 procedure identifier would appear in this column.
WRITE_OFF_AMT_CALC	BDC_INFO	The write off amount entered by the user. If that is blank, this is the write off amount calculated by the system.
OPH_SURG_HX_LOC	CATARACT_PLANNING_INFO	Stores the description of the location where a surgery was performed if done historically and not linked to a case.
WK_COMP_INJ_DESC	CLAIM_INFO	Workers' Comp injury description.
PRINCIPLE_PX_DESC	CLAIM_INFO2	Principal International Classification of Diseases (ICD) procedure description.
TREATMENT_DESC	CLAIM_INFO2	The description of the further treatment that should be given to the patient.
PRODUCT_DESC	CLAIM_INFO3	A description of the product involved in the injury.
INJURY_DESC	CLM_INJURY_DESC	The claim injury description.

These 66 columns are remnants where the numeric code leaked through, but they’re exceptions to the rule.

Working with Categories

Best practices for the _C_NAME pattern:

1. Build Category Inventories

Create a reference list of valid values

View SQL Query

-- Build a reference of all appointment statuses
SELECT DISTINCT 
    APPT_STATUS_C_NAME as status_name,
    COUNT(*) as usage_count
FROM PAT_ENC
WHERE APPT_STATUS_C_NAME IS NOT NULL
GROUP BY APPT_STATUS_C_NAME
ORDER BY usage_count DESC;

status_name	usage_count
	94
Completed	13
Canceled	3
Scheduled	1

2. Handle NULL Values

-- Categories can be NULL - always check
SELECT COUNT(*) as total,
       COUNT(APPT_STATUS_C_NAME) as with_status,
       COUNT(*) - COUNT(APPT_STATUS_C_NAME) as missing_status
FROM pat_enc;

3. Use LIKE for Flexible Matching

-- When exact values might vary
SELECT * FROM diagnoses
WHERE DX_CATEGORY_C_NAME LIKE '%Emergency%';

The Trade-offs

This denormalized approach has pros and cons:

Advantages:

• Human-readable without joins
• Self-contained tables
• Simpler queries for basic reporting

Disadvantages:

• No enforced consistency
• Text matching is less efficient than numeric comparisons
• Category changes require updating all rows
• Potential for typos and variations

---

Key Takeaways

• Epic stores category names as text (_C_NAME), not numeric codes
• 1,695 category columns make this the most common pattern after LINE
• No ZC_ lookup tables exist in the EHI export—everything is denormalized
• Documentation often reveals the hidden numeric codes from Chronicles
• Always use exact text matching for category values
• This pattern trades referential integrity for simplicity and readability
• Build your own category inventories when consistency matters