Is it good practice to keep foreign key columns redundant?

Question

Consider that we have the models A, B, C and D, each one referencing to the above, ie, A has many Bs, B has many Cs and C has many Ds:

Redundancy-free

A
|  id | ... |
------------
| ... | ... |

B
|  id | a_id | ... |
--------------------
| ... |  ... | ... |

C
|  id | b_id | ... |
--------------------
| ... |  ... | ... |

D
|  id | c_id | ... |
--------------------
| ... |  ... | ... |

---

It would be recommended to include more columns in C and D indicating which the referenced record of A and B?

Redundancy

C
|  id | a_id | b_id | ... |
---------------------------
| ... |  ... |  ... | ... |

D
|  id | a_id | b_id | c_id | ... |
----------------------------------
| ... |  ... |  ... |  ... | ... |

It is a redundancy, but I usually do this to facilitate consultations, it is useful to avoid JOINs. I also believe that the economy of JOINimprove your performance.

Is this considered good or bad practice? (If not in all cases, at least when the data is immutable) Is there any better solution for this?

Answer 1

My personal opinion is that the benefit does not outweigh the ripple effect of integrity updates and validations on a large mass of data.

• Several Dbmss implement execution plans where the dependency between structures is taken into account. By increasing the complexity of the relationships described by foreign keys, you may be undermining the evaluation of this plan;
• If you delete or modify an item from the table To, yet all records dependent on B, C and D will need to be validated for consistency purposes. The proposed model offers no benefit for data change operations (UPDATE / DELETE).
• If you change the type of A.ID, you will need to make changes to all tables, not just the type of B.A_ID.
• Several ORM tools depend on a correct representation of the interdependence between tables in the database.

If its only advantage of this implementation is the agility for the creation of darlings, I would make the extra effort to correctly describe the interrelationship.

(Additionally, there is a system development principle called 'do not repeat' (of the English Don’t repeat yourself), that some Dbas also use to consider practice as a good choice for data hygiene.)

Answer 2

1. You didn’t ask for personal opinion, asked if it was good practice. In addition Stackoverflow does not propose to answer questions based on personal opinion and recommends that no questions of this kind be asked, let alone answers based on personal opinions.

2. No, it’s not good practice. It’s really bad practice. It’s not a personal opinion see the points below. Also if you want personal feedback, ask this kind of question on specialized forums like SQL Server Central (and get ready for possible "hard answers")

3. It is not even a matter of DRY but even normalization. We should denormalize only when strictly necessary (for performance). These are rare cases.

4. You will only write less joins, not necessarily the DBMS will be able to give you more performance. On the contrary it may get worse because as you include redundant data and use indexes you will also occupy more space in hard drive (more IO), force more CPU and memory usage.

5. Usually no matter the number of joins. If you indexed well and normalized correctly you should already achieve good performance.

6. If you have a very large data mass and complex relationships there are other much better means of achieving performance like using views, partitioning tables, fine index tunning, etc