Example: Use the common data model for pharma use cases

Prev Next

A breakdown of tables from the Common Data Model, used in the Composable MES for pharma.

Note

Before implementing a data model, read How to Use a Common Data Model to understand the basics of structuring tables.

Tulip's Common Data Model considers multiple industries that have different data requirements. For pharmaceutical manufacturing, use the tables listed below for a starting data schema.

The diagram below illustrates how the tables work together. Each connection represents a relationship.
Solid connections use the same fields to share data.

Common Data Model for Pharma table diagram

These tables are used in the Pharma Composable MES App Suite, available in the Tulip Library.

You should adjust the names of the tables to suit your operations as needed, as long as they align with the table's description.

These tables are a standardized starting point that you can extend further. Make sure to adapt this data model to serve your operations by renaming tables, adding tables, or editing necessary fields.

Physical Artifact Tables

Batches

Batches are a specific quantity of a product produced during a single production run, using the same materials and processes.

Label Field Type Description
ID Text * Required: unique identifier
Process Order ID Text Unique identifier for the process order
Product ID Text Unique identifier for the product
Type Text The classification of the batch type (e.g. Semifinished product, Finished product)
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate from other products
Status Text The current state of the batch (e.g., Open, In progress, Blocked, Completed, Reviewed by manufacturing, Reviewed by QA)

Batch lifecycle

image.png

Containers

Containers are used to collect and transfer raw materials, intermediate materials, and products between locations.

Label Field Type Description
ID Text * Required: unique identifier
Cleanliness Status Text The cleanliness condition of the conatiner (e.g. Dirty, Cleaning in process, Clean)
Weight (kg) Number The weight of an item, measured in kilograms
Capacity Number The maximum amount a piece of equipment or container can hold
Unit of Measure Text The standard unit used to quantify materials (e.g., kg, mg, liters)

Container lifecycle

image.png

Equipment and Assets

Reusable equipment or devices that may be required for procedures and may require calibration.

Label Field Type Description
ID Text * Required: unique identifier
Name Text The specific name or designation given to an equipment used in the manufacturing process
Description Text A detailed explanation of the usage of the equipment
Status Text The current state of the equipment (e.g. Dirty, Clean)
Location Text The specific area where the equipment is utilized
Type Text The classification of the equipment (e.g. Deduster, Metal detector, Tableting machine)
Last Calibration Datetime The date when a piece of equipment or instrument was last calibrated

Equipment lifecycle

image.png

Materials

Materials could be raw materials, packaging materials, intermadiate materials, and semifinished or finished products.

Label Field Type Description
ID Text * Required: unique identifier
Material Name Text The specific name or designation given to a raw material used in the manufacturing process
Material Definition ID Text Unique identifier for the material type
Type Text The classification of the material (e.g. Raw material, Weighed material, Premix, Blend, Semifinished product)
Quantity Number The amount or number of units of a material, product, or component used or produced in a process
Unit of Measure Text The standard unit used to quantify materials (e.g., kg, mg, liters)
Batch ID Text Unique identifier for the batch
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate from other products
Product ID Text Unique identifier for the product
Location Text The specific area where materials are stored or used
Potency (%) Number The strength or concentration of an active ingredient in a material or product, expressed as a percentage
Position Text The specific physical location or placement of an item, material, or component within a process
Expiry Date Datetime The date after which a material or product should not be used
Container ID Text Unique identifier for the container

Rooms

Physical rooms for cleaning.

Label Field Type Description
ID Text * Required: unique identifier
Name Text The name of the room
Cleanliness Status Text The cleanliness condition of the conatiner (e.g. Dirty, Cleaning in process, Clean)
Clean Grade Text The cleanliness level or classification of an area or equipment

Samples

Samples are taken during the porcess and inspeced in a variety of locations.

Label Field Type Description
ID Text * Required: unique identifier
Batch ID Text Unique identifier for the batch
Product ID Text Unique identifier for the product
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate from other products
Type Text The classification of the sample (e.g. IPC Off line, QC)
Operator User The name of the operator taking or testing the sample
Quantity Number The amount or number of units of a material, product, or component used or produced in a process
Unit of Measure Text The standard unit used to quantify materials (e.g. kg, mg, liters)
Status Text The current state of the sample (e.g. Created, Procedure Defined, Disposed)
Date Taken Datetime The date on which a sample was collected or a measurement was recorded

Sample lifecycle

image.png

Scales

Scales are used to measure materials in a specified weight range.

