Database architecture for large business systems
Normalized schemas, indexes designed for real queries, atomic writes and separate read paths for reports.
- The schema is modeled from the domain first: clear entities, foreign keys and constraints.
- Composite indexes follow the actual filters and sort orders the application uses.
- Multi-step writes such as an order with its payment run inside transactions.
- Hot reads are cached and heavy reports read from aggregates instead of scanning live tables.
Context
ZarST holds 464 buildings, 22,000 apartments, 845 wells, ~1,000 dispatch requests and 200,000+ billing rows; SuvCRM keeps every business’s orders, bottles and payments.
Technical challenge
Screens built on naive queries slowed down as the data grew, and financial data must stay consistent on every change.
Chosen solution
Server-side pagination, no N+1 queries and ETag caching; bottle deposits and balances kept as ledgers; data scoped per business.
Trade-offs
ETags revalidate instead of serving stale data — one extra round trip in exchange for responses that are always correct.
Result
Request archive 328 → 44 ms with the response cut from 735 to 13 KB; user profile 59 → 1.6 ms; network map 1.6 → 0.1 s.
All deep dives