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.
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
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
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
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
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
Scale calibration lifecycle
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
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
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
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 |