In every real life Application, Database is a must. It might reside in Backend (ex: MySQL server-side) or in Application-end (ex: Android’s Local DB) to make application user-friendly and fetch data faster. A handy knowledge on Database always makes you a bit advanced than others but a Redundant Database will do just the opposite. It will make your life hell. Relationships are always crucial while designing a database in a large context.
In this context, I will write on simple Normalization concept to get rid of Data Redundancy
Suppose, we have to store Student, Department information in our Database. Student and Department are dependent on each other. One student can belong to only one department but one department can have many students. At the first glance one might depict the database like this:
Looks good? Yes, it looks pretty simple and easy to understand! Okay, let’s dig deep a bit.
Look at the following point:
- What If a Department Head changes 6 months later in any particular department?
Let’s assume Mr. B from CSE department retires from the job. Then you have to change all the hundreds/thousands of rows where Mr. B has entries. What if Mr. C has to hand over his position to someone else named Mr. O? Again you have to update all the rows where Mr. C has entries. Seems painful and tiring? Yes, that’s where Redundancy comes.
- We could have store these data into 2 separate tables. One in tbl_student and another one is in tbl_department.
- student_id, student_name, student_roll, department_id fields in tbl_student
- department_id, department_name, department_head in fields tbl_department
Let’s look at the following Normalization:
- We have broken the previous table into two parts as mentioned above
- tbl_department‘s id will Refer to tbl_student‘s department_id.
- Now, we can easily modify just the tbl_department table. Suppose, Mr. C hands over his duty to Mr. O. Then, we can easily update only one row in tbl_department.
- We can add other departments easily in the future.