Label Field Type Description
ID Text * Required: unique identifier
Minimum Weight Number The lowest acceptable weight for an item or material, often set to ensure compliance with quality or process standards
Unit of Measure of Minimum Weight Text The unit used to quantify the minimum acceptable weight (e.g. grams, milligrams)
Maximum Capacity Number The highest amount or volume that a piece of equipment, container, or system can hold
Unit of Measure of Maximum Weight Text The unit used to quantify the maximum capacity (e.g. liters, kilograms)
Cleanliness Status Text The cleanliness condition of the conatiner (e.g. Dirty, Cleaning in process, Clean)
Calibration Status Text Indicates whether equipment is calibrated and up-to-date with maintenance (e.g. Uncalibrated, Calibrated)

Scale cleanliness lifecycle

image.png

Scale calibration lifecycle

image.png

Stations

Shows all stations on the shop floor.

Label Field Type Description
ID Text * Required: unique identifier
Status Text The current station status (e.g. Running, Down, Idle, Paused)
Status Color Color Color of the current station status
Status Detail cell Track extra status details (e.g. reason downtime)
Process Cell cell The process cell of the station
Operator User The operator currently working at the station
Current Job ID Text Unique identifier for the work order currently in progress at the station
Material Definition ID Text Unique identifier for the material used in the work order in progress

Operational Artifact Tables

Comments and Exceptions

Comments and exceptions are logged when there is a deviation from the standard manufacturing procedures.

Label Field Type Description
ID Text * Required: unique identifier
Batch ID Text Unique identifier for the batch
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate it from other products
Product ID Text Unique identifier for the product
Status Text The current state of the comment or the exception (e.g. New, Reviewed By Sup, Closed)
Severity Text The level of impact or urgency of an issue or deviation
Description Text A detailed explanation or summary of an item, process, or issue that provides additional context or information
Location Text The specific area or equipment where the exception occured
App Name Text The name of the application where the comment or exception was logged
Step Name Text The name of the process step within an application where the exception was logged
Operator Comments Text Notes added by the operator during the production process
Type Text The classification of the artifact (e.g. Comment, Exception)
Image Image A photo of the product where the correction was submitted
Sample ID Text Unique identifier for the sample

Comment and exception lifecycle

image.png

Corrections

Corrections are logged when the app users need to change the previous input value.

Label Field Type Description
ID Text * Required: unique identifier
Operator Comments Text Notes added by the operator during the production process
Batch ID Text Unique identifier for the batch
Reported By User The individual who reports the correction
Reason Text The justification or explanation for the correction
Image Image A photo of the product where the correction was submitted
App Name Text The name of the application where the correction was logged
Step Name Text The name of the process step within an application where the correction was logged

Inspection Results

Stores the results of procedure steps with relation to the material being inspected. These are pass/fail results or measurements taken during a process step that requires an input from the user.

Label Field Type Description
ID Text * Required: unique identifier
Work Order ID Text Unique identifier for the work order
Unit ID Text Unique identifier for the unit
Material Definition ID Text Unique identifier for the material
Type Text Categorization or classification of the grouping or type of the result (i.e. the name of the test)
Status Text The current state of the result
Procedure Text Group results by the higher level process being performed at the time that the result was logged
Location Text Location where the inspection was executed
Image Image Photo of the result
Passed Boolean True/false value for whether the inspection passed
Operator User The operator who executed the inspection
Test Value Text The test value captured
Measured Number The measured result
Target Number The target value that expected when the measurement was performed
LSL Number The lower specification limit when the measurement was performed
USL Number The upper specification limit when the measurement was performed

Inspection result lifecycle

image.png

Process Orders

Process orders are steps taken to fulfill, and deliver customer or production orders

Label Field Type Description
ID Text * Required: unique identifier
Batch ID Text Unique identifier for the batch
Product ID Text Unique identifier for the product
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate from other products
Batch Size Number The quantity of product in a batch, often measured in units or weight
Unit of Measure Text The standard unit used to quantify materials (e.g., kg, mg, liters)
Status Text The current state of the process order (e.g., Released To Manufacturing, In Progress, Completed)
Planned Start Date Datetime The scheduled date to begin a process
Type Text Classification of the process order (e.g. Semifinished product, Finished product)
Planned End Date Datetime The scheduled date to complete a process
Start Date Datetime The actual date a process starts
End Date Datetime The actual date a process ends
QA Release Approver Text The individual responsible for approving the quality assurance release
Manufacturing Release Approver Text The person who approves the release of a product from manufacturing

Process order lifecycle

image.png

