Bad queries are annoying; bad schemas are expensive. A slow query can be rewritten in an afternoon, but a wrong column type or a missing key gets baked into millions of rows and years of application code. This guide walks ten design mistakes that keep showing up in real databases, why each one hurts, and the DDL that fixes it.
This is deliberately not a list about writing queries - for that see common SQL mistakes. Here we stay one level lower, at the DDL that shapes your tables in the first place. Get the schema right and half the "hard" query problems never appear.
What this guide covers
- No primary key
- Wrong data types
- Missing foreign keys & constraints
- Many values in one column
- Over- vs under-normalization
- EAV abuse
- Indexes: too few, too many
- Inconsistent naming
- Ignoring NULL and defaults
- No UTC / timezone strategy
- No audit timestamps
- No soft-delete or history plan
- Premature sharding & denormalization
1. No primary key
Every table needs a reliable way to identify a single row. Without a primary key you cannot update or delete one specific record safely, replication can duplicate rows, and other tables have nothing stable to reference. "We just never insert duplicates" is a hope, not a constraint.
Use a surrogate key (an auto-generated integer or UUID) as the identity, and add a UNIQUE constraint for any real-world natural key such as an email or SKU.
-- nothing identifies a row; duplicates are possible
CREATE TABLE users (
email VARCHAR(255),
fullname VARCHAR(120)
);CREATE TABLE users (
id BIGINT AUTO_INCREMENT PRIMARY KEY,
email VARCHAR(255) NOT NULL UNIQUE,
fullname VARCHAR(120) NOT NULL
);A surrogate key stays stable even if the business rule changes (people do change email addresses). The UNIQUE constraint still guarantees no two rows share an email. Concretely, imagine a support agent needs to delete one duplicate account: with no key, DELETE FROM users WHERE email = ? removes both rows, and there is no way to keep exactly one. Storage engines like InnoDB also build the physical row layout around the primary key, and logical replication needs one to identify which row a change applies to - a keyless table can silently double its rows on a replica. The natural key alone is not enough either: emails, usernames and phone numbers all get reassigned in the real world, so leaning on them as the sole identity eventually corrupts your foreign key references.
2. Wrong data types
Storing everything as VARCHAR feels flexible, but it throws away everything the database knows how to do for you: range checks, correct sorting, date math, and compact storage. The two classic offenders are dates-as-text and money-as-float.
Dates stored as strings sort alphabetically ('2026-1-9' lands after '2026-10-1') and cannot use date functions. Money stored as FLOAT or DOUBLE suffers binary rounding - 0.1 + 0.2 is not exactly 0.3 - which is unacceptable for currency.
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
ordered_on VARCHAR(20), -- '04/07/2026'? '2026-7-4'? who knows
amount FLOAT, -- rounding errors on money
quantity VARCHAR(10) -- can't SUM() text reliably
);CREATE TABLE orders (
id BIGINT PRIMARY KEY,
ordered_on DATE NOT NULL,
amount DECIMAL(12,2) NOT NULL, -- exact to the cent
quantity INT NOT NULL
);Watch out: always use DECIMAL / NUMERIC for money, never FLOAT. The rounding is invisible in tests and shows up as pennies that don't reconcile in production.
The real cost lands months later. A ordered_on VARCHAR column quietly accumulates three date formats because different code paths wrote it differently, and now a "sales this quarter" report either misses rows or throws conversion errors. A FLOAT total means an invoice reads $19.99 in the app but sums to $19.989999999 in a nightly reconciliation job, and finance opens a ticket you cannot close without a migration. Right-sizing types also matters: an INT auto-increment key caps out near 2.1 billion rows, and a busy events table can hit that ceiling and start failing inserts at 3am - which is why high-volume keys use BIGINT.
3. Missing foreign keys & constraints
A foreign key is a promise the database enforces: this customer_id must point at a real customer. Relying on application code to keep references valid means every bug, every ad-hoc script, and every parallel service is one chance to create an orphan row. Declared constraints hold no matter what touches the data.
-- customer_id is just a loose number, nothing guards it
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT,
amount DECIMAL(12,2)
);CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL,
amount DECIMAL(12,2) NOT NULL CHECK (amount >= 0),
CONSTRAINT fk_orders_customer
FOREIGN KEY (customer_id) REFERENCES customers(id)
ON DELETE RESTRICT
);CHECK constraints catch impossible values (a negative order total), and the foreign key's ON DELETE rule makes the relationship's lifecycle explicit instead of accidental. Learn more in the database concepts guide.
The argument against foreign keys is usually "they slow down writes" or "the ORM handles it." Both are traps. A foreign key check is a single indexed lookup, negligible next to the cost of the orphaned-data cleanup jobs teams end up writing without one. And "the app handles it" holds only until the second app, the migration script, the bulk import, or the 2am hotfix touches the table - each is a fresh chance to insert an order pointing at a customer that was already deleted. Once orphans exist, joins silently drop rows, counts disagree between screens, and you cannot even trust ON DELETE to clean up because the references were never real. Deciding the delete behavior up front - RESTRICT to block, CASCADE to follow, SET NULL to detach - encodes the business rule where every writer must obey it.
4. Storing many values in one column
Cramming a comma-separated list into a single column - tags = 'red,sale,new' - breaks the first rule of relational design: one cell, one value. You cannot index it, joining is impossible, and every query needs fragile string matching that also finds false positives (LIKE '%new%' matches 'renew').
CREATE TABLE products (
id BIGINT PRIMARY KEY,
name VARCHAR(120),
tags VARCHAR(255) -- 'red,sale,new'
);-- a proper many-to-many via a junction table
CREATE TABLE tags (
id BIGINT PRIMARY KEY,
name VARCHAR(60) NOT NULL UNIQUE
);
CREATE TABLE product_tags (
product_id BIGINT NOT NULL REFERENCES products(id),
tag_id BIGINT NOT NULL REFERENCES tags(id),
PRIMARY KEY (product_id, tag_id)
);Now "find every product tagged sale" is a clean, indexed join, and renaming a tag is a single-row update instead of a search-and-replace across every product string. The delimited-list version fails in ways that are hard to see until data grows: WHERE tags LIKE '%new%' also matches 'renewed' and 'newsletter', and it can never use an index, so the query gets linearly slower with every product. Counting how many products carry each tag becomes a string-parsing exercise instead of a GROUP BY. And there is no way to attach data to the relationship - the moment you need "when was this tag applied" or "who applied it," the CSV column has nowhere to put it, while the junction table just gets another column.
5. Over- vs under-normalization
Under-normalization repeats the same fact in many rows - store a customer's address on every order and one move means updating hundreds of rows, some of which you'll miss. Over-normalization splits data so aggressively that a simple read needs six joins and the model becomes painful to work with.
The practical target for transactional systems is third normal form: every non-key column depends on the key, the whole key, and nothing but the key. Learn the rules step by step in the database normalization guide, then relax them deliberately - for example, keeping a denormalized total on an invoice for reporting speed - only where you can measure the win.
-- customer details duplicated on every order row
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_name VARCHAR(120),
customer_email VARCHAR(255),
customer_city VARCHAR(80),
amount DECIMAL(12,2)
);-- each fact lives in exactly one place
CREATE TABLE customers (
id BIGINT PRIMARY KEY,
name VARCHAR(120) NOT NULL,
email VARCHAR(255) NOT NULL UNIQUE,
city VARCHAR(80)
);
CREATE TABLE orders (
id BIGINT PRIMARY KEY,
customer_id BIGINT NOT NULL REFERENCES customers(id),
amount DECIMAL(12,2) NOT NULL
);6. EAV abuse
The entity-attribute-value pattern stores data as rows of (entity, attribute_name, value) so you can add "any attribute" without changing the schema. It sounds flexible but it destroys everything relational: no data types (every value is text), no per-attribute constraints, no simple joins, and reconstructing one logical record means pivoting many rows.
CREATE TABLE product_attrs (
product_id BIGINT,
attr_name VARCHAR(60), -- 'color', 'weight_kg', 'in_stock'
attr_value VARCHAR(255) -- everything is a string
);CREATE TABLE products (
id BIGINT PRIMARY KEY,
color VARCHAR(30),
weight_kg DECIMAL(6,2),
in_stock BOOLEAN NOT NULL DEFAULT FALSE
);The concrete pain of EAV shows up in queries. "Find red products under 2kg that are in stock" needs three self-joins against product_attrs, one per attribute, and the database cannot enforce that weight_kg is even a number - someone will store 'heavy' and the report will crash on cast. There is no NOT NULL, no CHECK, no default, and no foreign key on the values. Teams that adopt EAV early because "requirements will change" almost always end up rebuilding the model into real columns once the attribute set stabilizes, which it always does.
If attributes really are open-ended and sparse, a native JSON/JSONB column is a far better escape hatch than EAV: it keeps one row per entity and most engines can index inside it. Reserve genuine EAV for narrow cases like user-defined custom fields, and never make it the backbone of your model.
7. Indexes: too few, too many
Two opposite mistakes. The common one is no index on foreign keys or filter columns, so every join and WHERE becomes a full table scan that gets slower as the table grows. The rarer one is indexing everything, which slows down every INSERT, UPDATE and DELETE (each write must maintain every index) and wastes storage.
-- index the FK you join on and the column you filter by
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status_date
ON orders(status, ordered_on); -- composite for common filterIndex the columns you actually join and filter on, put the most selective column first in a composite index, and drop indexes nothing uses. The SQL indexes guide covers selectivity and composite ordering in depth.
The missing-index symptom is a dashboard that was instant in development and takes eight seconds in production, because the orders table grew from a thousand test rows to ten million real ones and the unindexed customer_id join now scans them all on every page load. The opposite symptom is subtler: a table with fifteen indexes where bulk imports crawl and disk usage balloons, because each of those indexes must be rewritten on every insert. A useful discipline is to review index usage stats periodically and remove any index the planner never chooses - an index that is never read but always written is pure overhead.
8. Inconsistent naming
Naming is not cosmetic. When one table has userid, another has user_ID, and a third has fk_user, every join is a lookup and every new developer second-guesses the schema. Pick one convention and apply it everywhere.
- Case: stick to
snake_case; it survives case-insensitive engines and quoting rules. - Tables: pick singular or plural and never mix; be consistent.
- Keys: primary key
id, foreign key<table>_id(e.g.customer_id). - Booleans: prefix with
is_orhas_(is_active). - Reserved words: avoid column names like
order,user, ortypethat force quoting.
9. Ignoring NULL semantics and defaults
NULL means "unknown", not "zero" or "empty string" - and it behaves differently everywhere: it fails equality checks, aggregate functions skip it, and it can slip past a UNIQUE constraint. Leaving every column nullable pushes that ambiguity into every query. Mark columns NOT NULL unless absence is genuinely meaningful, and give sensible defaults.
CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
balance DECIMAL(12,2), -- NULL balance? is that 0 or unknown?
is_active BOOLEAN -- three states: true, false, NULL
);CREATE TABLE accounts (
id BIGINT PRIMARY KEY,
balance DECIMAL(12,2) NOT NULL DEFAULT 0,
is_active BOOLEAN NOT NULL DEFAULT TRUE
);Decide deliberately for each column whether NULL carries meaning. "The middle name is unknown" is a real NULL; "the balance is nothing yet" is a 0 default. The bugs NULL causes are quietly wrong rather than loud: SELECT SUM(balance) skips the NULL rows, so a total silently understates; WHERE discount <> 0.10 excludes rows where discount is NULL even though those clearly are not 10%; and COUNT(is_active) counts only the non-NULL flags. A nullable boolean is really three states, and app code that assumes two will mishandle the third. Most engines also allow multiple NULLs past a UNIQUE constraint, so a "unique" nullable column can hold a hundred NULL duplicates. Every one of these is a data-quality incident that no error message announces.
10. No UTC / timezone strategy
Storing local wall-clock time with no zone is a time bomb. Servers move regions, users span the globe, and daylight-saving transitions make some local times ambiguous or nonexistent. Without a single strategy, timestamps quietly drift and reports across regions never line up.
The reliable rule: store every timestamp in UTC using a timezone-aware type, and convert to the user's zone only when displaying. Keep a plain DATE for things that are genuinely date-only (a birthday), where a time and zone would be misleading.
-- local time, no zone: meaning depends on the server
created_at DATETIME-- store UTC, timezone-aware; convert on display
created_at TIMESTAMP WITH TIME ZONE
NOT NULL DEFAULT CURRENT_TIMESTAMPThe failure is always a support ticket you cannot reproduce: a report shows an order placed "tomorrow," a scheduled job fires an hour early after a clock change, or two events that happened seconds apart sort in the wrong order because they were written by servers in different regions. Naive local time also loses information permanently - once you have stored 02:30 on a spring-forward night, there is no way to know which side of the transition it was on. Storing UTC everywhere removes the ambiguity, and display-time conversion is a solved problem in every language.
11. No audit timestamps
Skipping created_at and updated_at feels harmless until the first time you have to answer "when did this record change, and is my cache stale?" Without them you cannot debug data issues by timeline, cannot build incremental syncs that pull "everything changed since X," and cannot even sort records by age. Adding the columns later means backfilling millions of rows with a guessed value, because the real timestamps are gone forever.
-- no way to know when a row was born or last touched
CREATE TABLE invoices (
id BIGINT PRIMARY KEY,
total DECIMAL(12,2) NOT NULL
);CREATE TABLE invoices (
id BIGINT PRIMARY KEY,
total DECIMAL(12,2) NOT NULL,
created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
);Make these two columns a default part of every table template. Many engines can maintain updated_at automatically with an ON UPDATE CURRENT_TIMESTAMP clause or a trigger, so the app never has to remember. The storage cost is a few bytes per row; the debugging and analytics value is enormous.
12. No soft-delete or history plan
A hard DELETE is irreversible, and the day a customer says "you removed my account by mistake, restore it" you discover the row is simply gone, along with every order that referenced it. The opposite mistake is soft-deleting everything with an is_deleted flag but forgetting that every single query now has to filter it out - miss the filter once and deleted rows reappear on a screen or in a total.
-- keep the row, mark when it was retired
ALTER TABLE customers
ADD COLUMN deleted_at TIMESTAMP WITH TIME ZONE NULL;
-- active rows only
SELECT * FROM customers WHERE deleted_at IS NULL;A nullable deleted_at is better than a boolean because it records when as well as whether. Decide the strategy per table: a soft delete for anything a user might need restored or that other records reference, a genuine hard delete for high-volume, disposable data like expired sessions. For data you must be able to reconstruct point-in-time - prices, permissions, contracts - go further and keep a history table that records each version, rather than overwriting the row in place. The key point is to decide deliberately; a schema with no deletion story at all is the one that loses data.
13. Premature sharding & denormalization
The last mistake is over-engineering for scale you do not have. Splitting a table across shards, duplicating columns "to avoid a join," or caching aggregates in extra columns all buy performance at the cost of correctness and complexity. Every denormalized copy is a value that can drift out of sync with its source, and once data is sharded you lose cross-shard transactions, foreign keys, and simple joins - problems most applications never needed to take on.
Rule of thumb: normalize first, measure with real data, and denormalize only the specific hot path a benchmark proves is slow. A well-indexed join on a modern database handles millions of rows comfortably - reach for sharding when a single node genuinely cannot hold or serve the data, not before.
When you do denormalize deliberately - say, storing a cached order_count on customers to avoid counting on every page - treat the derived column as owned by a trigger or a single well-tested code path, never updated ad hoc from many places. That keeps the copy honest. Premature optimization here is expensive precisely because it is structural: unwinding a shard layout or reconciling a drifted denormalized column is exactly the kind of migration this whole guide is about avoiding.
The mistakes at a glance
Each design mistake trades a little convenience today for a large, compounding cost later. Here they are side by side.
| Mistake | Consequence | Fix |
|---|---|---|
| No primary key | Duplicate rows, can't target one record | Surrogate key + UNIQUE natural key |
| Wrong data types | Bad sorting, rounding errors, broken math | DATE, DECIMAL, INT as appropriate |
| No foreign keys | Orphan rows, integrity depends on app code | Declare FOREIGN KEY and CHECK |
| CSV in a column | No indexing, fragile LIKE matching | Related / junction table |
| Under-normalized | Duplicated facts, update anomalies | Normalize to 3NF |
| EAV abuse | No types, no constraints, painful joins | Typed columns or JSON |
| Missing indexes | Full scans on joins and filters | Index FKs and filter columns |
| Inconsistent naming | Confusion, slow onboarding, bugs | One convention everywhere |
| Ignoring NULLs | Ambiguous data, wrong aggregates | NOT NULL + sensible defaults |
| No UTC strategy | Timestamps drift across regions | Store UTC, convert on display |
| No audit timestamps | Can't debug or sync by time | Add created_at / updated_at |
| No delete strategy | Lost data or leaked "deleted" rows | Soft-delete or history where needed |
| Premature sharding | Drifted copies, lost joins & constraints | Normalize, measure, then optimize |
Which mistakes hurt most later
Not every mistake ages the same way. The chart below is a representative view of how much pain each one tends to cause once a schema is in production, weighing how hard it is to unwind and how much data and code it touches. Structural mistakes near the top are the ones worth extra care up front.
Notice the pattern: the cheapest to fix later - missing indexes, naming - can be changed without touching data. The expensive ones change the shape of your data, which means migrating every existing row and every query that reads it.
Schema mistakes are the most expensive bugs you can ship. An index can be added tonight; changing a column type on a billion-row table, or splitting a CSV column into a related table, means a careful data migration and rewrites across the whole application. Spend the extra hour on the design before the first migration runs.
A pre-migration checklist
Before you run CREATE TABLE on anything real, walk this list.
Every table has a primary key
A surrogate key for identity, plus a UNIQUE constraint on the real natural key.
Types match the data
Dates as DATE/TIMESTAMP, money as DECIMAL, counts as INT - never text stand-ins.
Relationships are declared
Foreign keys and CHECK constraints enforce integrity in the database, not just in app code.
One value per cell
No delimited lists; use related tables for one-to-many and many-to-many.
Normalized, then indexed
Reach 3NF, then index the foreign keys and filter columns your queries actually use.
Nullability and time are intentional
Every column's NOT NULL and default is a decision; every timestamp is UTC.
Good schema design is not about memorising rules - it is about making each of these choices on purpose. If you want to go deeper, the database concepts and DDL commands guides cover the building blocks, and a SQL developer can review a design before it hardens into production.