Not sure what's expected from the DBA side on this task.
Can you be more specific?

From what I can tell the only way to solve this would be to add more fields to existing tables that track if the transclusion is directly caused by wikitext, create a new table that stores "template links" rather than transclusions, or to only store direct transclusions in the templatelinks table. The first two options would require changes to the database.

As we mentioned on T278236, let's try not to add more stuff to *links tables, they are already under lots of pressure.

Yes. Beside *links not being normalizied (T222224), I think we the best way to do this is store to this secondary data in (on top of *links tables) in a graph database, currently only wdqs uses a graph database but if we want to record and query transitive relationships, graph database is a better choice than MySQL. i.e. let's make sure *links do one thing and do it well instead of adding new functionalities to it that wouldn't work.

Actually the redirect table could be a further candidate for indirect use of linktarget table, when both pagelinks and templatelinks table migration are completed.

This could help in detection of

• redirect-to-redirect
• redirect to not existing page

At least, it might reduce string amount by multiple redirects to the same page, and a linktarget table entry would yet exist anyway if that page is not orphaned.

This should simplify and unify Special:Whatlinkshere generation.

• If there is an entry for this page name then look for lt_id in the related tables.

Ceterum censeo, an exists (queries) table should be introduced and use linktarget as well.