CloudSketch AI Logo
FeaturesTemplatesPricingEnterpriseAboutContactLog in
🌙☀️
Log inStart free
Home/Templates/Pharma & Life Sciences

💊 Pharma & Life Sciences · Database ERD

Electronic Batch Record Schema

A schema for electronic batch records in pharmaceutical manufacturing: products, master recipes, batches, steps, materials used and deviations.

More Pharma & Life Sciences templates

Opening a template is free and never counts toward your daily diagrams. Asking the AI to change it counts as one.

Drawing diagram…

What this diagram shows

  • Each batch follows an approved master recipe version
  • Every step records who did it and when (GMP)
  • Deviations are linked to the batch for QA release

Prompt used

ERD for electronic batch records: Product has MasterRecipes with versions and approval status; Batch is produced from a MasterRecipe with batch number and quantity; BatchStep records each executed step with operator, verifier and timestamps; MaterialConsumption links raw material lots used in steps; Deviation is raised against a Batch; QaRelease records the final release decision.

Mermaid code
erDiagram
  PRODUCT ||--o{ MASTER_RECIPE : "made by"
  MASTER_RECIPE ||--o{ BATCH : "executed as"
  BATCH ||--|{ BATCH_STEP : records
  BATCH_STEP ||--o{ MATERIAL_CONSUMPTION : uses
  MATERIAL_LOT ||--o{ MATERIAL_CONSUMPTION : "consumed in"
  BATCH ||--o{ DEVIATION : raises
  BATCH ||--o| QA_RELEASE : "released by"
  MASTER_RECIPE {
    int id PK
    int product_id FK
    string version
    string status
  }
  BATCH {
    int id PK
    int recipe_id FK
    string batch_no
    decimal quantity
  }
  BATCH_STEP {
    int id PK
    int batch_id FK
    string step_name
    string operator
    string verifier
    datetime done_at
  }
  DEVIATION {
    int id PK
    int batch_id FK
    string classification
    string status
  }

Inside this diagram

An entity relationship diagram with 8 tables, 18 columns and 7 relationships.

Tables and their columns

  • PRODUCT
  • MASTER_RECIPE: id (PK), product_id (FK), version, status
  • BATCH: id (PK), recipe_id (FK), batch_no, quantity
  • BATCH_STEP: id (PK), batch_id (FK), step_name, operator, verifier, done_at
  • MATERIAL_CONSUMPTION
  • MATERIAL_LOT
  • DEVIATION: id (PK), batch_id (FK), classification, status
  • QA_RELEASE

Relationships

  • PRODUCT made by MASTER_RECIPE: each PRODUCT has zero or more MASTER_RECIPE, each MASTER_RECIPE belongs to exactly one PRODUCT
  • MASTER_RECIPE executed as BATCH: each MASTER_RECIPE has zero or more BATCH, each BATCH belongs to exactly one MASTER_RECIPE
  • BATCH records BATCH_STEP: each BATCH has one or more BATCH_STEP, each BATCH_STEP belongs to exactly one BATCH
  • BATCH_STEP uses MATERIAL_CONSUMPTION: each BATCH_STEP has zero or more MATERIAL_CONSUMPTION, each MATERIAL_CONSUMPTION belongs to exactly one BATCH_STEP
  • MATERIAL_LOT consumed in MATERIAL_CONSUMPTION: each MATERIAL_LOT has zero or more MATERIAL_CONSUMPTION, each MATERIAL_CONSUMPTION belongs to exactly one MATERIAL_LOT
  • BATCH raises DEVIATION: each BATCH has zero or more DEVIATION, each DEVIATION belongs to exactly one BATCH
  • BATCH released by QA_RELEASE: each BATCH has zero or one QA_RELEASE, each QA_RELEASE belongs to exactly one BATCH

Make your own diagram like this

Describe your own system in plain English and the AI ER diagram generator draws it in seconds, in the same style as this template.

Related templates

State MachinePharma & Life Sciences

Lab Sample Lifecycle (LIMS)

The states of a laboratory sample in a LIMS, from collection to reporting and disposal, with rejection and re-testing.

SequencePharma & Life Sciences

Adverse Drug Event Reporting

How a suspected side effect reported by a doctor or patient is captured, assessed and reported to the regulator on time (pharmacovigilance).

C4 ArchitectureProPharma & Life Sciences

Clinical Trial Management Architecture

The systems that run a clinical trial: trial management, electronic data capture, randomisation, drug supply and safety reporting.

Data FlowPharma & Life Sciences

Drug Track-and-Trace Data Flow

How medicine packs are serialised and traced from factory to pharmacy to stop counterfeits: unique codes, aggregation and verification.

FlowchartPharma & Life Sciences

Regulatory Submission Workflow

How a pharmaceutical company prepares and files a marketing application (dossier) with regulators and handles their queries.

FlowchartPharma & Life Sciences

GxP Deviation and CAPA Workflow

How a pharmaceutical plant handles a manufacturing deviation: report, impact assessment, root cause, CAPA and batch disposition.