Senior

How would you design the traceability accounting of batches in PostgreSQL?

sobes.tech AI

Answer from AI

To track traceability of batches in PostgreSQL, you can design a structure that allows tracking the origin, movements, and changes of batches at all stages.

Main points:

Batches table — stores unique batch identifiers, their characteristics (e.g., production date, raw materials, quantity).
Batch movements table — records all events with batches: movements, transformations, disposals. Each record contains a reference to the batch, type of operation, date, quantity, and other parameters.
Batch relations table — if batches can be split or merged, a table reflecting the familial relationships between batches (e.g., original batches and those derived from them) is needed.

Example of a simplified schema:

CREATE TABLE batches (
  batch_id SERIAL PRIMARY KEY,
  batch_code VARCHAR UNIQUE,
  production_date DATE,
  raw_material VARCHAR,
  quantity NUMERIC
);

CREATE TABLE batch_movements (
  movement_id SERIAL PRIMARY KEY,
  batch_id INT REFERENCES batches(batch_id),
  movement_type VARCHAR, -- e.g., 'movement', 'packaging'
  movement_date TIMESTAMP,
  quantity NUMERIC,
  location VARCHAR
);

CREATE TABLE batch_relations (
  parent_batch_id INT REFERENCES batches(batch_id),
  child_batch_id INT REFERENCES batches(batch_id),
  PRIMARY KEY (parent_batch_id, child_batch_id)
);

This approach will allow:

Tracking the history of each batch.
Understanding the origin and subsequent use of raw materials.
Analyzing supply and production chains.

For complex scenarios, you can add batch versions, statuses, integration with quality systems, etc.

Previous Next