MySQL Normalization
What is Normalization?
Normalization is the process of organizing data in a database efficiently.
To do this, we need to create tables and relate them based on certain rules that are meant to secure information while enabling the database to be easily adjusted or expanded as needed. Below is a brief explanation of what normalization principles do:
First Normal Form (1NF):
Atomic Values:
Each column in a table must contain atomic (indivisible) values.
Avoid storing multiple values in a single column or repeating groups of values.
Table: Students
| StudentID | Name |
|---|---|
| 1 | John Michael |
| 2 | Patrick James |
Second Normal Form (2NF):
No Partial Dependencies:
- The table should be in 1NF.
- No non-key column should be dependent on only a portion of the primary key.
- Move any partial dependencies into separate tables.
Table: Orders
Table: Products
| ProductID | Name |
|---|---|
| 101 | AB |
| 103 | CD |
Third Normal Form (3NF):
No Transitive Dependencies:
- The table should be in 2NF.
- No non-key column should be dependent on another non-key column (non-key-to-non-key dependency).
- Move any transitive dependencies into separate tables.
Table: Employees
| EmployeeID | Department | City |
|---|---|---|
| 1 | IT | DOM |
| 2 | Sales | DAR |
Table: Departments
| Department | City |
|---|---|
| IT | DOM |
| Sales | DAR |
Designing Normalized Database Schemas:
Designate Objects:
- Specify objects and properties that should be recorded in a database.
Clarify Connections:
- Determine connections between the objects (one-to-one, one-to-many, many-to-many).
Normalize:
- Convert data into tables and normalize them (1NF, 2NF, 3NF) to avoid repetition and ensure correctness of information.
If Needed – Undo Normalization:
- Where read speed is important and redundancy is not a problem, denormalize certain parts for optimizations.
Refine Iteratively:
- According to needs changes feedback use pattern requirements keep improving the schema.