Data Model Primer

February 2026 · 16 min

Why an FC Needs to Know the Data Model

You will not write SQL queries. But understanding the data model is essential for:

1. Data Migration -- Mapping legacy data to Bio Ecko tables requires knowing column names, types, and relationships
2. Report Interpretation -- BI dashboards query these tables; understanding them helps you validate report accuracy
3. Troubleshooting -- When a bill shows the wrong amount, knowing that bill_items links to services and tariffs helps you trace the root cause
4. Configuration -- Master data setup (departments, services, formulary) means populating specific tables correctly
5. Integration -- HL7/FHIR mappings reference specific table columns

Think of the data model as the blueprint of the building you are furnishing. You don't build the walls, but you need to know where the rooms are.

Core Entity Hierarchy

Bio Ecko's data model follows a clear hierarchy:

Key Relationships:

• Every patient belongs to an organization
• Every appointment links to a patient and a profiles (doctor)
• Every bill links to a patient and an appointment or admission
• Every lab_order links to an appointment or admission and a lab_test (from the test catalogue)

Patient Data Tables

The patient is the centre of the entire data model. Here are the key patient-related tables:

MRN (Medical Record Number):

• Auto-generated unique ID for each patient within an organization
• Format is configurable (e.g., MRN-2026-00001 or BEK/12345)
• Once assigned, an MRN never changes -- it is the patient's permanent identifier
• Duplicate MRN detection is built-in to prevent the same patient from getting two records

Clinical Data Tables

Clinical data captures what happens during patient encounters:

FC Insight: The visit_diagnoses table is critical for ABDM compliance, insurance claims, and clinical analytics. Ensure ICD-10 coding is enforced (not free text) during configuration.

Financial Data Tables

Financial tables track the revenue cycle:

Tariff System: A service can have multiple tariffs -- one per branch, one per insurance plan, one for self-pay patients. The billing engine picks the correct tariff based on the patient's context. During configuration, you must set up tariffs carefully -- pricing errors are the #1 source of billing complaints.

Inventory & Pharmacy Tables

Pharmacy and inventory share a common stock management backbone:

FEFO (First Expiry First Out): Bio Ecko enforces FEFO for pharmacy dispensing -- the batch closest to expiry is dispensed first. This is critical for medication safety and waste reduction.

Relationships -- How Tables Connect

Understanding foreign keys (links between tables) is the most important data model concept for an FC:

Example Chain: Patient → Visit → Prescription → Dispensing → Billing

1. patients.id → referenced by appointments.patient_id
2. appointments.id → referenced by prescriptions.appointment_id
3. prescriptions.id → referenced by prescription_items.prescription_id
4. prescription_items.id → referenced by dispensing_records.prescription_item_id
5. dispensing_records.id → billing creates a bill_items entry linked to the dispensing

Why This Matters:

• If you delete a patient (rare, but possible), all their appointments, prescriptions, and bills are affected
• If a service tariff is wrong, every bill item using that tariff is wrong
• If a drug in the formulary has an incorrect generic name, every prescription referencing it inherits the error

FC Rule: Fix master data errors immediately when discovered. The longer wrong master data stays, the more transactions it corrupts.

Audit Trail and Soft Deletes

Bio Ecko uses two data integrity patterns:

Audit Columns (on every table):

• created_at -- When the record was created
• updated_at -- When last modified
• created_by -- Which user created it
• updated_by -- Which user last modified it

Soft Deletes (on critical tables): Instead of physically deleting records, Bio Ecko sets a deleted_at timestamp. The record remains in the database but is hidden from the UI. This is essential for:

• Regulatory compliance (medical records must be retained)
• Audit trails (who deleted what and when)
• Recovery (accidentally deleted records can be restored)

FC Relevance: When a user says "I deleted a patient record by mistake," the data is not lost. A super-admin can restore soft-deleted records from the admin panel.

Notes

Related topics