Normalization vs. Denormalization
In enterprise ERP platforms, striking the right balance between database normalization (reducing duplicate data) and denormalization (improving read speeds) is a key architectural decision. For financial ledgers, 3rd Normal Form is strictly adhered to, but for reporting dashboards, materialized views are essential.
Foreign Key Cascades and Safety
Never use ON DELETE CASCADE on critical entities like Users, Orders, or Invoices. Accidental deletions can wipe out terabytes of historical data. Instead, use soft-deletes (an 'is_deleted' boolean column) or ON DELETE RESTRICT to maintain strict referential integrity.
ALTER TABLE invoices
ADD CONSTRAINT fk_customer
FOREIGN KEY (customer_id)
REFERENCES customers(id)
ON DELETE RESTRICT;Composite Indexing Strategies
ERPs have complex search forms (e.g., 'Find all invoices for Customer X between Date Y and Date Z with Status W'). We analyze query plans (EXPLAIN ANALYZE) to build composite B-Tree indexes that match these exact query patterns, transforming 5-second queries into 50-millisecond queries.