Database Design Errors to Avoid & How To Fix Them

Design • 15-Jul-2025

by Roxana Haidiner

Design Errors

Even now, in 2025, with powerful database tools and cloud platforms, developers still make elementary mistakes while schema designing. These are prone to create issues of performance, inconsistency of data, and more technology debt.

This article highlights most common database design mistakes, how to avoid them, and why graphical tools like DbSchema can avert better designs from the start:

Missing Foreign Keys
Missing or Inadequately Designed Indexes
Using JSON and JSONB in Relational Databases
Overlooking Normalization (or Overdoing It)
No Clear Naming Conventions
Lack of Schema Documentation

1. Missing Foreign Keys

One of the most common database design errors is skipping foreign keys. It is essential that foreign keys be maintained for referential integrity between tables. Without these, invalid data can be put into the database, for example, inserting a user review that is not appropriate.

Example

Suppose we are given two tables: Users and Reviews. It is a requirement that each review must be assigned a valid user.

Users (user_id, username)
Reviews (review_id, user_id, review_text)

Unless user_id in Reviews is explicitly stated to be a foreign key, the database will not disallow inserting a review for a user that doesn’t exist.

Fix

Specify a definite association through a foreign key. This is how you can add it in SQL:

ALTER TABLE Reviews
ADD CONSTRAINT fk_reviews_users
FOREIGN KEY (user_id) REFERENCES Users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

This ensures that:

It must always be referring to some current user
If a user is deleted or updated, the associated reviews will be processed according to the action specified (in this instance, CASCADE)

Tip: Use visual design tools like DbSchema to better see and handle these relationships more distinctly through your schema model.

Visual Relationships with DbSchema Tool

2. Missing or Inadequately Designed Indexes

Another common mistake is skipping indexes or creating the wrong ones. If indexes are not implemented properly, queries that filter or join large tables will degrade with more data.

Signs You Need Indexes

Queries are running too slowly to return output
Full-table scans even for basic filters
Joins of tables are slow
Increased load on the database server

How to Fix It

First, find which columns are frequently mentioned in WHERE, JOIN, or ORDER BY clauses, and add indexes to them.

CREATE INDEX idx_orders_customerid ON Orders(CustomerID);

Avoid creating too many indexes. Each one can slow down INSERT, UPDATE, and DELETE operations. Always monitor query performance before and after adding them.

Create Index with DbSchema Tool

3. Using JSON and JSONB in Relational Databases

Storing structured data in a single column with JSON is a ubiquitous practice when dealing with stretchy or optional data. Both JSONB and JSON formats are implemented for the most part of modern relational databases.

JSON vs. JSONB

JSON stores the data in plain text. It is readable and straightforward but slower.
JSONB keeps the same data in a binary format. It allows for quicker comparison, filtering, and retrieval.

Certain databases even facilitate indexing JSONB fields, thus making them simpler to deal with when querying.

When JSON or JSONB are a Suitable Option

Its appearance changes between rows or shifts frequently
You must save optional or nested fields without adding many extra columns
It is for internal settings, user options, logs, or multicultural contents

This approach leaves room for maneuvering without undue complication of your schema, especially in early development stages.

What to Keep in Mind

JSON fields are not strongly typed – the database will not enforce structure
Filtering and reporting may be slower or more complex
If fields are frequent in queries and consistent, you’re better off with separate columns or tables

Use JSON or JSONB when you need flexibility, but re-evaluate your structure as the application matures. Visual tools such as DbSchema enable you to model and document JSON & JSONB fields together with the rest of your schema.

JSONB Array in PostgreSQL

4. Overlooking Normalization (or Overdoing It)

Others simply don’t normalize at all, shoving everything into a big table. Others normalize too much, spreading the data across many small tables, making queries harder to manage.

Fix

Normalize to 3NF (Third Normal Form) where necessary
Only denormalize when performance is a concern
Start with a well-supported design and validate it with sample queries and data

Visual design tools help you understand and fine-tune normalization by displaying foreign key relationships more visibly.

✖️ Not in 3NF:

review_id	user_id	user_name	phone_number	movie_id	rating	comment
501	U001	Alice Martin	555-342-9752	101	5	Enjoyed it!
502	U002	Ben Carter	222-865-9876	102	4	Well written.

Changing a user’s name or phone number would require updating every review they’ve written.

✔️ 3NF (After normalization):

Solution: Move user_name and phone_number into a separate Users table and reference them using user_id in Reviews.

Reviews Table

review_id	user_id	movie_id	rating	comment
501	U001	101	5	I enjoyed it!
502	U002	102	4	Advised well.

Users Table

user_id	user_name	phone_number
U001	Alice Martin	555-342-9752
U002	Ben Carter	222-865-9876

3NF keeps your schema clean, avoids duplication, and simplifies maintenance.

5. No Clear Naming Conventions

Schemas that are not consistently named are hard to manage. Column names like id, countryID, Country_id, and countryid in the same project can be confusing and bad for teamwork.

Fix

Use lowercase with underscores: country_id, country_name
Keep consistent prefixes, suffixes, and pluralization
Document and stick to naming rules across the database

Clear and predictable naming helps everyone on your team understand and expand the schema more easily.

Naming Conversions with DbSchema Tool

6. Lack of Schema Documentation

Not many teams prioritize documenting their schema, business rules, and constraints. After a few months, it becomes unclear why certain columns were added or how calculations were intended.

Fix

Maintain a clear, up-to-date schema diagram
Add comments directly to the database:

COMMENT ON COLUMN Orders.OrderDate IS 'The date the order was placed';

Use tools like DbSchema to generate interactive HTML5 documentation that you can share with the team.
Good documentation helps onboard new developers, improves decision-making, and removes guesswork.

Conclusion

Database design is not just about creating tables and columns. It’s about building systems that last, perform well, and allow teams to collaborate effectively.

By avoiding these mistakes and using visual tools, you can build reliable, maintainable, and scalable database systems.

Whether starting from scratch or updating an old schema, remember these principles, and keep refining your design as your application grows.