Patient Identity and Demographics

Purpose: To master Epic’s comprehensive model for capturing who patients are—from unique identifiers to demographics, addresses, and provider relationships.

The Foundation of Everything

Before any diagnosis is made or medication prescribed, the healthcare system must answer a fundamental question: Who is this person? Epic’s patient data model provides a comprehensive answer, spanning multiple tables that capture identity, demographics, contact information, and care relationships.

Explore the core patient record

View SQL Query

SELECT 
    PAT_ID,
    PAT_MRN_ID,
    PAT_NAME,
    BIRTH_DATE,
    SEX_C_NAME
FROM PATIENT;

PAT_ID	PAT_MRN_ID	PAT_NAME	BIRTH_DATE	SEX_C_NAME
Z7004242	APL324672	MANDEL,JOSHUA C	10/26/1982 12:00:00 AM	Male

This single row represents the starting point for all clinical data. But Epic’s patient model extends far beyond these basics.

The Master Patient Table

The PATIENT table serves as the central hub for demographic data:

View the comprehensive patient master documentation

View SQL Query

SELECT documentation
FROM _metadata
WHERE table_name = 'PATIENT' 
  AND column_name IS NULL

documentation
The PATIENT table contains one record for each patient in your system. The data contained in each record consists of demographics, PCP and primary location information, registration information, and other information. Primary key: PAT_ID.

With 86 columns in the main table alone, Epic captures everything from basic demographics to care preferences. But that’s just the beginning.

Understanding Patient Identifiers

Epic uses two distinct identifier systems, each serving a critical purpose:

Compare internal and external patient identifiers

View SQL Query

SELECT 
    PAT_ID,
    PAT_MRN_ID,
    -- Extract the prefix patterns
    SUBSTR(PAT_ID, 1, 1) as pat_id_prefix,
    SUBSTR(PAT_MRN_ID, 1, 3) as mrn_prefix
FROM PATIENT;

PAT_ID	PAT_MRN_ID	pat_id_prefix	mrn_prefix
Z7004242	APL324672	Z	APL

PAT_ID (Patient ID):

• Internal database identifier
• Immutable—never changes
• Used for all table relationships
• Format: Alphanumeric with system prefix (e.g., “Z7004242”)

PAT_MRN_ID (Medical Record Number):

• Human-facing identifier
• Appears on wristbands and documents
• Can be facility-specific
• Format: Often includes location prefix (e.g., “APL324672”)

Prove PAT_ID is the universal foreign key

View SQL Query

SELECT 
    table_name,
    COUNT(*) as tables_with_pat_id
FROM _metadata
WHERE column_name = 'PAT_ID'
  AND table_name != 'PATIENT'
GROUP BY table_name
ORDER BY table_name
LIMIT 10

table_name	tables_with_pat_id
ACCT_GUAR_PAT_INFO	1
ALLERGY_FLAG	1
ANTICOAG_SELF_REGULATING	1
BENEFITS	1
CLAIMS_DERIVE_PAT_FLAGS	1
CLARITY_ADT	1
CL_REMIT	1
COMMUNITY_RESRC_REVIEWED	1
COVERAGE_MEMBER_LIST	1
EXT_DATA_LAST_DONE	1

Modeling Patient Names

Epic stores names in multiple formats to support different use cases:

Examine the multiple name storage formats

View SQL Query

SELECT 
    PAT_NAME,
    PAT_FIRST_NAME,
    PAT_MIDDLE_NAME,
    PAT_LAST_NAME
FROM PATIENT;

PAT_NAME	PAT_FIRST_NAME	PAT_MIDDLE_NAME	PAT_LAST_NAME
MANDEL,JOSHUA C	Joshua	C	Mandel

The system maintains:

• PAT_NAME: Formatted as “LASTNAME,FIRSTNAME MI”
• Component fields: Separate first, middle, and last names
• Alternative names: Stored in the PATIENT_ALIAS table

Explore patient aliases and alternative names

View SQL Query

SELECT 
    p.PAT_NAME as primary_name,
    a.LINE,
    a.ALIAS as alternative_name
