I’ll explain by an example:
-------------------------------------------------------------------
| Course | Field | Instructor | Instructor Phone |
-------------------------------------------------------------------
| English | Languages | John Doe | 0123456789 |
| French | Languages | John Doe | 0123456789 |
| Drawing | Art | Alan Smith | 9856321158 |
| PHP | Programming | Camella Ford | 2225558887 |
| C++ | Programming | Camella Ford | 2225558887 |
-------------------------------------------------------------------
- If you have a
Courseyou can easily get itsInstructorsoCourse->Instructor. - If you have an
Instructoryou can’t get hisCourseas he might be teaching different courses. - If you have an
Instructoryou can easily get hisPhonesoInstructor->Phone.
That means the if you have a Course then you can get the Instructor Phone which means Course->Instructor Phone (i.e. Transitive dependency)
Now for the problems:
- If you delete both the
FrenchandEnglishcourses then you will delete their instructorJohn Doeas well and his phone number will be lost forever. - There is no way to add a new
Instructorto your database unless you add aCoursefor him first, or you can duplicate the data in anInstructors tablewhich is even worse. - If Instructor
John Doechanges his phone number then you will have to update all Courses that he teaches with the new info which can be very prone to mistakes. - You can’t delete an Instructor from your database unless you delete all the courses he teaches or set all his fields to null.
- What if you decide to keep the birth date of your instructors? You will have to add a
Birth Datefield to theCoursestable. Does this even sound logical? Why keep an instructor information in the courses table in the first place?