logo
ExamplesEditorPricingDocs
37.1kSign in

Health insurance claims processing database schema

Payer-side claims processing in SQL Server: plans with benefit rules, policies covering members and dependents, provider network tiers, claims with service lines, per-line adjudication, coded denials, appeals, explanations of benefits and remittance to providers.

Insurance Claims ER diagram

Health insurance claims processing database schema n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 n1 plans plan_idBIGINT plan_codeNVARCHAR plan_nameNVARCHAR plan_typeNVARCHAR plan_yearSMALLINT individual_deduct…BIGINT family_deductible…BIGINT out_of_pocket_max…BIGINT currency_codeCHAR effective_fromDATE effective_toDATE is_activeBIT benefit_rules benefit_rule_idBIGINT plan_idBIGINT service_categoryNVARCHAR place_of_serviceNVARCHAR network_tierNVARCHAR copay_minorBIGINT coinsurance_perce…DECIMAL applies_to_deduct…BIT requires_prior_au…BIT annual_visit_limitSMALLINT effective_fromDATE effective_toDATE policies policy_idBIGINT plan_idBIGINT policy_numberNVARCHAR group_numberNVARCHAR group_nameNVARCHAR subscriber_member…BIGINT effective_fromDATE effective_toDATE statusNVARCHAR premium_minorBIGINT currency_codeCHAR members member_idBIGINT policy_idBIGINT member_numberNVARCHAR relationshipNVARCHAR given_nameNVARCHAR family_nameNVARCHAR date_of_birthDATE sexCHAR coverage_startDATE coverage_endDATE is_activeBIT providers provider_idBIGINT npiNVARCHAR provider_nameNVARCHAR provider_typeNVARCHAR tax_idNVARCHAR specialtyNVARCHAR address_lineNVARCHAR is_activeBIT provider_network_st… network_status_idBIGINT provider_idBIGINT plan_idBIGINT network_tierNVARCHAR contract_codeNVARCHAR fee_schedule_codeNVARCHAR effective_fromDATE effective_toDATE claims claim_idBIGINT claim_numberNVARCHAR member_idBIGINT policy_idBIGINT billing_provider_…BIGINT rendering_provide…BIGINT claim_typeNVARCHAR network_tierNVARCHAR received_atDATETIME2 service_from_dateDATE service_to_dateDATE total_billed_minorBIGINT currency_codeCHAR statusNVARCHAR adjusted_from_cla…BIGINT payment_idBIGINT claim_lines claim_line_idBIGINT claim_idBIGINT line_numberSMALLINT service_dateDATE procedure_codeNVARCHAR modifier_codesNVARCHAR diagnosis_codeNVARCHAR place_of_serviceNVARCHAR unitsDECIMAL billed_amount_min…BIGINT service_categoryNVARCHAR adjudications adjudication_idBIGINT claim_line_idBIGINT adjudication_roundSMALLINT benefit_rule_idBIGINT network_tier_appl…NVARCHAR allowed_amount_mi…BIGINT deductible_applie…BIGINT copay_minorBIGINT coinsurance_minorBIGINT plan_paid_minorBIGINT patient_responsib…BIGINT currency_codeCHAR is_deniedBIT pricing_sourceNVARCHAR adjudicated_atDATETIME2 denials denial_idBIGINT adjudication_idBIGINT reason_codeNVARCHAR remark_codeNVARCHAR denial_categoryNVARCHAR descriptionNVARCHAR is_appealableBIT denied_amount_min…BIGINT currency_codeCHAR denied_atDATETIME2 appeals appeal_idBIGINT claim_idBIGINT denial_idBIGINT appeal_levelNVARCHAR submitted_byNVARCHAR submitted_atDATETIME2 reasonNVARCHAR statusNVARCHAR decided_atDATETIME2 decision_notesNVARCHAR overturned_amount…BIGINT currency_codeCHAR explanations_of_ben… eob_idBIGINT claim_idBIGINT member_idBIGINT eob_numberNVARCHAR issued_atDATETIME2 total_billed_minorBIGINT total_allowed_min…BIGINT total_plan_paid_m…BIGINT total_patient_res…BIGINT currency_codeCHAR delivery_channelNVARCHAR document_uriNVARCHAR is_supersededBIT provider_payments payment_idBIGINT provider_idBIGINT payment_numberNVARCHAR payment_methodNVARCHAR trace_numberNVARCHAR total_amount_minorBIGINT currency_codeCHAR paid_atDATETIME2 statusNVARCHAR remittance_uriNVARCHAR

Related database schemas

PostgreSQL
Healthcare

Pharmacy dispensing & inventory database schema

How a dispensing pharmacy models its data: a drug catalogue by strength and form, stock held as batches with expiry dates picked first-expired-first-out, dispense events, interaction and allergy checks, insurance claims and a controlled-drugs register.

MySQL
Healthcare

Clinic appointment scheduling database schema

Outpatient scheduling in MySQL: recurring availability templates that generate slots, appointments that consume exactly one slot, waitlists that backfill cancellations, two-way reminders, referrals, and a dated no-show policy.

PostgreSQL
Healthcare

Hospital / EHR database schema

The core of an electronic health record: patients and providers, appointments versus encounters, coded conditions and observations, prescriptions against a drug catalogue, allergies and insurance coverage over time.

PostgreSQL
Healthcare

Strava-style fitness tracking database schema

How a fitness tracking app models its data: activities with compressed sensor streams instead of one row per second, segments and segment efforts with materialised leaderboards, personal records, kudos and comments, clubs, gear mileage and training load.