Motion Forward Physical Therapy =============================== Database schema: physical_therapy_clinic Platform: postgresql Tables: 8 Total rows: 4,404 ABOUT THE BUSINESS ------------------ Motion Forward Physical Therapy is an outpatient rehabilitation provider operating five clinics across a metropolitan region. The company offers physical therapy services covering injury recovery, post-surgical rehabilitation, chronic pain management, and specialty programs for populations such as pediatric, geriatric, and neurological patients. Patients arrive either through a physician referral or by self-referring directly to a clinic. The company bills a mix of private health insurers, Medicare, Medicaid, Workers Compensation programs, and VA Benefits to collect payment for the services it delivers. HOW THE BUSINESS OPERATES ------------------------- When a new patient arrives, their demographic information and insurance coverage are recorded, and a therapist conducts an initial evaluation that results in a formal treatment plan specifying a diagnosis and a maximum number of authorized visits. From that point forward, the patient schedules individual appointments against that treatment plan, each visit handled by a licensed therapist at one of the five clinic locations. After a visit is marked complete, the billing staff submits an insurance claim to the appropriate payer on the patient's behalf. The claim then moves through the insurer's review process and may be approved, partially approved, denied, or appealed, with the final approved dollar amount recorded so management can monitor how much revenue is actually collected versus what was billed. WHAT EACH TABLE HOLDS --------------------- clinics (12 rows) One row per physical clinic location that Motion Forward operates. This table serves as the master list of locations that therapists are assigned to and where appointments are held. therapists (260 rows) One row per licensed physical therapist currently or previously employed by Motion Forward. Each therapist is based at a specific clinic and holds one of six recognized specialties that determines the types of cases they are best suited to treat. patients (260 rows) One row per person who has registered as a patient at any Motion Forward clinic, regardless of how many treatments or visits they have had. insurers (12 rows) One row per insurance company or government payer program whose coverage Motion Forward accepts. The reimbursement rate captures the negotiated percentage of the billed amount that this payer typically covers. patient_insurers (1,200 rows) One row per insurance policy held by a patient. Because a patient may carry both a primary and a secondary insurance plan, a single patient can have more than one row here. This table records the policy number, when coverage began, and which policy is the primary one used for billing. treatment_plans (260 rows) One row per course of treatment prescribed for a patient following their evaluation. A treatment plan is tied to a specific diagnosis, assigns a lead therapist responsible for the patient's care, and caps the number of visits the insurer has authorized. As care progresses the plan moves through statuses such as Active, Completed, or On Hold. appointments (1,200 rows) One row per visit that has been scheduled or carried out at a clinic. Each appointment is linked to the governing treatment plan, records which therapist actually saw the patient and at which location, and captures how long the session ran and how much was billed for it. insurance_claims (1,200 rows) One row per claim submitted to an insurer seeking reimbursement for a completed appointment. This table tracks the full lifecycle of a claim from initial submission through approval or denial, and records the amount the insurer actually agreed to pay as well as the reason if a claim was rejected. HOW THE TABLES RELATE --------------------- Each therapist is assigned to one clinic, and every appointment takes place at a specific clinic with a specific therapist, tying individual visits back to both the staff and location involved. A patient's care is organized through treatment plans — each plan belongs to one patient and designates one lead therapist — and appointments are then scheduled against that plan so the clinic can track progress toward the authorized visit limit. On the billing side, each completed appointment can generate an insurance claim directed to one of the insurers listed on the patient's coverage record, and the outcome of that claim — including any denial reason and the amount actually approved — is recorded separately so the business can reconcile what it billed against what it collected. - Each therapists row belongs to one clinics, through therapists.clinic_id → clinics.clinic_id. - Each patients row belongs to one clinics, through patients.referring_clinic_id → clinics.clinic_id. - patient_insurers resolves a many-to-many relationship: each row pairs one patients with one other party via patient_insurers.patient_id. - patient_insurers resolves a many-to-many relationship: each row pairs one insurers with one other party via patient_insurers.insurer_id. - Each treatment_plans row belongs to one patients, through treatment_plans.patient_id → patients.patient_id. - Each treatment_plans row belongs to one therapists, through treatment_plans.therapist_id → therapists.therapist_id. - Each appointments row belongs to one treatment_plans, through appointments.treatment_plan_id → treatment_plans.treatment_plan_id. - Each appointments row belongs to one therapists, through appointments.therapist_id → therapists.therapist_id. - Each appointments row belongs to one clinics, through appointments.clinic_id → clinics.clinic_id. - Each insurance_claims row belongs to one appointments, through insurance_claims.appointment_id → appointments.appointment_id. - Each insurance_claims row belongs to one insurers, through insurance_claims.insurer_id → insurers.insurer_id. DATA DICTIONARY --------------- clinics clinic_id INTEGER e.g. 1 A unique identifier assigned to each clinic location, used throughout the database to connect therapists, patients, and appointments back to the physical site. clinic_name VARCHAR(120) e.g. Limited listing Human-readable name of the clinic location, e.g. 'Northside Sports Rehab'. street VARCHAR(120) e.g. 7014 Vineyard Pkwy Street address of the clinic. city VARCHAR(60) e.g. Evanston City where the clinic is located. state VARCHAR(2) e.g. PA State abbreviation. postal_code VARCHAR(10) e.g. 47140 ZIP code of the clinic. therapists therapist_id INTEGER e.g. 1 Surrogate primary key for the therapist. first_name VARCHAR(50) e.g. Gideon Therapist's given name. last_name VARCHAR(50) e.g. Abara Therapist's family name. specialty VARCHAR(60) e.g. Orthopedic The clinical area in which the therapist is licensed and primarily practices — one of Orthopedic, Neurological, Pediatric, Sports Medicine, Geriatric, or Cardiopulmonary. clinic_id INTEGER e.g. 4 The clinic where this therapist is primarily based and sees patients. hire_date DATE e.g. 2022-07-12 The date the therapist began employment with Motion Forward. patients patient_id INTEGER e.g. 1 Surrogate primary key for the patient. first_name VARCHAR(50) e.g. Rosa Patient's given name. last_name VARCHAR(50) e.g. Egwu Patient's family name. date_of_birth DATE e.g. 1998-02-26 The patient's birth date, used to determine age and eligibility for age-specific programs such as pediatric or geriatric care. phone VARCHAR(20) e.g. (772) 328-8523 Primary contact phone number. email VARCHAR(120) e.g. rosa.egwu1@example.com Email address; not all patients provide one. referring_clinic_id INTEGER e.g. 5 The clinic that first registered or received this patient, whether through a physician referral or self-referral. insurers insurer_id INTEGER e.g. 1 Surrogate primary key for the insurer. insurer_name VARCHAR(120) e.g. Timberline Trading Name of the insurance company or program, e.g. 'BlueCross BlueShield'. payer_type VARCHAR(60) e.g. Medicaid Classifies the insurer as a Private health plan, Medicare, Medicaid, Workers Compensation program, or VA Benefits — categories that differ significantly in billing rules and reimbursement rates. reimbursement_rate DECIMAL(5,2) e.g. 0.88 The negotiated rate, expressed as a percentage of the billed amount, that this payer agrees to reimburse for covered services. patient_insurers patient_insurer_id INTEGER e.g. 1 Surrogate primary key for the coverage record. patient_id INTEGER e.g. 59 The covered patient. insurer_id INTEGER e.g. 2 The insurer providing coverage. policy_number VARCHAR(30) e.g. POL-HB-114972 The unique identifier printed on the patient's insurance card for this particular policy. coverage_start DATE e.g. 2022-05-20 The date on which this insurance policy became active for the patient. is_primary BOOLEAN e.g. 0 Indicates whether this policy is the patient's primary insurance. A value of true means this insurer is billed first; a secondary policy, if present, may cover remaining balances. treatment_plans treatment_plan_id INTEGER e.g. 1 Surrogate primary key for the treatment plan. patient_id INTEGER e.g. 6 The patient this plan is designed for. therapist_id INTEGER e.g. 169 The therapist who authored the plan. diagnosis_code VARCHAR(30) e.g. M11.7 A standardized medical diagnosis code documenting the condition being treated under this plan. start_date DATE e.g. 2022-09-23 Date the treatment plan officially begins. authorized_visits INTEGER e.g. 5 The total number of therapy visits the insurer has pre-approved for this treatment plan. Appointments beyond this number may not be reimbursed. status VARCHAR(60) e.g. Completed Where the treatment plan currently stands — Active means care is ongoing; Completed and Discharged mean the patient finished treatment; On Hold means care is temporarily paused; Cancelled means the plan was abandoned before completion. appointments appointment_id INTEGER e.g. 1 Surrogate primary key for the appointment. treatment_plan_id INTEGER e.g. 14 Identifies which treatment plan this visit is being conducted under, linking the visit to the patient's diagnosis and authorized visit count. therapist_id INTEGER e.g. 1 The therapist who conducted or will conduct the session. clinic_id INTEGER e.g. 10 The clinic location where the appointment takes place. appointment_datetime TIMESTAMP e.g. 2022-06-28 19:35:00 The date and time the appointment was scheduled to begin. status VARCHAR(60) e.g. Rescheduled The outcome of the scheduled visit — whether the patient attended and it was Completed, whether they did not show up (No-Show), or whether the visit was Cancelled, Rescheduled, or is still In Progress. duration_minutes INTEGER e.g. 65 How long the therapy session actually lasted, in minutes. billed_amount DECIMAL(12,2) e.g. 76.08 The dollar amount charged to the insurer for this visit before any insurer adjustments or patient cost-sharing. insurance_claims claim_id INTEGER e.g. 1 Surrogate primary key for the insurance claim. appointment_id INTEGER e.g. 179 The appointment this claim is billing for. insurer_id INTEGER e.g. 6 The insurer to whom the claim was submitted. submitted_date DATE e.g. 2025-02-11 The date the billing staff sent this claim to the insurer for review. claim_status VARCHAR(60) e.g. Submitted The current standing of the claim in the insurer's review process — ranging from Submitted and Under Review through Approved, Partially Approved, Denied, or Appealed. approved_amount DECIMAL(12,2) e.g. (null) The dollar amount the insurer has agreed to pay for this claim. This may be less than the billed amount or zero if the claim was denied. denial_reason VARCHAR(60) e.g. (null) When a claim is denied or only partially approved, this field records the insurer's stated reason, such as Coverage Lapsed, Not Medically Necessary, or Visit Limit Exceeded. PROVENANCE ---------- Every value in this dataset is synthetic. No real person, company, or record is represented. Generated by the Agoge dataset generator 1.0.0 with seed 11. The same brief and seed reproduce this data exactly.