Pharmacy Management data model
Normalized (3NF) pharmacy-management model. The prescription order is separated from its individual fills/dispenses, drug products are keyed by NDC and RxNorm, claims follow the NCPDP Telecommunication adjudication shape, and cost-share is broken out into copay components. Controlled-substance flags (DEA schedule) and interaction/recall knowledge bases support dispensing safety. PHI is classified phi and masked.
Operational pharmacy-management data for retail, mail-order, and specialty dispensing: patients, prescribers, drug products (NDC/RxNorm), prescriptions, fills/dispenses, inventory lots, suppliers, insurance plans, NCPDP insurance claims, patient copays, drug-drug interactions, and FDA recalls. Patient and prescriber PHI is classified and masked for HIPAA. It is a normalized (3NF) relational model that mirrors the source system, suited to operational databases and to landing source data faithfully.
- 13
- tables
- 165
- columns
- 17
- relationships
- 12
- PII columns
Opening the template creates a project with the ERD, platform-native DDL, a dbt project and documentation already generated (recommended platform: PostgreSQL). Edit it in plain English or on the canvas, then ship it to any supported warehouse.
Subject areas
Design decisions
Prescription order separated from fills
A prescription authorizes a quantity with refills; each dispense is a distinct fill with its own NDC, quantity, and pricing. Modeling fills as children of the prescription preserves the true dispensing grain and supports adherence (days-supply) analytics.
NDC and RxNorm coding for drug products
Drugs are keyed by the 11-digit National Drug Code and cross-walked to RxNorm so substitutions, formulary lookups, and interaction checks are interoperable.
NCPDP claim adjudication structure
Pharmacy claims are adjudicated in real time via the NCPDP Telecommunication Standard (BIN/PCN routing, B1/B2 transactions, reject codes); modeling claims and copays accordingly matches the real reimbursement flow.
PHI and controlled-substance controls for HIPAA/DEA
Patient/prescriber identifiers and cardholder IDs are classified phi and masked, and DEA numbers/schedules are restricted and fully masked, supporting HIPAA and DEA controlled-substance recordkeeping.
Tables in the Pharmacy Management model
Every table with its grain, columns, types and keys. PK marks a primary key, BK a business key and PII a column classified as personal data, which gets a masking strategy by default.
Tables (13)
pharmacy
Grain: one row per pharmacy location
| Column | Type | Keys |
|---|---|---|
| pharmacy_key | bigint | PK |
| pharmacy_id | varchar | BK |
| ncpdp_id | varchar | |
| npi | varchar | |
| name | varchar | |
| pharmacy_type | varchar | |
| dea_number | varchar | |
| phone | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
patient
Grain: one row per patient
All PHI masked for HIPAA.
| Column | Type | Keys |
|---|---|---|
| patient_key | bigint | PK |
| patient_id | varchar | BK |
| ssn | varchar | PII |
| first_name | varchar | PII |
| last_name | varchar | PII |
| date_of_birth | date | PII |
| gender | varchar | |
| varchar | PII | |
| phone | varchar | PII |
| address_line1 | varchar | PII |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| preferred_language | varchar | |
| is_active | boolean |
prescriber
Grain: one row per prescribing clinician
| Column | Type | Keys |
|---|---|---|
| prescriber_key | bigint | PK |
| prescriber_id | varchar | BK |
| npi | varchar | |
| dea_number | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| specialty | varchar | |
| license_number | varchar | |
| license_state | varchar | |
| phone | varchar | PII |
| is_active | boolean |
supplier
Grain: one row per drug wholesaler / supplier
| Column | Type | Keys |
|---|---|---|
| supplier_key | bigint | PK |
| supplier_id | varchar | BK |
| name | varchar | |
| dea_number | varchar | |
| account_number | varchar | |
| contact_name | varchar | PII |
| phone | varchar | |
| varchar | ||
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
drug
Grain: one row per drug product (NDC)
| Column | Type | Keys |
|---|---|---|
| drug_key | bigint | PK |
| ndc | varchar | BK |
| rxnorm_code | varchar | |
| brand_name | varchar | |
| generic_name | varchar | |
| drug_class | varchar | |
| dosage_form | varchar | |
| strength | varchar | |
| strength_unit | varchar | |
| route | varchar | |
| dea_schedule | varchar | |
| is_controlled | boolean | |
| manufacturer | varchar | |
| awp | decimal | |
| is_active | boolean |
insurance_plan
Grain: one row per pharmacy benefit plan (BIN/PCN)
| Column | Type | Keys |
|---|---|---|
| insurance_plan_key | bigint | PK |
| plan_id | varchar | BK |
| payer_name | varchar | |
| bin | varchar | |
| pcn | varchar | |
| group_number | varchar | |
| plan_type | varchar | |
| formulary_id | varchar | |
| is_active | boolean |
prescription
Grain: one row per prescription (the order)
The prescription order; individual dispenses are fills.
| Column | Type | Keys |
|---|---|---|
| prescription_key | bigint | PK |
| patient_key | bigint | |
| prescriber_key | bigint | |
| drug_key | bigint | |
| pharmacy_key | bigint | |
| rx_number | varchar | BK |
| written_date | date | |
| sig | varchar | |
| quantity_written | decimal | |
| days_supply | smallint | |
| refills_authorized | smallint | |
| refills_remaining | smallint | |
| daw_code | varchar | |
| is_controlled | boolean | |
| origin | varchar | |
| status | varchar | |
| expiration_date | date |
fill
Grain: one row per dispensed fill of a prescription
Dispense events; one prescription has many fills.
| Column | Type | Keys |
|---|---|---|
| fill_key | bigint | PK |
| prescription_key | bigint | |
| pharmacy_key | bigint | |
| drug_key | bigint | |
| fill_number | smallint | |
| quantity_dispensed | decimal | |
| days_supply | smallint | |
| fill_date | date | |
| sold_date | date | |
| lot_number | varchar | |
| ingredient_cost | decimal | |
| dispensing_fee | decimal | |
| total_price | decimal | |
| pharmacist_initials | varchar | |
| status | varchar |
inventory
Grain: one row per drug lot held in pharmacy stock
| Column | Type | Keys |
|---|---|---|
| inventory_key | bigint | PK |
| pharmacy_key | bigint | |
| drug_key | bigint | |
| supplier_key | bigint | |
| lot_number | varchar | BK |
| quantity_on_hand | decimal | |
| reorder_point | decimal | |
| unit_cost | decimal | |
| expiration_date | date | |
| received_date | date | |
| storage_location | varchar | |
| status | varchar |
insurance_claim
Grain: one row per adjudicated pharmacy claim (NCPDP)
NCPDP Telecommunication claim adjudication.
| Column | Type | Keys |
|---|---|---|
| insurance_claim_key | bigint | PK |
| fill_key | bigint | |
| insurance_plan_key | bigint | |
| patient_key | bigint | |
| claim_id | varchar | BK |
| rx_reference_number | varchar | |
| transaction_code | varchar | |
| cardholder_id | varchar | PII |
| ingredient_cost_submitted | decimal | |
| dispensing_fee_submitted | decimal | |
| gross_amount_due | decimal | |
| amount_paid | decimal | |
| patient_pay_amount | decimal | |
| adjudication_status | varchar | |
| reject_code | varchar | |
| submitted_datetime | timestamp | |
| adjudicated_datetime | timestamp |
copay
Grain: one row per patient cost-share component on a claim
| Column | Type | Keys |
|---|---|---|
| copay_key | bigint | PK |
| insurance_claim_key | bigint | |
| patient_key | bigint | |
| cost_share_type | varchar | |
| amount | decimal | |
| deductible_applied | decimal | |
| coinsurance_amount | decimal | |
| benefit_stage | varchar | |
| payment_method | varchar | |
| collected | boolean | |
| collected_date | date |
drug_interaction
Grain: one row per known drug-drug interaction pair
Interaction knowledge base referenced at dispensing.
| Column | Type | Keys |
|---|---|---|
| drug_interaction_key | bigint | PK |
| drug_key | bigint | |
| interacting_drug_key | bigint | |
| severity | varchar | |
| mechanism | varchar | |
| clinical_effect | varchar | |
| management | varchar | |
| documentation_level | varchar | |
| reference_source | varchar |
drug_recall
Grain: one row per FDA drug recall affecting a lot
| Column | Type | Keys |
|---|---|---|
| drug_recall_key | bigint | PK |
| drug_key | bigint | |
| recall_number | varchar | BK |
| affected_lot_number | varchar | |
| classification | varchar | |
| reason | varchar | |
| status | varchar | |
| recall_initiation_date | date |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- prescription.patient_key → patient (many-to-one)
- prescription.prescriber_key → prescriber (many-to-one)
- prescription.drug_key → drug (many-to-one)
- prescription.pharmacy_key → pharmacy (many-to-one)
- fill.prescription_key → prescription (many-to-one)
- fill.pharmacy_key → pharmacy (many-to-one)
- fill.drug_key → drug (many-to-one)
- inventory.pharmacy_key → pharmacy (many-to-one)
- inventory.drug_key → drug (many-to-one)
- inventory.supplier_key → supplier (many-to-one)
- insurance_claim.fill_key → fill (many-to-one)
- insurance_claim.insurance_plan_key → insurance_plan (many-to-one)
- insurance_claim.patient_key → patient (many-to-one)
- copay.insurance_claim_key → insurance_claim (many-to-one)
- copay.patient_key → patient (many-to-one)
- drug_interaction.drug_key → drug (many-to-one)
- drug_recall.drug_key → drug (many-to-one)
Related templates
All templates →From requirements to AI-ready context, in one platform.
Describe your business in a conversation, start from a template or connect your own database. Your first model is ready in minutes.