When a Business Actually Needs Proper Database Design
A poorly designed database rarely announces itself on day one. It shows up six months later as duplicate customer records, reports that don't reconcile, a login page that times out during month-end billing, or a developer afraid to add one more column because nobody remembers why three other tables already store the same field. If any of that sounds familiar, the problem usually isn't the application code - it's the schema underneath it. Urgent IT Solution gets called in at exactly this point: when an Excel-based tracker has been outgrown, when a startup's MVP database was never meant to survive real traffic, or when a merger has left two systems with incompatible data models that somehow need to talk to each other.
Relational vs NoSQL: A Decision, Not a Default
We don't default to MySQL or PostgreSQL just because they're familiar, and we don't reach for MongoDB just because it's trendy. The choice depends on the shape of the data and how it will be queried:
- PostgreSQL or MySQL for transactional systems with strong entity relationships - order management, HRMS, inventory, financial ledgers - where ACID compliance and referential integrity actually matter.
- MongoDB or similar document stores for catalog-style data, content management backends, or systems where the schema itself changes frequently and joins are rare.
- Hybrid setups - a relational core for transactions paired with a document store or Redis cache for session data, search indexes, or high-read lookup tables - are common in mid-sized SaaS and marketplace applications we build.
- Time-series databases like TimescaleDB when the workload is IoT sensor logs, monitoring metrics, or anything dominated by timestamped inserts and range queries.
What the Design Process Actually Involves
Entity Modeling and Normalization
We start with entity-relationship diagrams built from real business rules, not generic templates - what counts as a "customer," whether an order can exist without a confirmed payment, how returns and refunds relate to the original transaction. From there we normalize to third normal form as a baseline, then deliberately denormalize specific tables where read performance justifies it - a common trade-off in reporting tables, dashboard aggregates, and any table that gets hit by high-frequency SELECT queries.
Indexing Strategy
Indexes are decided by query patterns, not applied blindly to every foreign key. We review the actual WHERE clauses, JOIN conditions, and ORDER BY usage the application will run, then build composite indexes matched to those access paths. Over-indexing slows down writes and bloats storage just as badly as under-indexing slows down reads, so this is a deliberate balancing exercise, usually revisited after the first few weeks of production traffic using EXPLAIN ANALYZE output.
Constraints, Keys and Data Integrity
Foreign keys, unique constraints, check constraints, and NOT NULL rules get enforced at the database layer, not left entirely to application code. This matters because application code changes, gets rewritten in a new framework, or gets bypassed by a direct script five years later - the database should still refuse to store a negative price or an order with no customer, regardless of what wrote the query.
Migration and Legacy Data Work
A large share of database engagements aren't greenfield builds - they're migrations. Moving from a legacy Access database, an old MySQL 5.x instance, or an on-premise SQL Server box to a managed cloud instance (AWS RDS, Azure SQL, or Google Cloud SQL) involves more than a dump-and-restore. We map old fields to new schemas, write transformation scripts for inconsistent legacy data (mixed date formats, duplicate entries, orphaned foreign keys), and run parallel validation - comparing row counts, checksums, and sample records between old and new systems before cutover. Downtime windows are planned around the client's actual usage patterns, not scheduled generically at midnight if the business runs 24/7 across regions.
Performance Tuning for Databases Already in Production
Not every project starts from scratch. We regularly get asked to fix a database that's already live and already slowing down under real load. That work looks different from initial design:
- Reviewing slow query logs and identifying the specific queries responsible for timeouts, not just "the database feels slow."
- Query rewriting - replacing correlated subqueries with joins or window functions, eliminating N+1 query patterns coming from the application's ORM.
- Connection pooling configuration (PgBouncer, for example) when the issue is connection exhaustion rather than query speed.
- Partitioning large tables by date or region when a single table has grown past tens of millions of rows and full scans are becoming routine.
- Read replicas to separate reporting/analytics load from transactional writes, so a monthly report doesn't lock up checkout processing.
Security and Access Control at the Data Layer
Application-level authentication is not the same as database-level access control, and both need attention. We implement role-based database users with the minimum privileges each application service actually needs - a reporting service gets read-only access, a background job that only updates order status doesn't get DELETE rights on the customer table. Sensitive fields (payment details, government ID numbers, health data) are encrypted at rest and, where relevant, tokenized so the raw values never sit in plain text even inside internal admin tools. For clients in regulated sectors, we also account for audit logging requirements - tracking who changed what record and when, which is often a compliance requirement rather than an optional nice-to-have.
Deliverables You Actually Get
- An ER diagram and data dictionary documenting every table, field, relationship, and constraint.
- The schema itself, delivered as version-controlled migration scripts (using tools like Flyway, Liquibase, or framework-native migrations) rather than a one-time SQL dump.
- Indexing and query optimization report with before/after benchmarks where performance work was involved.
- A backup and recovery plan appropriate to the data's importance - point-in-time recovery for transactional systems, scheduled snapshots for lower-stakes data.
- Handover documentation written for whoever maintains the system next, including our team if ongoing support is part of the engagement.
How This Fits With the Rest of a Project
Database design rarely happens in isolation. It's usually the foundation under a custom software build, an ERP or HRMS module, or an application migration project. Because Urgent IT Solution also builds the application layers that sit on top of these databases, the schema is designed with the actual API and reporting requirements in mind from the start, rather than being handed off as an abstract diagram that the application team has to reverse-engineer around. For standalone database engagements - where another team owns the application code - we work directly from existing API contracts and reporting specs to make sure the schema serves both without requiring a redesign six months in.