I don't know if having a foreign-key on id instead of
TargetTagName is better, for that you have to define better.
The tagsynonyms table isn't particularly big. On Stack Overflow it counts 3,209 rows the moment, so a full-table scan wouldn't be much of a problem, in the context of The Data Explore that is.
The tags table hold 42,533 rows.
We can only guess how those queries for tag search are run (I doubt it is even run, just fetched from memory but let's skip that) so maybe this guess will work:
, tagname as tname
from tags t
inner join tagsynonyms ts
on ts.targettagname = t.tagname
-- where tname like '%sdk%'
order by [count] desc
In its first run it fetched 45,760 rows in 101 ms and this is the execution plan that was used:
If you uncomment the where clause the following index comes into play:
CREATE UNIQUE NONCLUSTERED INDEX [IX_Tags_TagName] ON [dbo].[Tags](TagName ASC);
cutting down the results to 165 rows and 45 ms.
I don't see a specific use case where the current schema would benefit from such foreign key design. Only if you have an information requirement that can't be achieved due to the current technical implementation there would be reason to change or add foreign-keys and possible indexes.
tl;dr; I don't think having a foreign-key on id's instead of tagname in this context add much value. The current design in SEDE is fine for its purpose.