References Tables

References are a secondary table type within the Tulip Common Data Model, as they do not fit within a Digital Twin model and should only be considered by advanced users. You should only include Reference tables once you've gone through the Solution Design process and exhausted all other options. Reference tables should NEVER serve as the foundation for an app solution.

GHS

It represents Globally Harmonized System (GHS) information that is the hazardous material classification used internationally.

Label Field Type Description
ID Text * Required: unique identifier
Hazard Statement Text A statement describing the hazards of a substance or mixture
Pictogram Image A graphic symbol that conveys hazard information from the GHS symbols

Material Definitions

Definitions of all Items made, purchased, or assembled. This describes items and their specific properties.

Label Field Type Description
ID Text * Required: unique identifier
Name Text The name of the material definition
Type cell Type of material (e.g. Raw, Intermediate, Final or Make, Buy)
Description Text Details about the material
Image Image Picture of the material
Status Text The current state of the material (e.g. New, Ready, Blocked, Obsolete)
Unit of Measure Text The standard unit used to quantify materials (e.g., kg, mg, liters)
Version/Revision Text The number or letter representing the individual revision of the part
Vendor ID Text The unique ID to reference to the supplier of the material
Target Cycle Time Interval The amount of time it should take to complete 1 unit, this can be used to set target production rates and establish target cycle times.
CAS Number Text The unique identifier for chemical substances that is assigned by the Chemical Abstracts Service
Storage Condition Text The current state of the storage unit/bin
Flammability Class Text The level/class of flammability of the material
Carcinogen/Mutagen Boolean True/false value for whether the material contains carcinogen/mutagen
Period After Opening (month) Number The number of months since the material was opened
MSDS File A document that contains the Material Safety Data Sheet

Material List

Material lists include the materials to dispense for each batch.

Label Field Type Description
ID Text * Required: unique identifier
Batch ID Text Unique identifier for the batch
Product ID Text Unique identifier for the product
Process Order ID Text Unique identifier for the process order
Material Definition ID Text Unique identifier for the material
Material Name Text The specific name or designation given to a raw material used in the manufacturing process
Theoretical Quantity Number The expected amount of material or product that should be used or produced according to the process plan, based on calculations or standards
Unit of Measure of Theoretical Quantity Text The unit used to quantify the theoretical amount of material (e.g. kilograms, liters, tablets)
Quantity Needed Number The amount of material or product left after a process step, taking into account what has already been used or consumed
Unit of Measure of Remaining Quantity Text The unit used to quantify the remaining material or product (e.g., kilograms, liters, tablets)
Type Text The classification of the material (e.g., Raw Material, Compensator, API)
GHS ID Text A unique identifier related to the Globally Harmonized System of Classification and Labeling of Chemicals (GHS)
Tolerance Limit (%) Number The acceptable range of variation for a parameter, expressed as a percentage
Position Text The location or placement of an item within the process
Resource Material Text Materials required or consumed during the manufacturing process

SKU Parameters

SKU parameters refer to the specific attributes that identify and differentiate a product in inventory management (i.e. size, color, brand, model).

Label Field Type Description
ID Text * Required: unique identifier
Product Name Text The official or commercial name of the final pharmaceutical product, used to identify and differentiate from other products
Product ID Text Unique identifier for the product
Language Text The language used in documents or instructions
Tablet Material Definition ID Text The material definition ID of the required tablets for the batch
Article Code of Blisters Text A unique code for blister packaging materials
Article Code of Aluminum Foil Text A unique code for aluminum foil used in packaging
Article Code of Leaflets Text A unique code for informational leaflets included in packaging
Article Code of Cartons Text A unique code for carton packaging materials
Pharma Code of Blisters Text A code specific to the pharmaceutical industry for blister packaging
Pharma Code of Aluminum Foil Text A pharmaceutical code for aluminum foil
Pharma Code of Leaflets Text A pharmaceutical code for leaflets
Pharma Code of Cartons Text A pharmaceutical code for cartons
Temperature of Primary Packaging Number The temperature at which the primary packaging should be maintained
Box Label Type Text The type or format of labels used on boxes
Size of Blisters Text The dimensions of blister packaging
Primary Packaging Speed (blister/min) Number The rate at which blisters are packaged per minute
Secondary Packaging Speed (carton/min) Number The rate at which cartons are packed per minute
Carton Print Design Image The design or layout of printing on cartons
Weight of a Blister (g) Number The weight of an individual blister, typically measured in grams
Camera Setup Program Text Configuration settings for camera systems used in the production process