12may26- Mistakes in data modeling

 

1. The "Hard-Coded" Unit of Measure

In our manufacturing project, we built a schema for Raw Materials but ignored the detail that different global plants might use different measurement systems.

• The Mistake: We modeled the Quantity column as a simple decimal and assumed everyone used Metric (kilograms) without including a UnitID or UOM column.

• The Consequence: When the company acquired a US-based plant using Imperial units, the system began mixing pounds and kilograms in the same column. Production batches failed because the formulas were chemically incorrect, leading to thousands of dollars in wasted physical inventory.

• How we handled it: We performed a massive data migration to add a UnitOfMeasure reference table. We then implemented a calculation layer in the database that automatically converted all inputs into a "Base Unit" while storing the original local unit for the plant’s UI.

---

2. Overlooking "Time-Travel" (Temporal Data)

We ignored the detail that Product Costs are not static; they fluctuate based on the market price of raw materials like steel or plastic.

• The Mistake: We simply overwrote the UnitCost in the Parts table whenever the price changed, rather than creating a historical price table.

• The Consequence: When the finance team tried to run a "Year-to-Date" profitability report, the system calculated January’s sales using May’s high inflation prices. All historical financial reports were wrong, making it look like the company had lost money all year when it hadn't.

• How we handled it: We moved the cost attribute out of the main Parts table and into a PartPriceHistory table. We used EffectiveDate and EndDate columns so the system could "look back in time" and join the correct price to the date the order was actually placed.

---

3. The "Flat" Equipment Hierarchy

In our asset management module, we ignored the detail that a "Machine" is actually a collection of smaller, replaceable components with their own maintenance schedules.

• The Mistake: We modeled the Equipment table as a flat list, treating a "CNC Lathe" as a single entity rather than a parent with child components (like motors or sensors).

• The Consequence: Maintenance teams couldn't track the specific life cycle of a $10,000 motor inside the machine. When the motor was moved to a different lathe, the service history was lost, leading to redundant repairs and a critical "unplanned downtime" event because a sensor's age was hidden.

• How we handled it: We redesigned the model to support a Self-Referencing Relationship (a ParentEquipmentID column within the table). This allowed us to build a "Tree" structure where every sub-part could have its own history while still belonging to the main machine.