8 Common Database Design Mistakes and How to Fix Them

21CTO Guide: Good database design is essential for data accuracy, consistency, integrity, and for ensuring efficient, reliable, and easy-to-use databases.

This article introduces common poor database design practices, explains why they are problematic, and shows how to avoid them.

Bad Practice #1: Ignoring the Purpose of Data

Data is stored to be used later in applications. Designers must know what the data represents, how it will be accessed, at what rate, and the expected volume. Different use cases—such as manual daily collection versus real‑time industrial data—require different models. Understanding the system’s purpose helps choose the right engine, entities, record size, and format; neglecting this leads to fundamental design flaws.

Bad Practice #2: Poor Normalization

Database design is not deterministic; designers may follow all rules and still produce different layouts. However, adhering to normalization (at least up to the third normal form) yields layouts that best represent entities and balance query, insert, update, and delete performance.

Bad Practice #3: Redundancy

Redundant fields and tables may seem convenient initially but cause maintenance nightmares, increased size, inconsistency, and performance degradation. Strict normalization avoids unnecessary overhead; redundancy should only be used in special cases and be well documented.

Bad Practice #4: Weak Referential Integrity (Constraints)

Referential integrity constraints are valuable tools provided by database engines. If constraints are omitted or minimal, data integrity relies on business logic, increasing the risk of human error.

Bad Practice #5: Not Leveraging Database Engine Features

Modern database engines offer powerful capabilities that simplify development and ensure data correctness, safety, and availability. These include fast data views, index acceleration, built‑in aggregation, transaction handling with commit/rollback, locking, stored procedures, functions, constraint enforcement, triggers, and query optimizers.

Bad Practice #6: Composite Primary Keys

While composite keys are allowed, using an auto‑generated integer ID as a simple primary key is generally preferred, especially for tables with millions of rows, to keep indexes compact and maintain performance.

Bad Practice #7: Poor Indexing

Creating indexes on every column may speed up SELECTs but harms INSERT, UPDATE, and DELETE performance due to synchronization overhead. Effective indexing requires selecting the most frequently queried columns, considering column data types, and balancing read/write workloads.

Bad Practice #8: Confusing Naming Conventions

Table and column names should be descriptive and consistent. Avoid cryptic abbreviations, overly short names, or reserved words. Adopt simple conventions such as using "Id" for primary keys, prefixing foreign keys with the related table name, and using clear prefixes for constraints (PK, FK, IDX) and unique indexes.

Provide fast, effective data views, often denormalized for query purposes without losing correctness.

Accelerate table index lookup speed.

Offer built‑in aggregation functions without additional programming.

Ensure transactions can be committed or rolled back to maintain data integrity.

Maintain data safety with proper locking during transactions.

Support complex data management via stored procedures.

Enable complex calculations and data transformations through functions.

Enforce data correctness and prevent erroneous constraints.

Trigger automatic actions when data events occur.

Run an optimizer to generate efficient execution plans, preserving them for future use.

Author: 寂静日光