MySQL: MySQL Database Design and Normalization Theory
Last updated: 2026-08-26
Good database design is the cornerstone of a system’s success—poor design can lead to endless problems down the road.
This lesson covers the theory and practice of database design.
1. What You'll Learn
- Database Design Process
- ER Diagram Design
- Normal Form Theory (1NF → BCNF)
- Anti-paradigm design
- Naming Conventions
2. Design Process
graph TB
A[Requirements Analysis] --> B[Conceptual Design ER Diagram]
B --> C[Logical Design Table Structure]
C --> D[Physical Design Index/Partition]
D --> E[Implementation and Deployment]
E --> F[Operations and Maintenance Optimization]
3. Paradigm Theory
| Paradigm | Requirements | Example |
|---|---|---|
| 1NF | Fields cannot be further divided | A phone number cannot have multiple entries |
| 2NF | Eliminating partial dependencies | Non-primary key fields are fully dependent on the primary key |
| 3NF | Eliminate transitivity | Non-primary key fields cannot depend on other non-primary key fields |
| BCNF | Every determining factor is a candidate key | A stricter form of 3NF |
▶ Example: 3NF Design
Output:
TEXT
📖 Display only
id | customer_id | total_amount
---+--------------+-------------
1 | 101 | 299.99
SQL
-- Violation of 3NF (Department name depends on dept_id, not directly on employee id)
CREATE TABLE employees_bad (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
dept_name VARCHAR(50) -- Transitive dependency
);
-- ✅ Comply with 3NF
CREATE TABLE departments (
id INT PRIMARY KEY,
name VARCHAR(50)
);
CREATE TABLE employees (
id INT PRIMARY KEY,
name VARCHAR(50),
dept_id INT,
FOREIGN KEY (dept_id) REFERENCES departments(id)
);
Output:
TEXT
📖 Display only
Query executed successfully
▶ Example: Anti-Paradigm Design for Performance
Output:
TEXT
📖 Display only
Query executed successfully
SQL
-- Normalized design: requires JOIN to get order total
CREATE TABLE orders (
id INT PRIMARY KEY,
customer_id INT,
order_date DATE
);
CREATE TABLE order_items (
id INT PRIMARY KEY,
order_id INT,
product_name VARCHAR(100),
quantity INT,
price DECIMAL(10, 2)
);
-- Anti-paradigm: add a total_amount column to avoid real-time calculation
ALTER TABLE orders ADD COLUMN total_amount DECIMAL(10, 2);
-- Now you can query order totals without a JOIN
SELECT id, customer_id, total_amount FROM orders WHERE id = 1;
Output:
TEXT
📖 Display only
Query executed successfully
▶ Example: ER Diagram to Table Structure
Output:
TEXT
📖 Display only
Query executed successfully
SQL
-- From ER diagram to SQL: E-commerce core tables
-- Customers table
CREATE TABLE customers (
id INT PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
email VARCHAR(100) UNIQUE NOT NULL
);
-- Products table
CREATE TABLE products (
id INT PRIMARY KEY AUTOINCREMENT,
name VARCHAR(100) NOT NULL,
price DECIMAL(10, 2) NOT NULL
);
-- Orders table (relationship: one customer → many orders)
CREATE TABLE orders (
id INT PRIMARY KEY AUTOINCREMENT,
customer_id INT NOT NULL,
order_date DATE NOT NULL,
FOREIGN KEY (customer_id) REFERENCES customers(id)
);
-- Order items (relationship: one order → many items, one product → many items)
CREATE TABLE order_items (
id INT PRIMARY KEY AUTOINCREMENT,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id),
FOREIGN KEY (product_id) REFERENCES products(id)
);
Output:
TEXT
📖 Display only
id | customer_id | total_amount
---+--------------+-------------
1 | 101 | 299.99
4. Anti-Paradigm Design
Sometimes, for the sake of performance, we deliberately break the rules.
| Scenario | Anti-paradigm Approach | Reason |
|---|---|---|
| Frequent JOINs | Redundant Fields | Fewer JOINs |
| Statistical Queries | Calculated Columns | Avoiding Real-Time Calculations |
| Reports | Summary Reports | Speed Up Queries |
5. Naming Conventions
| Best Practices | Recommendations | Things to Avoid |
|---|---|---|
| Table Name | snake_case, plural | singular, camelCase |
| Field Name | snake_case | camelCase, Chinese |
| Primary Key | id | userId |
| Foreign Key | table_id | customerId |
| Index | idx_table_col | table_col_idx |
6. ER Diagram Design
erDiagram
CUSTOMERS ||--o{ ORDERS : places
ORDERS ||--|{ ORDER_ITEMS : contains
PRODUCTS ||--o{ ORDER_ITEMS : includes
CUSTOMERS {
int id PK
string name
string email
}
ORDERS {
int id PK
int customer_id FK
decimal amount
date order_date
}
PRODUCTS {
int id PK
string name
decimal price
}
❓ FAQ
Q Is it necessary to adhere to 3NF?
A In most cases, yes. However, in scenarios with many reads and few writes, it’s acceptable to deviate from the normal form.
Q What data types should be used for the fields?
A Use BIGINT for the primary key, DECIMAL for amounts, TIMESTAMP for dates and times, and VARCHAR for text.
Q Should table names be in the singular or plural?
A We recommend using the plural (users, orders) to indicate "a set of records."
Q Is it necessary to adhere to the three normal forms?
A Not necessarily. In high-concurrency query scenarios, you can deviate from the normal forms; appropriate redundancy can reduce the number of JOINs.
Q What tools can be used for ER diagrams?
A MySQL Workbench (forward/reverse engineering), draw.io (free online tool), and dbdiagram.io (ER diagram generation from code).
📖 Summary
- Design Process: Requirements → ER Diagram → Table Structure → Indexes → Deployment
- Normal Forms: 1NF (non-atomized) → 2NF (elimination of partial dependencies) → 3NF (elimination of transitive dependencies)
- Anti-pattern: Appropriate redundancy for performance
- Naming Conventions: snake_case, primary key id, foreign key table_id
📝 Exercises
-
Basic Problem (Difficulty: ⭐): Design an ER diagram for a blog system.
-
Advanced Problem (Difficulty ⭐⭐): Normalize a table in 1NF to 3NF.
-
Challenge Question (Difficulty: ⭐⭐⭐): Design an e-commerce database that includes tables for users, products, orders, and payments.
