Database Normalization in simple term

Database Normalization in simple term

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_idclass_idteacher
11Smith
22Jackson
33James
42Jackson

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.

idclass_nameteacheroffice
1MathSmithA107
2ProgrammingJacksonB205
3HistoryJamesA100
4ScienceJacksonB205

Solution: isolate those related columns (teacher, office) and move them into a separate table

Referenced from Adam McNeilly's post