Bad database design is one of the most expensive problems in software, and one of the least visible until it is too late. Systems that take 20 seconds to run a report that should take 200 milliseconds. Applications that cannot answer basic business questions because the data was never stored in a way that makes those questions answerable. Customer records that diverge across tables until nobody knows which version is correct. These are not edge cases — they are what happens when database structure is treated as an afterthought.
This post is not a SQL tutorial. It is a guide for understanding why database design decisions made early in a project have consequences that compound over years, and what a well-designed relational database actually looks like.
Why Database Design Is a Business Problem, Not Just a Technical One
Every line of business data your organization generates lives somewhere. How it is organized determines what you can do with it.
A database that stores customer order history as a single text blob per order can tell you that an order existed. It cannot tell you which product lines are most profitable by region without significant data extraction work. A database where addresses are stored inconsistently — sometimes "FL", sometimes "Florida", sometimes "Fla." — cannot generate reliable state-level reports without a cleanup pass every time.
These are design problems, not data problems. The data itself is often fine. The structure around it makes it impossible to use effectively.
We have seen this pattern repeatedly: a system was built quickly to solve an immediate problem, the schema grew organically with each new feature, and three years later the team is spending 30–40% of their development time working around the limitations of a database that was never designed to support what the business actually needs.
The Foundation: Tables, Keys, and Relationships
A relational database organizes data into tables. Each table represents one type of entity — customers, orders, products, invoices — and each row represents one instance of that entity.
The design question is never "should we use tables?" It is which entities deserve their own table, what information belongs in each one, and how those tables connect to each other.
Primary keys are the mechanism that gives every row a unique identity. A customer with ID 4821 is that customer and only that customer, no matter what else changes about the record. Primary keys should be stable, meaningless outside the database (an auto-incremented integer or UUID works; a customer's email address does not), and never reused.
Foreign keys are how relationships are encoded. An order table contains a customer_id column that references the primary key of the customers table. This is not just a convention — when enforced at the database level, a foreign key constraint prevents you from creating an order for a customer who does not exist, or deleting a customer who still has open orders. These constraints are frequently disabled or skipped in favor of handling relationships in application code, which is almost always a mistake. Application code can have bugs. Database constraints cannot be bypassed by a bug in a feature someone wrote on a Friday afternoon.
Candidate keys are any column or combination of columns that could uniquely identify a row. A product might have both an internal ID and a manufacturer SKU that are each unique. The primary key is the one you designate as the official identifier; the others are candidate keys that may warrant unique constraints.
Data Integrity: The Rules That Protect Your Data
Constraints are rules the database enforces on every write, from every application, every API, every script, and every manual admin action. They are the most reliable form of data validation that exists.
NOT NULL prevents a column from being left empty. If an invoice must have a date, a NOT NULL constraint on the invoice_date column ensures that no invoice can be created without one — regardless of whether every application layer that creates invoices remembered to include it.
UNIQUE ensures no two rows can have the same value in a column. Email addresses in a users table should be unique. Product SKUs should be unique. Without a UNIQUE constraint, you are relying entirely on application logic to prevent duplicates, and that reliance will eventually fail.
CHECK constraints enforce business rules at the column level. An order_quantity should be greater than zero. A discount_percentage should be between 0 and 100. A status column should only contain values from a defined set. These rules belong in the database, not scattered across application layers where they can be inconsistently applied or forgotten.
DEFAULT values reduce the risk of null data for columns where a sensible default exists. If all new accounts are active by default, encode that. It reduces application code complexity and the chance of new records being created with missing state.
The business consequence of skipping these constraints is data that cannot be trusted. Reports return wrong totals because quantities were stored as negative numbers. Customer matching fails because email addresses are not normalized and duplicates exist. Financial calculations produce different results depending on which application wrote the record.
Normalization: Eliminating the Redundancy That Causes Problems
Normalization is a framework for organizing data to reduce redundancy and ensure that each fact about the world is stored in exactly one place. When a fact is stored in multiple places, it will eventually be updated in one place but not the others, and the database will contain contradictions.
First Normal Form (1NF) requires that every column contain a single, atomic value. If you are storing a customer's three phone numbers in one column separated by commas, that column violates 1NF. It cannot be queried by individual number, sorted, or validated. The fix is a separate customer_phones table.
Second Normal Form (2NF) applies to tables with composite primary keys: every non-key column must depend on the entire key, not just part of it. A common violation: an order line table with a composite key of order_id + product_id that also stores the product name. The product name depends only on product_id, not on the combination of order and product. When the product name changes, every historical order line needs to be updated — or the data becomes inconsistent.
Third Normal Form (3NF) eliminates transitive dependencies: non-key columns should not depend on other non-key columns. If an employee table stores department_id, department_name, and department_budget together, the department information depends on department_id, not on the employee's primary key. Updating a department's budget now requires updating every row for every employee in that department. Move department data to its own table.
Boyce-Codd Normal Form (BCNF) is a stricter version of 3NF that catches edge cases involving tables with overlapping candidate keys. Most business applications reach a well-designed state at 3NF; BCNF is worth understanding but rarely the practical focus.
The practical outcome of normalization is not theoretical purity — it is systems where data updates propagate correctly, reporting is consistent, and storage is not wasted on duplicated information.
Relationships and What They Mean for Queries
Every relationship between entities falls into one of three categories.
One-to-many is the most common: one customer has many orders, one order has many line items, one department has many employees. These are modeled with a foreign key on the "many" side.
Many-to-many requires a junction table. A product can appear on many orders, and an order can contain many products. The order_items table is the junction: it has a foreign key to orders and a foreign key to products, and the combination of both is the primary key. Without the junction table, you would either need to store multiple order IDs per product or multiple product IDs per order — both of which are violations of 1NF and both of which create querying nightmares.
One-to-one relationships are less common and often indicate information that could be in the same table. When they are intentional — separating sensitive columns like payment credentials or PII into a separate table for access control purposes — they are a legitimate design choice.
Designing relationships correctly at the start is substantially cheaper than untangling them later. We have worked with systems where features that should have taken a week required a month because the data relationships needed to answer the underlying business question had not been modeled.
Indexing: Where Performance Lives
A database without proper indexing is a database that performs acceptably with small data and collapses under real-world load.
When you run a query that filters by customer_id on a table with ten million rows, the database has two options: scan every row in the table, or use an index to jump directly to the relevant rows. A B-Tree index on customer_id turns a full table scan into a lookup that takes milliseconds instead of seconds.
B-Tree indexes are the standard. They support equality queries, range queries, and sorting. Columns that appear in WHERE clauses, JOIN conditions, and ORDER BY clauses are candidates for indexing.
Composite indexes index multiple columns together. An index on (customer_id, created_at) will accelerate queries that filter by customer and date range simultaneously. Column order in composite indexes matters: the index is most useful when queries filter on the leftmost columns first.
Full-text indexes are built for text search — finding rows where a description contains specific words. They work differently from B-Tree indexes and are appropriate for search features, not for filtering on exact column values.
The penalty for over-indexing is write performance. Every insert, update, and delete must update every index on the table. Tables with 20 indexes on them will exhibit noticeably slower writes. The right approach is to index strategically: start with foreign keys and the columns most commonly used in filtering, measure, and add indexes where query analysis shows they are needed.
Denormalization: When to Break the Rules Deliberately
Normalization is the right default. Denormalization is a deliberate departure from it for specific performance reasons, not a license to skip the design work.
The classic case: a fully normalized schema requires four table joins to produce a report. The report runs dozens of times per day and has become slow. Adding a precomputed summary column or a dedicated reporting table — accepting some data redundancy in exchange for query speed — can be a reasonable trade.
Denormalization increases write complexity (you now have to update data in multiple places when it changes), risks consistency (the redundant data can fall out of sync), and makes the schema harder to understand. It should be applied surgically, to specific bottlenecks, after you have confirmed through measurement that the normalized design cannot be made fast enough through indexing and query optimization alone.
Skipping normalization upfront because "we'll just cache it" is a different problem. It defers the real design work and usually results in the consistency issues normalization was specifically designed to prevent.
Security: What Lives in the Database Matters
Access control belongs at the database level, not just at the application level. Database users and roles should be granted only the permissions they need. A read-only reporting connection has no business being able to drop tables. An application service account should not have permission to modify schema.
Passwords must be hashed using a slow hashing algorithm like bcrypt or Argon2, not MD5, not SHA-1, and not stored in plaintext. This is non-negotiable. When a database is compromised — and enough of them are that this scenario deserves to be in your threat model — hashed passwords limit the damage. Plaintext passwords are an instant, complete credential leak for every user in the system.
Sensitive data — Social Security numbers, card numbers, health records — that must be stored at all should be encrypted at the column level, with keys managed separately from the database itself. The compliance requirement is not the only reason to do this; it is also the right technical control.
The Long-Term Cost of Getting It Wrong
A poorly designed database does not usually fail immediately. It degrades. Queries that ran in 50 milliseconds take 5 seconds after a year of data growth. Reports that were fast become overnight batch jobs. Features that should take two weeks to build require two months because the data relationships are not structured to support them.
The systems that are most expensive to work on are almost never the technically ambitious ones. They are the ones where someone made pragmatic shortcuts in the database schema early on, and the business scaled on top of those shortcuts until reversing them became a major project.
Getting the design right at the start — clear entities, enforced constraints, proper normalization, deliberate indexing — is the lowest-cost path to a system that scales with your business instead of fighting it.
