Database Design Best Practices for Web Apps

Related: What Is an API? A Plain Guide for Business

A web app is only as reliable as the data model underneath it. Get the schema right early and features are easy to add, queries stay fast, and reports are trustworthy. Get it wrong and every new feature fights the structure, bugs creep in through inconsistent data, and performance falls off a cliff as tables grow. This guide covers the database design choices that matter most for web apps in 2026, with concrete rules you can apply on your next build.

Key takeaways

• Model the real-world entities and their relationships first; the tables follow from that.
• Give every table a stable primary key and enforce relationships with foreign keys.
• Normalize to remove duplicated data, then denormalize only where a proven read path needs it.
• Index the columns you filter, join, and sort on, and no more; each index costs write time and space.
• Treat schema changes as code: use versioned migrations so every environment stays in sync.
• Pick SQL by default for web apps; reach for NoSQL only when the access pattern truly calls for it.

Start with the data model, not the tables

Before you create a single table, list the things your app deals with: users, orders, products, invoices, comments, projects. These are your entities. Then describe how they relate. A user places many orders. An order contains many line items. A project has many tasks and many members. Getting these relationships clear on paper saves days of rework later.

Three relationship shapes cover almost everything:

Relationship	Example	How it is stored
One to one	User to profile settings	Shared or foreign key on one side
One to many	Customer to orders	Foreign key on the many side
Many to many	Students to courses	A join table linking both keys

Name tables and columns consistently. Pick one convention, such as snake_case plural table names and singular column names, and apply it everywhere. Consistency is not cosmetic; it removes a whole class of small mistakes when people write queries months later.

Keys and relationships

Every table needs a primary key that uniquely identifies a row and never changes. For most web apps an auto-incrementing integer or a UUID works well. Integers are compact and fast to join. UUIDs are handy when you generate ids on the client, merge data from several sources, or do not want row counts leaking in public URLs. Never use a value that can change, like an email address, as the primary key.

Use foreign keys to link related tables and let the database enforce them. A foreign key stops an order from pointing at a customer who does not exist, making your data trustworthy by design. Decide delete behavior deliberately: should deleting a customer delete their orders, block the delete, or set the reference to null? Spelling this out prevents orphaned rows and surprise data loss.

CREATE TABLE orders (
  id BIGSERIAL PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(id),
  status VARCHAR(20) NOT NULL DEFAULT 'pending',
  total_cents INTEGER NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

Notice the money column is stored as an integer count of cents, not a floating point number. Floats lose precision and cause rounding errors in totals. Store money as integer minor units or a fixed decimal type, and store every timestamp with a time zone so a bare date is never ambiguous.

Normalize first, denormalize on purpose

Normalization means storing each fact in exactly one place. A customer address lives in the customers table, not copied onto every order. Change the address once and every query sees the update. For most web apps, aiming for what is called third normal form is a sensible default that removes the duplication behind contradictory records.

Denormalization is the deliberate opposite: copying or precomputing data to make a specific read faster. It is a valid tool, but a trade, not a starting point. Only denormalize when you have a real, measured read path that is too slow, and accept that you now own the job of keeping the copies in sync. A safe example is storing an order_total on the order row so a list view need not sum line items every time, as long as a trigger or application code keeps it correct.

• Normalize for correctness and easy writes.
• Denormalize for a proven slow read, and document why.
• Never denormalize on a guess. Measure first.

Indexing for speed

An index is a lookup structure that lets the database find rows without scanning the whole table. Add one to any column you frequently filter by, join on, or sort by. Foreign key columns almost always deserve an index because you join on them constantly. A query that filters orders by customer_id and status is far faster with an index covering those columns.

Indexes are not free. Each one takes disk space and must be updated on every write, so more indexes mean slower inserts and updates. The goal is enough indexes to serve your real queries and no vanity extras. A few rules that hold up well:

• Index the columns in your common WHERE, JOIN, and ORDER BY clauses.
• For multi-column filters, a single composite index often beats several single-column ones, and column order matters.
• Add a unique index to enforce values that must not repeat, such as a username or an email.
• Use your database EXPLAIN command to confirm a query actually uses the index you expect.

When you design an API on top of this data, the queries it runs shape which indexes you need. Our guide to REST API design best practices covers how endpoint patterns and pagination map onto database reads so the two stay in step.

Migrations, integrity, and scaling

Treat schema changes exactly like code: written as versioned migration files, reviewed, committed to your repository, and run in order in every environment. Never edit a production schema by hand. A migration tool applies the same steps to your laptop, staging, and production, so the three never drift apart and a new team member can build the whole database from scratch with one command.

Push as much data integrity into the database as you can. NOT NULL, UNIQUE, and CHECK constraints catch bad data at the source, no matter which service or script wrote it. Relying only on application code fails the moment a second app, a background job, or a manual fix touches the same tables.

On scaling, resist doing it before you need to. A well-indexed relational database on modern hardware handles millions of rows comfortably. When you do grow, the usual order is: add the right indexes, add a read replica for read-heavy traffic, add caching for hot lookups, and only then consider partitioning or sharding. Each step adds complexity, so take one rung at a time and measure the effect before the next.

FAQ

Should I use SQL or NoSQL for my web app?

Default to a relational SQL database such as PostgreSQL or MySQL. They give you strong consistency, flexible queries, and mature tooling that suits most web apps. Choose a NoSQL store only when your access pattern is a strong fit, such as very high write volume with simple key lookups, or documents whose shape varies a lot. Many teams mix both: SQL for core records and a specialized store for one job like search or caching.

What is the most common database design mistake?

Two stand out. The first is duplicating the same fact in many places, which leads to records that disagree with each other. The second is missing indexes on the columns used in joins and filters, which makes pages slow as data grows. Both are cheap to avoid at design time and painful to fix once the app is live and full of data.

How do I change a schema without downtime?

Make changes additive and gradual. Add a new nullable column or table first, backfill data in batches, switch the application to use it, and only then remove the old shape in a later migration. Avoid a single large blocking change on a big table during peak hours. This expand then contract approach lets old and new code run side by side during a deploy.

Working with Apex Logic

Apex Logic designs and builds the databases behind web apps, SaaS products, and internal tools. We model your data around how the business really works, set up keys, constraints, and indexes that keep queries fast, and put versioned migrations in place so your schema is safe to change as you grow. If you are starting a new build or wrestling with a slow or tangled database, see our services or get in touch and we will help you get the foundation right.

References

PostgreSQL documentation, official guides on data types, constraints, and indexing.

Use of Time Zones and money storage: standard relational database design guidance on precise numeric and timestamp types.

Apex Logic project experience building and maintaining production web app databases.