Payer Systems

Medicare vs Medicaid

Complete comparison of Medicare and Medicaid payer systems. Learn database design for eligibility, claims processing, dual eligible patients, and state-federal coordination.

Quick Comparison

Feature	Medicare	Medicaid
Administration	Federal (CMS)	Federal + State
Eligibility	Age 65+ or disabled	Low income
Coverage	Nationwide uniform	Varies by state
Cost Sharing	Premiums, deductibles, copays	Minimal or none
Enrollment	65+ automatic, others apply	Income verification required
Beneficiaries	~65 million	~85 million

Medicare Parts Explained

Part A - Hospital Insurance

Inpatient hospital, skilled nursing, hospice, home health

Premium-free for most (paid via payroll taxes)

Part B - Medical Insurance

Doctor visits, outpatient care, preventive services

Standard premium ~$174/month (2024)

Part C - Medicare Advantage

Private plans replacing A + B, often includes D

~50% of Medicare beneficiaries enrolled

Part D - Prescription Drug Coverage

Pharmacy benefits through private plans

Optional, premium varies by plan

Payer Database Schema

-- Payer Master Table
CREATE TABLE payer (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  payer_name            VARCHAR(255) NOT NULL,
  payer_type            VARCHAR(50),             -- medicare, medicaid, commercial
  
  -- Payer Identifiers
  payer_id              VARCHAR(10) UNIQUE,      -- CMS assigns 5-digit ID
  naic_code             VARCHAR(10),             -- For commercial payers
  
  -- Contact
  address               VARCHAR(255),
  city                  VARCHAR(100),
  state                 VARCHAR(2),
  zip_code              VARCHAR(10),
  phone                 VARCHAR(20),
  
  -- EDI
  edi_payer_id          VARCHAR(20),             -- For 837/835 transactions
  
  -- Status
  active                BOOLEAN DEFAULT TRUE,
  effective_date        DATE,
  termination_date      DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

-- Medicare Beneficiary
CREATE TABLE medicare_beneficiary (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Medicare Identifiers
  hicn                  VARCHAR(12),             -- Old Health Insurance Claim Number
  mbi                   VARCHAR(11) UNIQUE,      -- New Medicare Beneficiary Identifier
  
  -- Enrollment
  part_a_effective      DATE,
  part_b_effective      DATE,
  part_c_plan_id        VARCHAR(50),             -- Medicare Advantage plan
  part_d_plan_id        VARCHAR(50),             -- Drug plan
  
  -- Status
  enrollment_status     VARCHAR(50),
  enrollment_reason     VARCHAR(100),            -- Age, disability, ESRD
  
  -- Coordination of Benefits
  is_dual_eligible      BOOLEAN DEFAULT FALSE,
  medicaid_id           VARCHAR(50),
  
  created_at            TIMESTAMP DEFAULT NOW(),
  updated_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_medicare_mbi ON medicare_beneficiary(mbi);
CREATE INDEX idx_medicare_patient ON medicare_beneficiary(patient_id);

-- Medicaid Beneficiary
CREATE TABLE medicaid_beneficiary (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Medicaid Identifiers (varies by state)
  medicaid_id           VARCHAR(50) UNIQUE NOT NULL,
  state                 VARCHAR(2) NOT NULL,
  
  -- Eligibility
  eligibility_category  VARCHAR(100),            -- Children, pregnant, disabled, aged
  income_level          DECIMAL(10,2),
  household_size        INTEGER,
  
  -- Dates
  effective_date        DATE NOT NULL,
  termination_date      DATE,
  redetermination_date  DATE,                    -- Annual renewal
  
  -- Managed Care
  mco_plan_id           VARCHAR(50),             -- Medicaid MCO
  
  -- Dual Eligible
  is_dual_eligible      BOOLEAN DEFAULT FALSE,
  medicare_mbi          VARCHAR(11),
  
  -- Status
  status                VARCHAR(50),
  
  created_at            TIMESTAMP DEFAULT NOW(),
  updated_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_medicaid_id ON medicaid_beneficiary(medicaid_id);
CREATE INDEX idx_medicaid_patient ON medicaid_beneficiary(patient_id);
CREATE INDEX idx_medicaid_state ON medicaid_beneficiary(state);

-- Insurance Coverage (unified view)
CREATE TABLE insurance_coverage (
  id                    UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  patient_id            UUID NOT NULL,
  
  -- Payer
  payer_id              UUID REFERENCES payer(id),
  payer_type            VARCHAR(50),             -- medicare, medicaid, commercial
  
  -- Policy Details
  policy_number         VARCHAR(100),
  group_number          VARCHAR(100),
  plan_name             VARCHAR(255),
  
  -- Coverage Period
  effective_date        DATE NOT NULL,
  termination_date      DATE,
  
  -- Priority (for coordination of benefits)
  coverage_order        INTEGER,                 -- 1=primary, 2=secondary, 3=tertiary
  
  -- Specific IDs
  medicare_mbi          VARCHAR(11),
  medicaid_id           VARCHAR(50),
  
  -- Status
  verification_status   VARCHAR(50),
  last_verified         DATE,
  
  created_at            TIMESTAMP DEFAULT NOW()
);

CREATE INDEX idx_coverage_patient ON insurance_coverage(patient_id);
CREATE INDEX idx_coverage_payer ON insurance_coverage(payer_id);

Dual Eligible Patients

What are Dual Eligible Patients?

Individuals who qualify for both Medicare and Medicaid. Medicare is primary payer, Medicaid covers Medicare cost-sharing and additional services. Critical for claims coordination.

Claims Processing for Dual Eligible

1.Submit to Medicare first - Medicare processes as primary

2.Receive Medicare payment - Get Explanation of Benefits (EOB)

3.Submit to Medicaid - Include Medicare EOB with claim

4.Medicaid pays balance - Covers copays, deductibles, coinsurance

SQL Query Examples

Find Dual Eligible Patients

SELECT 
  p.mrn,
  p.first_name || ' ' || p.last_name as patient_name,
  mb.mbi as medicare_id,
  mcb.medicaid_id,
  mcb.state as medicaid_state
FROM patient p
JOIN medicare_beneficiary mb ON p.id = mb.patient_id
JOIN medicaid_beneficiary mcb ON p.id = mcb.patient_id
WHERE mb.is_dual_eligible = TRUE
  AND mcb.is_dual_eligible = TRUE
  AND mcb.status = 'active'
ORDER BY p.last_name;

Check Coverage for Claim Submission

SELECT 
  ic.coverage_order,
  p.payer_name,
  ic.payer_type,
  CASE 
    WHEN ic.payer_type = 'medicare' THEN ic.medicare_mbi
    WHEN ic.payer_type = 'medicaid' THEN ic.medicaid_id
    ELSE ic.policy_number
  END as identifier
FROM insurance_coverage ic
JOIN payer p ON ic.payer_id = p.id
WHERE ic.patient_id = 'patient-uuid'
  AND ic.effective_date <= CURRENT_DATE
  AND (ic.termination_date IS NULL OR ic.termination_date >= CURRENT_DATE)
ORDER BY ic.coverage_order;

Eligibility Verification

Real-Time Eligibility Checks

Medicare:

Use MBI to verify through CMS HPMS or commercial clearinghouses. Response includes Part A/B/C/D coverage.

Medicaid:

Each state maintains eligibility system. Use state-specific Medicaid ID. Some states use real-time portals, others batch files.

Best Practice:

Verify eligibility at every encounter. Store verification response with timestamp and transaction ID for audit trail.

Related Healthcare Guides

Medical Billing →Claims Adjudication →Prior Authorization →Healthcare Glossary →

mdatool.