The report table is agreed. No one is responsible for it.
This is a different problem.
The table looked clean on the whiteboard.
Seventeen columns. One row per batch.
Every department's data, agreed, signed, version-numbered.
Oran had been looking at it for three days.
He had a document called Problems with this table.
It had eleven items in it now.
He was still finding more.
Problem 1: One batch, many inspections.
QA inspects at three stages: incoming material, in-process, and final.
Each stage produces a separate inspection record.
Each record has its own result, its own inspector, its own timestamp.
The current table has one row per batch.
One inspection_result column. One inspector_id. One inspected_at.
Which inspection result goes in the row?
Oran sent an email to QA.
QA replied: "Show the final result. That's the one that matters."
Oran replied: "What if the final result is pass but the in-process result was fail and there's no record of what corrective action was taken?"
QA did not reply for two days.
Then they replied: "That's a good point. We'll discuss internally."
Three days later: "Can you just add three columns? One per stage."
Oran looked at the table.
He added three columns.
He stared at them.
Product A has three inspection stages.
Product B has four.
Product C has two, but the second one is optional depending on customer requirements.
The new product line launching in Q3 has a stage that doesn't exist yet.
He deleted the three columns.
He opened a new file.
-- This cannot be one table.
-- This is at least two tables.
CREATE TABLE production_batches (
batch_id VARCHAR(50) PRIMARY KEY,
product_code VARCHAR(50) NOT NULL,
...
);
CREATE TABLE inspection_results (
result_id BIGINT PRIMARY KEY,
batch_id VARCHAR(50) REFERENCES production_batches(batch_id),
stage VARCHAR(50), -- 'incoming' / 'in_process' / 'final'
result VARCHAR(20), -- 'pass' / 'fail' / 'conditional'
inspector_id VARCHAR(50),
inspected_at DATETIME,
notes TEXT
);
He sent this to QA.
QA said: "That looks more complicated."
Oran said: "It is more complicated. That's because the data is more complicated."
QA said: "Can we think about this after the audit?"
Problem 2: One material lot, many batches. One batch, many material lots.
This one took Oran a morning to find
and an afternoon to fully understand why it was a problem.
A production batch uses multiple raw materials.
Each raw material comes from a specific lot.
Each lot has its own supplier, its own incoming inspection record,
its own certificate of conformance.
For traceability — which is the entire point of this system —
you need to be able to answer: if a defect is found in the field,
which material lot might have caused it?
The current table has one column: material_lot_no.
One column, one lot.
Reality: three to seven lots per batch, depending on the product.
-- Also cannot be one table.
-- This is a junction table.
CREATE TABLE batch_material_lots (
batch_id VARCHAR(50) REFERENCES production_batches(batch_id),
lot_no VARCHAR(50) REFERENCES material_lots(lot_no),
quantity_used DECIMAL(10,3),
unit VARCHAR(20),
PRIMARY KEY (batch_id, lot_no)
);
Oran sent this to Manufacturing.
Manufacturing said: "We don't track it that granularly."
Oran said: "The audit requires traceability to material lot level."
Manufacturing said: "We'll start tracking it."
Oran said: "When?"
Manufacturing said: "We'll discuss internally."
That was eight days ago.
Problem 3: The spec that changed.
This one Oran found by accident.
He was reviewing historical data to understand the migration scope
when he noticed that Product A's defect threshold
was different in the records from fourteen months ago
versus the records from six months ago.
He checked the spec document.
Current version: defect rate threshold 2.0%.
He went looking for the previous version.
It existed in an email chain.
Sent by the previous Engineering manager.
Who had left the company.
Eleven months ago.
Previous threshold: 3.5%.
Which means: batches that were compliant under the old spec
are non-compliant under the new spec.
And batches from the transition period —
when the spec changed but the system wasn't updated —
are recorded against the wrong threshold.
Compliant against which version?
-- The spec is not a column.
-- The spec is a table.
-- With versions.
CREATE TABLE product_specs (
spec_id BIGINT PRIMARY KEY,
product_code VARCHAR(50),
version VARCHAR(20),
effective_from DATE,
effective_to DATE, -- NULL means current
defect_threshold DECIMAL(5,2),
created_by VARCHAR(50),
notes TEXT
);
-- And the inspection result needs to reference the spec version
-- that was in effect at the time of inspection.
-- Not the current spec.
-- The spec that existed on that date.
Oran sent this to Engineering.
Engineering said: "That's a lot of tables."
Oran said: "Yes."
Engineering said: "Can we just use the current spec for everything?"
Oran said: "Then the historical data is inaccurate."
Engineering said: "Is that a problem for the audit?"
Oran looked at his notebook.
He thought about Ms. Chen and her iPad.
He thought about the word traceability.
"Yes," he said. "It is a problem for the audit."
Engineering said: "We'll discuss internally."
The status meeting, Day 47.
The CEO asked for an update.
Oran presented the schema.
Not the whiteboard table. The real schema.
Six tables. Fourteen relationships. Three pages of documentation.
The VP of Manufacturing said:
"I thought we agreed on one table."
Oran said:
"We agreed on what data to capture.
The structure needed to change to capture it correctly."
The VP of QA said:
"Who is responsible for making sure the data gets into this system?"
The room looked at Oran.
Oran looked at his notebook.
He found the page from Day 1.
He read it out loud:
"Passive cooperation is not cooperation.
If a department does not submit data by the agreed date,
the project is blocked.
That is a management issue, not an IT issue."
He looked up.
"This was agreed. It was signed. The submission deadline for Production data was Day 40.
Today is Day 47. I have not received Production's historical data."
The VP of Manufacturing smiled.
"We've been very busy. We'll get it to you."
Oran wrote the date in his notebook.
He put a small star next to it.
A report that everyone agreed on
and no one was responsible for
is not a report.
It is a beautiful, shared, distributed lie.
The schema was right now.
The data was still in the drawers.
Day 47 of 90.
Next: Ep6 — The Abstract Table and the Gateway
Day 87. Three days before re-audit.
The departments are still in a meeting about column names.
Oran is in the server room with three tables and a whiteboard.
While everyone argued, IT built the thing anyway.
It wasn't perfect. It was enough.
For now.
Coding Cat Oran is a serialized fiction about building real production systems inside real companies.
The one-to-many relationships are real. The many-to-many relationships are real.
The spec that changed with no documentation is, unfortunately, extremely real.
The cat is fictional.
By SysLayer · dev.to/syslayer