An ERP system is only as good as its data model. Get the database design wrong, and you spend the next five years building workarounds, patching data inconsistencies, and dreading every new feature request.
This guide covers the database development patterns that make ERP systems survive a decade of business change — from schema design and normalisation to multi-tenancy, audit logging, and migration strategies.
Core ERP entities and their relationships
Every ERP system, regardless of industry, revolves around a common set of entities: Organisations, Users, Products/Services, Customers, Suppliers, Orders (Sales and Purchase), Invoices, Payments, and Inventory. The art is in how you model the relationships between them.
Use a shared-nothing approach for tenant data in multi-tenant systems: each organisation's data should be logically separated, either through a tenant_id foreign key on every table (simpler) or separate schemas per tenant (more isolated but harder to maintain).
Normalisation vs. performance
Start with Third Normal Form (3NF) for all transactional data. This prevents update anomalies and keeps your data consistent. For reporting, create materialised views or a separate read-optimised schema — do not denormalise your transaction tables for reporting convenience.
The biggest normalisation mistake in ERP systems is embedding calculated values. Do not store the total price on an order row — calculate it from line items. Store the formula, not the result.
Audit trails and temporal data
Every ERP system needs an immutable audit log. For each critical entity (Orders, Invoices, Payments), create a corresponding history table that records who changed what, when, and the before/after values. This is not optional — it is a regulatory and operational necessity.
Consider using a temporal table pattern (system-versioned tables in PostgreSQL) for entities that need point-in-time querying. This lets you answer questions like "what was this customer's credit limit on March 15th?" without complex join logic.
Frequently Asked Questions
Should I use SQL or NoSQL for ERP?+
SQL (PostgreSQL or Microsoft SQL Server) is the right choice for almost all ERP systems. ERP data is inherently relational — orders have line items, customers have addresses, invoices reference orders. NoSQL databases can supplement for specific use cases like logging or caching, but should not be the primary data store.
How do I handle multi-tenancy in ERP databases?+
The simplest approach is a shared database with a tenant_id column on every table, enforced at the application layer. For stronger isolation, use separate database schemas per tenant. Separate databases per tenant offer the strongest isolation but the highest operational overhead.
