Common Database Design Mistakes

A database schema is one of the hardest parts of an application to change later. Code can be refactored in an afternoon; a table with five years of data and twenty dependent reports cannot. That is why database design mistakes made in the first weeks of a project tend to haunt it for years. The good news is that most of them are well known and easy to avoid if you spot them early. Here are ten we encounter regularly, each with its fix.

1. Storing money as a floating-point number

FLOAT and DOUBLE store approximate binary values. They cannot represent many decimal amounts exactly, so totals drift by fractions of a paisa or cent, and reconciliations fail in ways that are maddening to track down.

-- Wrong
price FLOAT

-- Right: exact decimal with fixed scale
price DECIMAL(12,2) NOT NULL

Alternatively, store amounts as integers in the smallest currency unit (paise or cents). Either way, also store the currency code if you will ever deal with more than one currency.

2. Using the wrong data types

Dates stored as text ('03/10/2024') cannot be sorted or compared reliably, and nobody knows whether that is 3 October or 10 March. Numbers stored as text sort as "1, 10, 2". Booleans stored as "Y", "yes", "1" and "true" in the same column defeat every query.

Use native types: DATE, TIMESTAMP, INTEGER, BOOLEAN (or TINYINT(1) in MySQL). The database then validates values for you and indexes them efficiently.

One exception runs the other way: phone numbers, postcodes and identity numbers look numeric but are not quantities. Leading zeros matter and you never add them together, so store them as text with validation.

3. No foreign keys

Some teams skip foreign key constraints "for speed" or because the framework "handles relationships". The result, sooner or later, is orphaned records: order lines pointing at deleted orders, payments for customers who no longer exist.

ALTER TABLE order_items
    ADD CONSTRAINT fk_items_order
    FOREIGN KEY (order_id) REFERENCES orders(id)
    ON DELETE RESTRICT;

Foreign keys document relationships, protect integrity and help query planners. The overhead is small for typical business workloads. Remember to index the referencing column too.

4. Lists packed into a single column

A column like tags = 'urgent,export,priority' or product_ids = '12,48,301' seems convenient until you need to find every order containing product 48, count tag usage, or rename a tag. Queries become fragile LIKE '%48%' searches that also match 148 and 480.

Use a separate linking table, often called a junction table:

CREATE TABLE order_tags (
    order_id INT NOT NULL REFERENCES orders(id),
    tag_id   INT NOT NULL REFERENCES tags(id),
    PRIMARY KEY (order_id, tag_id)
);

5. Making every column nullable

If every column allows NULL, the database cannot help you enforce that an invoice has a date or that a customer has a name. Every query must then guard against missing values. Make columns NOT NULL by default and allow nulls only where "unknown" or "not applicable" is a real, meaningful state. Add CHECK constraints for simple rules such as quantity > 0.

6. Ignoring time zones

An application stores local server time, then moves to a cloud server in another region, or starts serving customers in other countries. Suddenly "today's orders" includes some from yesterday. Store timestamps in UTC (or in a time-zone-aware type such as PostgreSQL's TIMESTAMPTZ) and convert to the user's local time only for display. Decide this before the first row is written; converting historical data later is painful.

7. The "one table for everything" model

The entity-attribute-value (EAV) pattern stores data as rows of (entity_id, attribute_name, value). It promises infinite flexibility, but every value becomes text, constraints disappear, and simple questions require many self-joins. It has legitimate uses for genuinely user-defined fields, but used as the main design it is slow and hard to query. Modern alternatives include a JSON or JSONB column for the truly variable parts, alongside normal columns for everything predictable.

8. Losing history by overwriting

When a product's price changes or a customer's address is updated, overwriting the value is fine for the current state, but disastrous if invoices read the price from the product table. Old invoices then show today's price. Store transactional facts (price charged, address shipped to, tax rate applied) on the transaction itself. Where you need a full change history, keep an audit table or use temporal patterns with valid_from and valid_to columns.

9. Inconsistent naming

CustomerID, cust_id, customer and clientId all referring to the same thing in different tables slows every developer and invites bugs. Pick a convention and write it down: for example, lowercase snake_case, plural or singular table names consistently, primary keys named id, foreign keys named <table>_id. Avoid reserved words such as order, user and group as table names, or you will be quoting them forever.

10. Designing without the queries in mind

A schema can be perfectly normalised and still perform badly if nobody considered how it will be read. Before finalising a design, list the ten most important screens and reports, sketch the SQL each needs, and make sure the indexes support them. Also think about volume: a table that will hold hundreds of millions of event rows needs a plan for archiving or partitioning from the start.

Spotting database design mistakes in an existing system

A few quick checks against the database catalogue reveal many of these problems:

-- MySQL: columns that look like money stored as floating point
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_schema = DATABASE()
  AND data_type IN ('float', 'double')
  AND (column_name LIKE '%price%' OR column_name LIKE '%amount%'
       OR column_name LIKE '%total%');

-- Tables with no primary key
SELECT t.table_name
FROM information_schema.tables t
LEFT JOIN information_schema.table_constraints c
  ON c.table_schema = t.table_schema AND c.table_name = t.table_name
 AND c.constraint_type = 'PRIMARY KEY'
WHERE t.table_schema = DATABASE()
  AND t.table_type = 'BASE TABLE'
  AND c.constraint_name IS NULL;

Fixing design problems in a live system needs careful migration planning. Our database management team reviews schemas and plans those changes, and our software consulting service can review designs before a project starts, when changes are cheapest.

Key takeaways

Use exact DECIMAL types for money and native types for dates and numbers.
Let the database enforce integrity with foreign keys, NOT NULL and CHECK constraints.
Avoid packed lists and overused EAV; store transaction-time values on the transaction.
Most database design mistakes are cheap to avoid early and expensive to fix later.

Databases articles