Is tag and tag-synonym table schema current in the Data Explorer?

Question

https://data.stackexchange.com/stackoverflow/query/new

**Tags**
Id
TagName

**TagSynonyms**
Id
SourceTagName
TargetTagName
AutoRenameCount

(There's no foreign key relation between the two)

I type sdk in Tags field in stackoverflow.
It shows iphone because iphone-sdk is a synonym for iphone (and SO obviously sort tag list by used-count)

Isn't having a foreign key to Tags instead of TargetTagName better than the shown schema to do the job(return the sorted tag-list)?

Answer 1

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:

select *
from (
  select id
       , tagname
       , [count]
       , tagname as tname
  from tags 
  union
  select t.id
       , tagname
       , [count]
       , sourceTagName
  from tags t
  inner join tagsynonyms ts 
          on ts.targettagname = t.tagname
  ) ctags
-- 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.