I would like to know more about how tags are stored in the database, and how each tag is retrieved in a search query.
I would also like to know if I can store many URLs in a one cell of a table as a string, in the same way you have stored tags in the above link.
As we don't have access to the actual database schema we can assume what is offered in the Stack Exchange Data Explorer is close enough to reason about.
This is the Entity Relationship Model for tags with the relevant attributes:
Data Examples +-------------+ | Posts | | - Id | 100017 | - Tags | <discussion><tags><database> +-----+-------+ | |* +-----+-------+ | PostTags | | - PostId | 10017 10017 10017 | - TagId | 4 51 322 +-----+-------+ |* | +-----+-------+ | Tags | | - Id | 4 51 322 | - TagName | discussion tags database +-------------+
Between the Posts table and Tags table there is a relation over the join table PostTags. Do notice that the field
tags in the Posts table also holds the tags that exist on the question. This is a de-normalized field where the tagnames that are linked to the question are enclosed in
< > so they can be accessed without extra reads on joined tables.
In a search query on SEDE you could either do a
posts.tags like '%<database>%' or a join over posts, posttags and tags with a where clause on
tags.tagname = 'database'. The former leads to a full table scan on the posts table, which for larger databases like Stack Overflow might cause problems, the latter involves more reads over more tables.
If you're interested in the historical use of tags you can query the PostHistory table. Its records with
posthistorytypeid in (3,6) hold the events when tags being added/edited, where the actual tags are stored denormalized in the
text field, stored similar as is done in
Do know that in actual production there is something called the tag engine which is a proprietary "search" engine that is fed with info from above tables and then does the heavy lifting of searching in combination with Elastic Search.