What is meta? ×
Meta Stack Exchange is where users like you discuss bugs, features, and support issues that affect the software powering all 128 Stack Exchange communities.

I was looking at the schema and I noticed that there is a column titled **tags** in the posts table. What is the purpose of this column given that there is another table called **PostTags** that stores this information?

share|improve this question
1  
My guess is that the data dump schema doesn't accurately represent the actual database schema. –  jjnguy Nov 9 '10 at 20:13
2  
Or it's performance enhancement via denormalization. –  Robert Harvey Nov 9 '10 at 20:28
    
@ Robert Harvey, can you please elaborate (bearing in mind that I am novice) how this could be a performance enhancement? –  Mohamad Nov 9 '10 at 20:31
2  
@Mel: PostTags would be used to look up posts by tag. The tags column in the Posts table would be updated every time a tag is added or deleted from said post and is just a text representation of the current tags. Or so I'm guessing. The performance is gained when viewing lists of posts, because they don't require an addition query to display their tags. –  Powerlord Nov 9 '10 at 21:26

1 Answer 1

up vote 4 down vote accepted

The Tags field is a text field that stores a delimited list of tags for a question. I believe this was done to improve query performance when searching for questions with certain tags -- normalized schema vs. full-text index? I dunno which would win, though I suppose the latter since that appears to be what's in use.

The PostTags table exists only in Data Explorer (you'll notice it isn't in the data dump directly) as a normalized version of a question's tags, so you can do JOINs in your queries, instead of doing text searches on the Tags field.

When running queries in Data Explorer, use the PostTags table exclusively. Data Explorer runs on SQL Azure so there's no full-text index on the Tags column. Data Explorer runs on SQL 2008, but there isn't a full-text index on the Tags column.

share|improve this answer
    
thank you for the detailed answer. But is it not more work to update the tags in the delimited list every time there's a change in the tags? You would have to select all the tags, concatenate them, and store them as a list... that sounds like a bit of work, no? –  Mohamad Nov 10 '10 at 1:36
3  
@Mel: Yes, it is, for sure. However, edits are far less frequent than searches. Moreover, in terms of time complexity, an edit is O(1), whereas a search increases in proportion to the number of questions. So they have to code around making searching very efficient. –  Jon Seigel Nov 10 '10 at 2:23

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .