Dental Practice data model
Normalized (3NF) dental-office model. Procedures are coded with the ADA Code on Dental Procedures and Nomenclature (CDT) held as a lookup, tooth positions follow the Universal Numbering System, and the revenue cycle is split into claims, claim lines, and payments. Treatment plans link proposed to performed work. PHI is classified phi and masked.
Operational dental-practice-management data: patients, dentists and hygienists, appointments, treatment plans, performed procedures (ADA CDT) located by tooth and surface, per-tooth charting (odontogram), insurance plans and coverage, dental claims (ADA J430), claim lines, and payments. Patient 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
- 151
- columns
- 25
- 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
ADA CDT coding held as a reference lookup
Dental procedures are billed with the ADA Code on Dental Procedures and Nomenclature (CDT); holding the codes as a static lookup keeps treatments and claim lines consistent and supports fee schedules.
Per-tooth/surface granularity
Dental work is recorded per tooth and surface using the Universal Numbering System; charting and treatments capture tooth number, surface, and quadrant so claims carry the detail payers require.
Revenue cycle split into claims, lines, and payments
Claims are submitted on the ADA J430 form with per-procedure lines and adjudicated independently of patient/insurer payments; splitting them preserves the true billing grain and supports A/R aging.
PHI classification and masking for HIPAA
Patient and guarantor identifiers and subscriber IDs are classified phi and masked (SSN fully) so the governance report enforces HIPAA controls on a covered dental provider.
Tables in the Dental Practice 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 (12)
practice
Grain: one row per dental practice / office location
| Column | Type | Keys |
|---|---|---|
| practice_key | bigint | PK |
| practice_id | varchar | BK |
| npi | varchar | |
| tax_id | varchar | |
| name | varchar | |
| phone | varchar | |
| address_line1 | varchar | |
| city | varchar | |
| state | varchar | |
| postal_code | varchar | |
| is_active | boolean |
dentist
Grain: one row per dentist / hygienist
| Column | Type | Keys |
|---|---|---|
| dentist_key | bigint | PK |
| practice_key | bigint | |
| dentist_id | varchar | BK |
| npi | varchar | |
| first_name | varchar | PII |
| last_name | varchar | PII |
| role | varchar | |
| specialty | varchar | |
| license_number | varchar | |
| license_state | varchar | |
| varchar | PII | |
| is_active | boolean |
patient
Grain: one row per dental patient
All PHI masked for HIPAA.
| Column | Type | Keys |
|---|---|---|
| patient_key | bigint | PK |
| practice_key | bigint | |
| patient_id | varchar | BK |
| chart_number | varchar | |
| 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 | |
| guarantor_name | varchar | PII |
| guarantor_relationship | varchar | |
| preferred_language | varchar | |
| is_active | boolean |
insurance_plan
Grain: one row per dental insurance plan
| Column | Type | Keys |
|---|---|---|
| insurance_plan_key | bigint | PK |
| plan_id | varchar | BK |
| carrier_name | varchar | |
| plan_name | varchar | |
| group_number | varchar | |
| plan_type | varchar | |
| annual_maximum | decimal | |
| deductible_individual | decimal | |
| preventive_coverage_pct | decimal | |
| basic_coverage_pct | decimal | |
| major_coverage_pct | decimal | |
| is_active | boolean |
patient_coverage
Grain: one row per patient's enrollment in a plan
| Column | Type | Keys |
|---|---|---|
| patient_coverage_key | bigint | PK |
| patient_key | bigint | |
| insurance_plan_key | bigint | |
| subscriber_id | varchar | PII |
| coverage_order | varchar | |
| relationship_to_subscriber | varchar | |
| effective_date | date | |
| termination_date | date | |
| is_active | boolean |
appointment
Grain: one row per scheduled appointment
| Column | Type | Keys |
|---|---|---|
| appointment_key | bigint | PK |
| patient_key | bigint | |
| dentist_key | bigint | |
| practice_key | bigint | |
| appointment_id | varchar | BK |
| appointment_type | varchar | |
| operatory | varchar | |
| scheduled_start | timestamp | |
| scheduled_end | timestamp | |
| checked_in_at | timestamp | |
| status | varchar | |
| reason | varchar |
treatment_plan
Grain: one row per proposed treatment plan
| Column | Type | Keys |
|---|---|---|
| treatment_plan_key | bigint | PK |
| patient_key | bigint | |
| dentist_key | bigint | |
| plan_id | varchar | BK |
| status | varchar | |
| estimated_total | decimal | |
| estimated_insurance_portion | decimal | |
| estimated_patient_portion | decimal | |
| created_date | date | |
| accepted_date | date |
treatment
Grain: one row per performed or planned procedure (ADA CDT)
Procedures coded in ADA CDT and located by tooth/surface.
| Column | Type | Keys |
|---|---|---|
| treatment_key | bigint | PK |
| patient_key | bigint | |
| dentist_key | bigint | |
| procedure_code_key | bigint | |
| appointment_key | bigint | |
| treatment_plan_key | bigint | |
| tooth_number | varchar | |
| tooth_surface | varchar | |
| quadrant | varchar | |
| status | varchar | |
| fee_charged | decimal | |
| performed_date | date | |
| notes | varchar |
tooth_chart
Grain: one row per tooth condition entry on a patient's chart
Per-tooth dental charting (odontogram).
| Column | Type | Keys |
|---|---|---|
| tooth_chart_key | bigint | PK |
| patient_key | bigint | |
| examined_by_dentist_key | bigint | |
| tooth_number | varchar | |
| tooth_surface | varchar | |
| condition | varchar | |
| status | varchar | |
| charted_date | date | |
| notes | varchar |
claim
Grain: one row per dental insurance claim (ADA J430)
ADA dental claim form (J430) submission.
| Column | Type | Keys |
|---|---|---|
| claim_key | bigint | PK |
| patient_key | bigint | |
| insurance_plan_key | bigint | |
| practice_key | bigint | |
| claim_id | varchar | BK |
| claim_type | varchar | |
| total_charged | decimal | |
| total_allowed | decimal | |
| insurance_paid | decimal | |
| patient_responsibility | decimal | |
| status | varchar | |
| denial_reason | varchar | |
| submitted_date | date | |
| adjudicated_date | date |
claim_line
Grain: one row per procedure line on a claim
| Column | Type | Keys |
|---|---|---|
| claim_line_key | bigint | PK |
| claim_key | bigint | |
| treatment_key | bigint | |
| procedure_code_key | bigint | |
| tooth_number | varchar | |
| tooth_surface | varchar | |
| charged_amount | decimal | |
| allowed_amount | decimal | |
| paid_amount | decimal | |
| status | varchar | |
| adjustment_reason | varchar | |
| service_date | date |
payment
Grain: one row per payment received (patient or insurer)
| Column | Type | Keys |
|---|---|---|
| payment_key | bigint | PK |
| patient_key | bigint | |
| claim_key | bigint | |
| practice_key | bigint | |
| payment_id | varchar | BK |
| payer_type | varchar | |
| amount | decimal | |
| applied_amount | decimal | |
| unapplied_amount | decimal | |
| payment_method | varchar | |
| reference_number | varchar | |
| posted_date | date | |
| status | varchar |
Lookup tables (1)
procedure_code
Grain: static ADA CDT procedure code reference
| Column | Type | Keys |
|---|---|---|
| procedure_code_key | bigint | PK |
| cdt_code | varchar | BK |
| description | varchar | |
| category | varchar | |
| typical_fee | decimal |
Relationships
Each relationship is a foreign key on the first table referencing the second.
- dentist.practice_key → practice (many-to-one)
- patient.practice_key → practice (many-to-one)
- patient_coverage.patient_key → patient (many-to-one)
- patient_coverage.insurance_plan_key → insurance_plan (many-to-one)
- appointment.patient_key → patient (many-to-one)
- appointment.dentist_key → dentist (many-to-one)
- appointment.practice_key → practice (many-to-one)
- treatment_plan.patient_key → patient (many-to-one)
- treatment_plan.dentist_key → dentist (many-to-one)
- treatment.patient_key → patient (many-to-one)
- treatment.dentist_key → dentist (many-to-one)
- treatment.procedure_code_key → procedure_code (many-to-one)
- treatment.appointment_key → appointment (many-to-one)
- treatment.treatment_plan_key → treatment_plan (many-to-one)
- tooth_chart.patient_key → patient (many-to-one)
- tooth_chart.examined_by_dentist_key → dentist (many-to-one)
- claim.patient_key → patient (many-to-one)
- claim.insurance_plan_key → insurance_plan (many-to-one)
- claim.practice_key → practice (many-to-one)
- claim_line.claim_key → claim (many-to-one)
- claim_line.treatment_key → treatment (many-to-one)
- claim_line.procedure_code_key → procedure_code (many-to-one)
- payment.patient_key → patient (many-to-one)
- payment.claim_key → claim (many-to-one)
- payment.practice_key → practice (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.