Database Design Mistakes That Sink ERP Systems Later

Enterprise resource planning systems live or die by their data model. Unlike a typical CRUD app, an ERP's schema has to represent real financial and operational truth — ledgers, contractor assignments, billing — where a modeling mistake doesn't just mean a bug, it means numbers that don't reconcile.

After building a ledger and billing system for government tender management, here are the mistakes I see most often, including a couple I've made myself.

Storing computed balances instead of deriving them

It's tempting to add a balance column to a contractor's account and update it on every transaction. It's fast to query and feels efficient. It's also a ticking time bomb: the moment a single write path forgets to update it — a manual database fix, a bug in an edge-case job, an aborted transaction — your stored balance and your actual transaction history disagree, and nobody notices until an audit.

The fix is boring but correct: store the ledger as an immutable sequence of transactions, and derive the balance with a query (or a materialized/cached view refreshed from the same source of truth). If you need the performance of a stored balance, cache it — but always be able to recompute it from the transaction log and treat any mismatch as a critical alert, not routine drift.

-- Derive, don't trust a stored column
SELECT contractor_id, SUM(amount) AS balance
FROM ledger_transactions
WHERE contractor_id = ?
GROUP BY contractor_id;

Modeling status as a string column

status VARCHAR(20) on a tender or contractor record seems harmless until you have five subtly different spellings of "approved" across two years of manual data entry and three developers. String status columns don't just risk typos — they make it impossible for the database to enforce which transitions are even valid.

For anything with a real state machine — a tender moving from draft to assigned to billed to paid — model the valid transitions in code (a state machine or transition table), and constrain the column with an enum or a foreign key to a statuses lookup table. It's a small amount of extra ceremony that eliminates an entire class of "how did this get into an impossible state" debugging sessions.

Not indexing for the query you'll actually run

Early in a project, tables are small and every query feels fast — index or not. The mistake is designing indexes around the schema instead of around the access patterns you already know are coming: "get all tenders for a contractor in a date range," "get all payments for a tender." If those are your two most common queries and there's no composite index supporting them, you're building a slow query into the schema from day one — it just won't hurt until the table has real volume.

Treating soft deletes as free

Soft-deleting rows (deleted_at) is the right call for auditability in financial systems — you never actually want to lose a ledger entry. But every unique constraint and every "active records" query needs to account for it explicitly, or you'll hit two failure modes: duplicate-key errors on records that look deleted but aren't excluded from a unique index, and reports that silently include soft-deleted rows because a whereNull('deleted_at') was missed somewhere.

The pattern behind all of these

Every one of these mistakes shares a root cause: designing the schema for how it looks on day one instead of how it behaves under real, messy, multi-year production use. In ERP systems specifically, that means designing for auditability and correctness first, and query convenience second — because the cost of an ERP schema mistake isn't a bug ticket, it's a reconciliation problem months later.