The pros of this approach are:
- the ease of a direct access to the connected object if you don't need the objects in the middle;
- potential performance improvement due to the reduction of joins (but with the optimizing engine of modern RDBMS, this gain could be marginal)
The cons are all the cons of denormalization:
- you have redundant data
- you have to keep this data in sync. The more objects there are in the middle, the more difficult it gets to keep the sync.
- Creation of concurrency bottlenecks: every change on objects of the middle might require an update of the A object, which can cause locking issues if different processes/users are at the origin of the change on the intermediary objects.
- Complexification/duplication of code: e.g. if B has some kind of status (e.g."DELETED", "SUSPENDED" or "INACTIVE) that impacts the accessibility of C and D for A, you'd need to replicate this feature in the code that maintains the sync. SO you need to analyse not only the technical relations between the data, but also the business logic that is behind it.
Finally, before considering the denormalization,you need to analyse the relation between A and D, to know where the foreign key needs to be added. If it's 1 to one you may add it in A (but does D already exist when you create an A?). But if it's one to many, you can't consider duplicating A rows, so you'd need to add a foreign key back to A in D (same question: does the A generally exist when D is created ?).
Personally, I'd fear that the cons of the denormalization outweigh the pros, especially when thinking about concurrency issues But a more in-depth analysis with measurements is required to analyse the dynamics behind the tables (e.g. number of reads vs. number of writes) in order to know the objective facts.