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

There are several columns such as Posts.PostTypeId where the content are encoded as an integer. Although not hard to find out playing with a few queries, what these integers represent should be documented, and shown in or linked from the schema.

share|improve this question
4  
you know what would be nice, if on hover it would display a tooltip with information about the column beyond the data type. –  waffles Jun 29 '10 at 21:53

3 Answers 3

up vote 4 down vote accepted

The data dump schema is well-documented in this question, including all the magic numbers. It doesn't include the normalized tables, but that should be obvious when looking at the Data Explorer schema.

The vote types are available by querying the VoteTypes table, but the post types are not. Personally, I would like to see the post types normalized just like the vote types, so you don't have to refer back to another website to find out the values (particularly if a 3rd value is added down the road).

EDIT: There is now a PostTypeId = 3 for Tag Wiki entries, so it's even more desirable for this column to be normalized. If the same Ids are maintained as the primary key in a PostTypes table, this shouldn't break any (many?) of the queries.

EDIT: I have added PostTypes and PostHistoryTypes to the schema. This will be live the next data dump import to SEDE (Jan 2011).

share|improve this answer
    
This is now live. The Area 51 vote types are not included in this iteration. –  Jon Seigel Jan 10 '11 at 18:42

An unofficial documentation on the value of these columns:

  • Posts.PostTypeId

    These should be obtained by SELECT * FROM PostTypes.

    • 1 = Questions
    • 2 = Answers
    • 3 = Tag wiki (from the early days, before separate excerpts)
    • 4 = Tag wiki excerpt
    • 5 = Tag wiki
    • 6 = Moderator election candidate platforms
    • 7 = Moderator-editable part of the site FAQ, and moderator election announcements (SE 2.0 only)
    • 8 = Privilege pages (/privileges/NAME) (Meta.SO only)

  • PostHistory.PostHistoryTypeId

    These should be obtained by SELECT * FROM PostHistoryTypes.

    • 1 = Initial Title - The first title a question is asked with.
    • 2 = Initial Body - The first raw body text a post is submitted with.
    • 3 = Initial Tags - The first tags a question is asked with.
    • 4 = Edit Title - A question's title has been changed.
    • 5 = Edit Body - A post's body has been changed, the raw text is stored here as markdown.
    • 6 = Edit Tags - A question's tags have been changed.
    • 7 = Rollback Title - A question's title has reverted to a previous version.
    • 8 = Rollback Body - A post's body has reverted to a previous version - the raw text is stored here.
    • 9 = Rollback Tags - A question's tags have reverted to a previous version.
    • 10 = Post Closed
    • 11 = Post Reopened
    • 12 = Post Deleted
    • 13 = Post Undeleted
    • 14 = Post Locked
    • 15 = Post Unlocked
    • 16 = Community Owned - A post has become community owned.
    • 17 = Post Migrated - A post was migrated.
    • 18 = Question Merged - A question has had another, deleted question merged into itself.
    • 19 = Question Protected
    • 20 = Question Unprotected
    • 21 = Post Disassociated - An admin removes the OwnerUserId from a post.
    • 22 = Question Unmerged - A previously merged question has had its answers and votes restored.
    • 24 = Approved suggested edit.

  • Votes.VoteTypeId

    These should be obtained by SELECT * FROM VoteTypes.

    • 1 = AcceptedByOriginator
    • 2 = UpMod
    • 3 = DownMod
    • 4 = Offensive
    • 5 = Favorite
    • 6 = Close
    • 7 = Reopen
    • 8 = BountyStart
    • 9 = BountyClose
    • 10 = Deletion
    • 11 = Undeletion
    • 12 = Spam
    • 13 = InformModerator
    • 15 = ??
    • 16 = Approved suggested edit

    If Area51 is to be included:

    • 2 = On-topic
    • 3 = Off-topic
    • 22 = Meh-vote
    • 23 = Referral in commitment phase
    • 28 = Fulfilled commitment (you or referral)
share|improve this answer
    
Citation for vote types 22 and 23? –  Jon Seigel Jun 29 '10 at 13:46
1  
@Jon: area51.stackexchange.com/reputation –  KennyTM Jun 29 '10 at 14:41
    
Ah yes. I didn't think of that. –  Jon Seigel Jun 29 '10 at 14:56
1  
Note also that PostTypeId is (currently) redundant with Questions having a NULL ParentId and for Answers ParentId is not NULL. –  Mark Hurd Jul 16 '10 at 18:00

Until there is official documentation, you can refer to Brent Ozar's Understanding the StackOverflow Database Schema.

share|improve this answer

You must log in to answer this question.

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