Database Design Basics: Building Scalable Web Applications
Every web application is only as good as the database behind it. The login screen, the dashboard, the reports your team pulls every morning – all of it depends on how your data is organized, stored, and retrieved. Get the database design right, and your app runs fast, scales smoothly, and adapts to new requirements without a complete rewrite. Get it wrong, and you’ll spend years fighting bugs, slow queries, and migration headaches.
We build custom web applications for businesses at Hunter Creative Media, and database design is where every project starts. Not wireframes, not color palettes – the data model. Because the structure of your data determines what your application can and can’t do, both now and five years from now. Anyone starting from scratch will get more out of a full breakdown of when custom web applications make sense.
Here’s what you need to know about database design, explained in terms that make sense even if you’ve never written a line of SQL.
Why Database Design Matters for Your Business
Think of your database as the filing system for your entire application. If you throw every document into one giant drawer, finding anything takes forever, and things get lost. If you organize documents into labeled folders with a clear system, everything is fast and reliable.
Poor database design shows up as:
Good database design prevents all of this. It’s cheaper to design it right upfront than to fix it after your app is in production with real users depending on it.
Relational vs. Non-Relational Databases
The first decision in database design is choosing the right type of database. Both have their place, and the right choice depends on your application’s needs.
Relational Databases (SQL)
Relational databases store data in tables with rows and columns, like a spreadsheet. Tables relate to each other through shared fields. PostgreSQL, MySQL, and Microsoft SQL Server are the most common.
Best for:
- Applications with structured, predictable data (CRMs, inventory systems, financial apps)
- Data that has clear relationships (customers have orders, orders have line items)
- Applications that need strong data integrity (you can’t accidentally delete a customer who has open orders)
- Complex reporting and querying
Example: A project management app where users belong to organizations, organizations have projects, projects have tasks, and tasks have comments. The relationships between these entities are clear and consistent.
Non-Relational Databases (NoSQL)
Non-relational databases store data in flexible formats like JSON documents, key-value pairs, or graphs. MongoDB, Firebase, and DynamoDB are popular choices.
Best for:
- Applications with highly variable data structures (a product catalog where different products have different attributes)
- Real-time applications that need extreme read/write speed (chat apps, live dashboards)
- Applications where the data model is still evolving rapidly (early-stage MVPs)
- Content management where each piece of content has a unique structure
Our take: For most business applications we build, relational databases (specifically PostgreSQL) are the right foundation. The data in business apps is inherently relational – customers, orders, invoices, employees, schedules. Relational databases enforce consistency and make complex queries straightforward. We reach for non-relational databases when the use case specifically demands it, not as a default.
Core Database Concepts Explained Simply
Tables and Columns
A table is a collection of related data. A “customers” table might have columns for name, email, phone, and address. Each row is one customer. Each column is one piece of information about that customer.
The key design decision is what gets its own table. A good rule: if something can exist independently or if it has its own distinct set of attributes, it probably deserves its own table.
Primary Keys
Every table needs a primary key: a unique identifier for each row. This is usually an auto-incrementing integer (id: 1, 2, 3...) or a UUID (a long random string like 550e8400-e29b-41d4-a716-446655440000). We prefer UUIDs for most applications because they’re globally unique and don’t leak information about how many records exist.
Relationships
Relationships define how tables connect to each other. There are three types:
customer_id column that references the customers table.enrollments) that connects the two.user_id that references the users table.Getting relationships right is the foundation of good database design. They determine how your application queries data and how efficiently it can do so.
Indexes
An index is like the index in the back of a textbook. Without it, the database has to scan every single row to find what you’re looking for. With an index on the right column, it jumps straight to the answer.
Always index:
- Primary keys (most databases do this automatically)
- Foreign keys (the
customer_idin your orders table) - Columns you frequently search or filter by (email, status, date)
- Columns you sort by
Don’t over-index. Every index speeds up reads but slows down writes, because the index has to be updated every time data changes. Index the columns you actually query, not every column in the table.
Normalization
Normalization is the process of organizing your data to reduce redundancy. Instead of storing a customer’s full address on every order, you store the address once in the customers table and reference it from the orders table.
The practical goal: every piece of information should live in exactly one place. When you update a customer’s email, you update it in one row in one table, and everywhere that references that customer automatically has the correct email.
There are formal “normal forms” (1NF, 2NF, 3NF, etc.), but for most business applications, following this principle gets you 90% of the way there: don’t duplicate data across tables, and don’t store multiple values in a single column.
Common Patterns for Business Applications
After building dozens of business apps, we’ve found certain patterns come up in almost every project. Incorporating these from the start saves significant rework later.
Users and Authentication
Nearly every app needs users. A well-designed users table includes:
- Unique identifier (UUID)
- Email (unique, indexed)
- Hashed password (never store plaintext)
- Account status (active, suspended, pending verification)
- Timestamps (created_at, updated_at, last_login_at)
Keep authentication data separate from profile data. The users table handles login. A profiles table handles display names, avatars, preferences, and other non-security information.
Role-Based Access Control
Most business apps need different permission levels. We typically implement this with three tables:
This is more flexible than hardcoding role checks throughout your application. When the client says “we need a new supervisor role that can do everything a manager can except delete records,” you add a role and assign permissions. No code changes needed.
Audit Logs
For business applications that handle sensitive data, keeping a record of who did what and when is often a requirement. An audit log table typically records:
- Who performed the action (user_id)
- What action was performed (create, update, delete)
- What record was affected (table name + record ID)
- What the data looked like before and after the change
- When it happened (timestamp)
We build audit logging into every business application from day one. Retrofitting it later is painful and you lose all the history from before implementation.
Soft Deletes
When a user “deletes” a record in most business apps, you don’t actually want to remove it from the database. A deleted invoice might still need to appear in financial reports. A deactivated employee’s records need to remain for compliance.
Soft deletes add a deleted_at timestamp column to tables. When a record is “deleted,” you set this timestamp instead of removing the row. Your application queries filter out records where deleted_at is not null, so they disappear from the interface but remain in the database for auditing, reporting, and recovery.
Timestamps on Everything
Every table should have created_at and updated_at columns, populated automatically. These are invaluable for debugging, auditing, reporting, and data analysis. We’ve never encountered a situation where having timestamps was a problem, but we’ve encountered many situations where not having them was.
Planning for Growth
A database that works for 100 users needs to still work for 10,000 users. Here’s how to plan for that.
Think About Query Patterns Early
Before building tables, list the questions your application will need to answer: “Show me all orders from this customer in the last 30 days.” “What’s the total revenue by service category this quarter?” “Which staff members have open tasks due this week?”
These questions determine your table structure, relationships, and indexes. If you design your database around how you’ll query it, performance stays manageable as data volume grows.
Pagination
Never load all records at once. Implement cursor-based or offset pagination from the start. A “show all customers” query that returns 50 rows works fine. That same query returning 50,000 rows will crash your app.
Archive Strategies
Plan for what happens to old data. Do completed orders from three years ago need to be instantly queryable? Or can they be moved to an archive table that’s only accessed for reporting? Keeping active tables lean keeps query performance fast.
Migration Strategies
Your database schema will change over time. New features require new tables, new columns, and sometimes restructuring existing data. Database migrations are how you manage these changes safely.
A migration is a versioned script that modifies your database schema. Each migration has an “up” (apply the change) and a “down” (reverse the change). Tools like Prisma, Knex, TypeORM, and Django’s built-in migrations handle this.
Rules we follow:
- Never modify a migration that’s already been run in production
- Always test migrations against a copy of production data before deploying
- Make migrations small and incremental rather than large and sweeping
- Include data transformations when schema changes require them
Backup and Recovery Basics
Your database is your business. If it disappears, everything built on top of it stops working.
- Automated daily backups at minimum, more frequent for high-transaction applications
- Off-site backup storage so a server failure doesn’t take your backups with it
- Regular restore testing because a backup you’ve never tested restoring is not a backup
- Point-in-time recovery for databases that support it (PostgreSQL’s WAL archiving), allowing you to restore to any moment in time, not just the last backup
How Poor Database Design Creates Technical Debt
Technical debt from bad database design compounds faster than almost any other kind. A poorly designed table doesn’t just cause one problem; it causes problems in every feature that touches that data. Developers work around the bad design with complicated queries, application-layer data transformations, and duplicate data. Each workaround makes the next feature harder to build.
We’ve inherited projects where a database redesign would save months of ongoing development time. The business chose to “just get something working” initially, and years later they’re paying for it in slower feature development, more bugs, and performance problems that can’t be solved without restructuring the data.
The same structural thinking drives our site builds too. Our QCAA Foundation case study shows how we rebuilt a nonprofit’s site around clear, organized content.