FROM PATIENT p
LEFT JOIN PATIENT_ALIAS a ON p.PAT_ID = a.PAT_ID
ORDER BY a.LINE;

primary_name	LINE	alternative_name
MANDEL,JOSHUA C	1	MANDEL,JOSH

Race and Ethnicity: Different Models

Epic models race and ethnicity differently, reflecting federal reporting requirements:

Compare race and ethnicity storage patterns

View SQL Query

-- Race: Stored in separate table (supports multiple selections)
SELECT 
    'Race' as data_type,
    pr.LINE,
    pr.PATIENT_RACE_C_NAME as value
FROM PATIENT_RACE pr
WHERE pr.PAT_ID = 'Z7004242'

UNION ALL

-- Ethnicity: Stored directly in PATIENT table (single selection)
SELECT 
    'Ethnicity' as data_type,
    1 as line,
    p.ETHNIC_GROUP_C_NAME as value
FROM PATIENT p
WHERE p.PAT_ID = 'Z7004242'
ORDER BY data_type, line;

data_type	LINE	value
Ethnicity	1	Not Hispanic or Latino
Race	1	White

This design reflects the federal distinction:

• Race: Multiple selections allowed (separate table with LINE pattern)
• Ethnicity: Single selection (Hispanic/Latino or Not)

The Complex Address Model

Patient addresses demonstrate Epic’s sophisticated approach to contact information:

Understand the three-tier address system

View SQL Query

-- Permanent address components in PATIENT table
SELECT 
    'Permanent' as address_type,
    CITY,
    STATE_C_NAME,
    ZIP,
    COUNTY_C_NAME
FROM PATIENT
WHERE PAT_ID = 'Z7004242'

UNION ALL

-- Temporary address fields (if populated)
SELECT 
    'Temporary' as address_type,
    TMP_CITY,
    TMP_STATE_C_NAME,
    TMP_ZIP,
    TMP_COUNTY_C_NAME
FROM PATIENT
WHERE PAT_ID = 'Z7004242' 
  AND TMP_CITY IS NOT NULL;

address_type	CITY	STATE_C_NAME	ZIP	COUNTY_C_NAME
Permanent	MADISON	Wisconsin	REDACTED	DANE
Temporary

Street addresses require joining to PAT_ADDRESS:

Get complete address with street lines

View SQL Query

SELECT 
    p.CITY,
    p.STATE_C_NAME,
    p.ZIP,
    pa.LINE,
    pa.ADDRESS as street_line
FROM PATIENT p
LEFT JOIN PAT_ADDRESS pa ON p.PAT_ID = pa.PAT_ID
ORDER BY pa.LINE;

CITY	STATE_C_NAME	ZIP	LINE	street_line
MADISON	Wisconsin	REDACTED	1	REDACTED

Emergency Contacts and Relationships

Knowing who to contact in an emergency is critical. Epic manages this through the PAT_RELATIONSHIPS table.

View emergency contacts

View SQL Query

SELECT 
    pr.PAT_REL_NAME as Contact_Name,
    pr.PAT_REL_RELATION_C_NAME as Relationship,
    pr.PAT_REL_HOME_PHONE as Home_Phone,
    pr.PAT_REL_MOBILE_PHNE as Mobile_Phone,
    CASE WHEN pr.PAT_REL_NOTIFY_YN = 'Y' THEN 'Yes' ELSE 'No' END as Emergency_Contact
FROM PAT_RELATIONSHIPS pr
WHERE pr.PAT_ID = 'Z7004242'
ORDER BY pr.LINE;

Contact_Name	Relationship	Home_Phone	Mobile_Phone	Emergency_Contact
REDACTED,REDACTED	Spouse		REDACTED	No

Language Preferences

Epic tracks multiple language preferences for different contexts:

Explore granular language preferences

View SQL Query

SELECT 
    LANGUAGE_C_NAME as primary_language,
    LANG_CARE_C_NAME as care_language,
    LANG_WRIT_C_NAME as written_language,
    PREF_PCP_LANG_C_NAME as preferred_pcp_language
