Posted inDatabase

Database Schema Design: Don’t Let Technical Debt Sink Your System

Database tutorial - IT technology blog
Database tutorial - IT technology blog

Why Does Proper Schema Design Matter?

Back in my early days as a developer, I thought creating a few tables and throwing in some columns was enough. Everything was fine until the project hit the 10 million record mark. Suddenly, simple queries took 15-20 seconds, and data anomalies started appearing for no apparent reason. That was the price of sloppy schema design.

Whether you use MySQL, PostgreSQL, or MongoDB, schema design mindset is the root of everything. A good schema allows the system to respond in milliseconds rather than seconds. It saves server resources and keeps data consistent. Conversely, a poor schema forces your backend code to carry heavy processing logic to compensate for data gaps.

Database design is essentially a game of trade-offs. You must balance read speed, write speed, and integrity. Let’s dive into practical implementation.

1. Normalization: Keeping Data Clean and Lean

Normalization is a way of organizing a database to eliminate redundancy. For most web/app projects, mastering the first three normal forms (1NF, 2NF, 3NF) is enough to get you through the battle.

First Normal Form (1NF): Atomicity

Each data cell must contain only a single value. Never store a list of phone numbers like "090..., 091..." in a single phones column. Split them up to make searching or indexing easier later.

Second Normal Form (2NF): Full Functional Dependency

Every non-primary key column must depend entirely on the primary key. For example, in an OrderDetails table, don’t stuff the product_name in there. If the product name changes, you’d have to update thousands of old order rows. Instead, only store the product_id.

Third Normal Form (3NF): No Transitive Dependency

Eliminate instances where one column depends on another column (which itself is not the key). Suppose a Users table has province_id and province_name. Since province_name depends on province_id, you should move the province information to a separate table.

-- Separate tables to achieve 3NF, avoiding province name redundancy
CREATE TABLE Provinces (
    id INT PRIMARY KEY,
    name VARCHAR(100)
);

CREATE TABLE Users (
    id SERIAL PRIMARY KEY,
    username VARCHAR(50),
    province_id INT REFERENCES Provinces(id)
);

2. When Should You Use Denormalization?

Over-normalization can sometimes be a performance disaster. When a system needs to fetch data from 7-8 different tables, constant JOIN operations will overload the server CPU. This is when we apply Denormalization—accepting controlled data redundancy to speed up queries.

I usually choose to denormalize in these three cases:

  • Dashboards/Reporting: Instead of forcing the database to COUNT(*) millions of rows every time a user loads a page, store a total_orders column in the Users table.
  • Point-in-time data: The OrderItems table should store the price at the time of purchase. Even if product prices change later, the customer’s old invoice must reflect the original price.
  • Reducing JOIN depth: If you only need to fetch a display name but have to JOIN through 4 tables, consider copying that name to the destination table.

With PostgreSQL, you have a heavy-duty weapon: JSONB. It allows you to store unstructured data directly within a relational table. This is perfect for frequently changing product attributes like color or size without needing dozens of side tables.

-- Use JSONB for schema flexibility while maintaining speed
CREATE TABLE products (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    specs JSONB -- Store color, material, weight...
);

-- Querying directly into JSONB fields is extremely fast
SELECT * FROM products WHERE specs->>'color' = 'black';

3. Anti-patterns to Avoid at All Costs

Through many code reviews, I’ve seen developers frequently fall into these three classic traps:

Mistake 1: EAV (Entity-Attribute-Value) Design

Many people create “universal” tables with key and value columns. This looks flexible but makes querying a nightmare. To filter by 3 conditions, you have to JOIN that same table 3 times. Performance will plummet as data grows.

Mistake 2: Storing Arrays as Comma-Separated Strings

Storing tags = "1,2,3" is a fatal mistake. You can’t index it, you can’t JOIN accurately, and removing a tag from that string is a logic nightmare in your code.

Mistake 3: Neglecting Foreign Keys

Don’t listen to anyone who says to drop Foreign Keys to “lighten” the database. Without them, after a few months of operation, your database will be full of “garbage.” Orders pointing to non-existent users will cause the system to crash unexpectedly.

4. Real-world Measurement and Optimization

Design isn’t the end. You need to observe how it actually performs in a production environment.

Use EXPLAIN ANALYZE: Get into the habit of running this command for critical queries. If you see a Seq Scan on a large table, it’s a warning bell that you’re missing an index or the schema is designed in the wrong direction.

Clean up redundant indexes: Indexes speed up reads but slow down writes. Don’t create indexes indiscriminately. Here is a query I often use on Postgres to find “idle” indexes:

SELECT s.relname AS table_name,
       indexrelname AS index_name,
       i.idx_scan
FROM pg_stat_user_indexes AS i
JOIN pg_stat_user_tables AS s ON s.relid = i.relid
WHERE i.idx_scan = 0; -- Index has never been used

Manage changes with Migrations: Never modify the schema manually directly on the DB. Use Flyway, Liquibase, or migration tools from Laravel or Django. This helps the team maintain history and rollback quickly if something goes wrong.

In summary, schema design is the art of balance. Don’t be too dogmatic about normalization, but don’t be so lax that you turn your DB into a landfill. I hope these practical experiences help you feel more confident when drafting your first tables for a project.

Share:
Tags: