Database Best Practices: Schema Design, Indexing, Transactions & Migrations

Application code can be rewritten in a weekend. A badly designed database is much harder to fix, because it holds years of real data and a dozen things depend on its shape. The good news is that most database problems come from the same handful of habits, and the fixes are not complicated. This guide covers the practices that matter most, whether you use PostgreSQL, MySQL, SQL Server or something else relational.

1. Design the schema around your data, not your screens

Start by listing the real things your system stores (users, orders, products) and how they relate. Give each its own table, with one clear meaning per column. A useful test: if you find yourself storing the same fact in two places, or putting comma-separated values in one column, the design needs another table.

A short guide to normalisation

One value per column. Do not store "red,green,blue" in a single field. Create a related table instead.
Every column depends on the key. If a customer's address is repeated on every order row, move it to a customers table and reference it.
Denormalise only on purpose. Copying data for read speed is fine, but do it deliberately, after measuring, and know how you will keep the copies in sync.

2. Let the database enforce the rules

Validation in your application is necessary, but it is not enough. Another service, a script or a future teammate can write to the database without going through your code. Constraints make bad data impossible, not just unlikely.

CREATE TABLE orders (
    id           BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  BIGINT       NOT NULL REFERENCES customers (id),
    status       VARCHAR(20)  NOT NULL CHECK (status IN ('pending', 'paid', 'shipped', 'cancelled')),
    total_cents  INTEGER      NOT NULL CHECK (total_cents >= 0),
    created_at   TIMESTAMP    NOT NULL DEFAULT CURRENT_TIMESTAMP
);

ALTER TABLE customers ADD CONSTRAINT uq_customers_email UNIQUE (email);

Primary keys on every table.
Foreign keys so you cannot create an order for a customer that does not exist.
NOT NULL wherever a value is genuinely required.
UNIQUE for things like email addresses.
CHECK for simple rules such as non-negative amounts.

3. Pick sensible data types

Money: never use floating point. Store whole cents in an integer, or use a DECIMAL type with a fixed scale.
Dates and times: use real date/time types, not strings. Store timestamps in UTC and convert for display.
Identifiers: auto-increment integers are compact and fast; UUIDs are handy when IDs are created in several places. Either is fine; just be consistent.
Text: choose lengths that reflect reality instead of a blanket large value everywhere.

4. Index with intent

An index is like the index of a book: it lets the database jump to the right rows instead of reading the whole table. Without a useful index, a query on a million rows scans a million rows.

Where indexes help

Columns used in WHERE filters, joins and ORDER BY.
Foreign key columns. Many databases do not create these automatically, and joins on them are everywhere.

CREATE INDEX idx_orders_customer_created
    ON orders (customer_id, created_at DESC);

Column order in a composite index matters. The index above serves "all orders for this customer, newest first" very well, but it cannot help a query that filters only on created_at.

Where indexes hurt

Every index speeds up reads and slows down writes, and takes disk space. Do not index everything "just in case". Add indexes for queries you actually run, and drop the ones nothing uses.

5. Read the query plan instead of guessing

When a query is slow, ask the database what it is doing. Most systems offer EXPLAIN (and EXPLAIN ANALYZE in PostgreSQL, which also runs the query and shows real timings).

EXPLAIN ANALYZE
SELECT id, total_cents
FROM orders
WHERE customer_id = 42
ORDER BY created_at DESC
LIMIT 20;

Look for a full table scan ("Seq Scan") on a large table, a big gap between estimated and actual row counts, and expensive sorts. That output will usually tell you exactly which index is missing.

6. Write queries that scale

Select only the columns you need. SELECT * pulls unnecessary data and breaks when the table changes.
Always paginate. Use LIMIT. For deep pages, prefer keyset pagination (WHERE id < last_seen_id) over a large OFFSET, which still has to walk past all the skipped rows.
Avoid the N+1 problem. Running one query to get 100 orders, then 100 more to get each customer, is 101 round trips. Use a join or a single query with IN.
Do work in the database, not in a loop. A single UPDATE ... WHERE beats fetching thousands of rows and updating them one at a time.
Do not wrap indexed columns in functions inside WHERE. WHERE LOWER(email) = ... cannot use a normal index on email.

7. Use transactions for anything that must succeed together

Transferring money between accounts is two updates. If the app crashes between them, you have lost money. A transaction makes them one all-or-nothing unit.

BEGIN;

UPDATE accounts SET balance_cents = balance_cents - 5000 WHERE id = 1;
UPDATE accounts SET balance_cents = balance_cents + 5000 WHERE id = 2;

COMMIT;   -- or ROLLBACK if anything went wrong

Keep transactions short. A transaction held open while you call an external API or wait for a user holds locks and blocks other work.

8. Never build SQL by joining strings

SQL injection is still one of the most common serious vulnerabilities, and the cure is simple: use parameterised queries. The database receives the query and the values separately, so user input can never change the meaning of the statement.

// Dangerous
const sql = "SELECT * FROM users WHERE email = '" + email + "'"

// Safe
const result = await db.query("SELECT * FROM users WHERE email = $1", [email])

ORMs and query builders do this for you, but check any place you drop to raw SQL.

9. Change the schema safely with migrations

Do not edit the production database by hand. Keep every schema change as a numbered, version-controlled migration script that runs the same way in development, staging and production. Tools such as Flyway, Liquibase, Prisma Migrate, EF Core migrations or Alembic handle ordering and tracking.

For live systems, prefer changes that are safe while the old code is still running:

Add the new column as nullable (or with a default).
Deploy code that writes to both the old and new column.
Backfill existing rows in small batches.
Switch reads to the new column.
Remove the old column in a later release.

It takes more steps than a single rename, but nobody sees an outage.

10. Protect the data

Back up automatically, and test the restore. A backup you have never restored is only a hope. Schedule a periodic restore drill.
Use least privilege. Your web app needs to read and write its tables, not drop them. Give it a limited account, and keep an admin account for migrations only.
Never store passwords in plain text. Hash them with a slow, purpose-built algorithm such as Argon2, bcrypt or scrypt, not a plain SHA hash.
Encrypt in transit, and consider encryption at rest for sensitive columns.
Keep secrets out of source control. Connection strings belong in environment variables or a secret manager.
Consider soft deletes carefully. A deleted_at column lets you recover from mistakes but must be filtered in every query; decide on one approach and apply it consistently.

11. Manage connections and keep an eye on it

Opening a database connection is expensive. Use a connection pool and size it sensibly; a pool far larger than the database can handle just moves the queue somewhere worse. Then monitor what you run: slow query logs, connection counts, disk usage and replication lag. Most outages are visible in these numbers well before they happen.

12. Name things consistently

Pick a convention and stick to it. A common, readable one is snake_case for tables and columns, plural table names (orders), a primary key named id and foreign keys named customer_id. Predictable names mean fewer mistakes and less time spent reading documentation.

Common mistakes, at a glance

Storing lists in one column instead of a related table.
Using floating-point numbers for money.
No foreign keys "for performance", then discovering orphaned rows.
Indexes missing on foreign keys and filter columns, or indexes on everything.
Loading entire tables into application memory to filter them.
Editing production schemas by hand with no record of what changed.
Backups that are never tested.

The takeaway

Good database work is mostly discipline: model the data clearly, let constraints guard it, index the queries you really run, wrap related changes in transactions, and treat the schema as code that is versioned and reviewed. None of that is glamorous, and all of it prevents the late-night incident where something that "worked fine in development" meets a million real rows.

Database Best Practices Every Developer Should Know