FROM PATIENT;

primary_language	care_language	written_language	preferred_pcp_language
English

This granularity supports scenarios where patients:

• Speak one language but prefer written materials in another
• Want providers who speak their native language
• Need interpreters for clinical care but not for written communication

The Overflow Table Pattern

Epic splits patient data across multiple tables to manage the 1000+ potential data elements:

Explore the continuation table pattern

View SQL Query

SELECT 
    'PATIENT' as table_name, COUNT(*) as column_count 
FROM pragma_table_info('PATIENT')
UNION ALL
SELECT 
    'PATIENT_2', COUNT(*) 
FROM pragma_table_info('PATIENT_2')
UNION ALL
SELECT 
    'PATIENT_3', COUNT(*) 
FROM pragma_table_info('PATIENT_3')
ORDER BY table_name;

table_name	column_count
PATIENT	83
PATIENT_2	55
PATIENT_3	65

Each overflow table serves specific purposes:

See specialized data in overflow tables

View SQL Query

-- PATIENT_2: Birth details and citizenship
SELECT 
    BIRTH_TM as birth_time,
    IS_ADOPTED_YN,
    CITIZENSHIP_C_NAME
FROM PATIENT_2
WHERE PAT_ID = 'Z7004242';

birth_time	IS_ADOPTED_YN	CITIZENSHIP_C_NAME
	N

Primary Care Provider Assignment

The patient’s medical home is tracked through PCP assignment:

Examine PCP assignment and preferences

View SQL Query

SELECT 
    p.CUR_PCP_PROV_ID,
    p.PREF_PCP_SEX_C_NAME,
    p.PREF_PCP_SPEC_C_NAME,
    p.PREF_PCP_LANG_C_NAME
FROM PATIENT p
WHERE p.PAT_ID = 'Z7004242';

CUR_PCP_PROV_ID	PREF_PCP_SEX_C_NAME	PREF_PCP_SPEC_C_NAME	PREF_PCP_LANG_C_NAME
144590

What’s Missing: Critical Gaps

This EHI export lacks several important elements found in full Epic systems:

1. Gender Identity

Confirm absence of gender identity fields

View SQL Query

SELECT column_name
FROM _metadata
WHERE table_name = 'PATIENT'
  AND (LOWER(column_name) LIKE '%gender%' 
       OR LOWER(column_name) LIKE '%sex%')
ORDER BY column_name;

column_name
PREF_PCP_SEX_C_NAME
SEX_C_NAME

Only biological sex is captured, not gender identity—a significant limitation for inclusive care.

2. Patient Merge History

Search for merge tracking tables

View SQL Query

SELECT 
    'No merge tracking tables found' as result,
    COUNT(*) as tables_checked
FROM sqlite_master 
WHERE type = 'table' 
  AND (name LIKE '%MERGE%' OR name LIKE '%DUPL%');

result	tables_checked
No merge tracking tables found	0

No merge history means you can’t trace when duplicate records were consolidated.

3. Test Patient Indicators

Check for test patient identification

View SQL Query

SELECT 
    p2.RECORD_TYPE_6_C_NAME
FROM PATIENT_2 p2
WHERE p2.PAT_ID = 'Z7004242';

RECORD_TYPE_6_C_NAME

Empty values suggest real patients; test patients would have specific record types.

---

Key Takeaways

• PATIENT is the master demographic table, extended by numbered overflow tables (PATIENT_2, PATIENT_3, etc.)
• PAT_ID is the immutable internal identifier; PAT_MRN_ID is the human-facing medical record number
• Names are stored both formatted (PAT_NAME) and componentized (first, middle, last)
• Race allows multiple selections (separate table); ethnicity is single selection (in PATIENT)
• Addresses use a hybrid model: city/state/zip in PATIENT, street lines in PAT_ADDRESS
• Emergency contacts are managed in the PAT_RELATIONSHIPS table.
• Language preferences are granular: spoken care, written materials, and provider preferences
• Critical gaps include gender identity, patient merge history, and comprehensive test patient flags

---