Suppose we have Users that have Blogs, blogs have Posts, and posts have Comments, all of which are one-to-many relationships.
Normally each entity would only hold the foreign key of its immediate parent. To navigate from the top level to bottom level, multiple joins will be used.
The DB provider is PostgreSQL. I'm using Entity Framework Core so the queries will be translated either via multiple Include and ThenInclude from the top level or a Where clause with multiple levels of navigation from the bottom level.
But in this application, we frequently need to query a User and all of its associated Comments.
In this case, does it make sense to add an additional User foreign key in Comment and matching navigation properties so that the navigation can be made with just one join instead of multiple ones? I'd assume this is good for performance and convenience.
Note that the real application has more than 4 levels, and frequently is the important pattern here.