The database design goal is always to store and retrieve the data in the most efficient manner, bad database design will lead to huge problems when the application grows bigger.
To avoid them database design should consist of the following properties
- No data redundancy
- Data integrity
No Data Redundancy in simple term No Repetitive Data
Data integrity in simple term Should able to CRUD without negative effect on other data
Normalization
Process of organizing a database to avoid data redundancy and ensure data integrity.
Normal Forms
The normalization process can be done by step by step process they are called Normal Forms (NF). Let's see first three Normal Forms.
1NF
Each record should represent only one primary key
One columns should have a single value
The column should have atomic values in simple terms it cannot be divided any further
No repeating groups
The table should not have the same group of columns example phone_number1, phone_number2...phone_numberN
2NF
No partial dependencies
Non-primary columns should depend only on the primary column
Example: The teacher column is determined only by the class_id, not by the entire primary identifier of (student_id, class_id) and the following table will lead to data redundancy because of repeating teacher name
student_id | class_id | teacher |
1 | 1 | Smith |
2 | 2 | Jackson |
3 | 3 | James |
4 | 2 | Jackson |
Solution: create a separate table for Teachers
3NF
There are no transitive dependencies
A transitive dependency is when the following happens:
- A determines B
- B does not determine A
- B determines C
Example: Following to table will lead to data redundancy because of repeating teacher name
id and class_name determine teacher but the teacher does not determine the id or class_name This is because a teacher could teach multiple classes.
id | class_name | teacher | office |
1 | Math | Smith | A107 |
2 | Programming | Jackson | B205 |
3 | History | James | A100 |
4 | Science | Jackson | B205 |
Solution: isolate those related columns (teacher, office) and move them into a separate table
Referenced from Adam McNeilly's post