Search code examples
sqldatabasedatabase-designtransitive-dependency

What is wrong with a transitive dependency?

I have some transitive dependencies in my database design. I have been told by my superiors that these can cause bugs. I am finding it difficult to find resources that will tell me how having these dependencies will cause bugs. What kind of problems will they cause?

I am not disputing the fact, just eager to learn what kind of problems they can cause.

Edit for more details:

From wikipedia :

Transitive dependency
A transitive dependency is an indirect functional dependency, one in which X→Z only by virtue of X→Y and Y→Z.

Solution

  • 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 Course you can easily get its Instructor so Course->Instructor.
    • If you have an Instructor you can't get his Course as he might be teaching different courses.
    • If you have an Instructor you can easily get his Phone so Instructor->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:

    1. If you delete both the French and English courses then you will delete their instructor John Doe as well and his phone number will be lost forever.
    2. There is no way to add a new Instructor to your database unless you add a Course for him first, or you can duplicate the data in an Instructors table which is even worse.
    3. If Instructor John Doe changes 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.
    4. You can't delete an Instructor from your database unless you delete all the courses he teaches or set all his fields to null.
    5. What if you decide to keep the birth date of your instructors? You will have to add a Birth Date field to the Courses table. Does this even sound logical? Why keep an instructor information in the courses table in the first place?