Stack Exchange releases "data dumps" of all its publicly available content roughly every three months via archive.org, and also makes that information queryable over the Internet at the Stack Exchange Data Explorer (SEDE) (updated weekly, on Sunday). Over time, as new features and other bits of data are introduced to Stack Exchange sites, the "anatomy" of the data dump is likely to change.
2 Answers
About this list:
- foreign key fields are formatted as links to their parent table
- italic table names are found in both the Data Dump on Archive.org as well as in the SEDE
Posts / PostsWithDeleted
You find in Posts
all non-deleted posts. PostsWithDeleted
includes rows with deleted posts while sharing the same columns with Posts
but for deleted posts only a few fields populated which are marked with a 1 below.
Id
1PostTypeId
1 (listed in thePostTypes
table)
1 = Question
2 = Answer
3 = Orphaned tag wiki
4 = Tag wiki excerpt
5 = Tag wiki
6 = Moderator nomination
7 = "Wiki placeholder" (Appears to include auxiliary site content like the help center introduction, election description, and the tour page's introduction, ask, and don't ask sections)
8 = Privilege wikiAcceptedAnswerId
(only present ifPostTypeId = 1
)ParentId
1 (only present ifPostTypeId = 2
)CreationDate
1DeletionDate
1 (only non-null for the SEDEPostsWithDeleted
table. Deleted posts are not present onPosts
. Column not present on data dump.)Score
1 (generally non-zero for only Questions, Answers, and Moderator Nominations)ViewCount
(nullable)Body
(as rendered HTML, not Markdown)OwnerUserId
(only present if user has not been deleted; always -1 for tag wiki entries, i.e. the community user owns them)OwnerDisplayName
(nullable)LastEditorUserId
(nullable)LastEditorDisplayName
(nullable)LastEditDate
(e.g.2009-03-05T22:28:34.823
) - the date and time of the most recent edit to the post (nullable)LastActivityDate
(e.g.2009-03-11T12:51:01.480
) - datetime of the post's most recent activityTitle
- question title (PostTypeId = 1
), or on Stack Overflow, the tag name for some tag wikis and excerpts (PostTypeId = 4/5
)Tags
1 - question tags (PostTypeId = 1
), or on Stack Overflow, the subject tag of some tag wikis and excerpts (PostTypeId = 4/5
)AnswerCount
- the number of undeleted answers (only present ifPostTypeId = 1
)CommentCount
(nullable)FavoriteCount
(nullable)ClosedDate
1 (present only if the post is closed)CommunityOwnedDate
(present only if post is community wiki'd)ContentLicense
1
Users
Id
Reputation
CreationDate
DisplayName
LastAccessDate
(Datetime user last loaded a page; updated every 30 min at most)WebsiteUrl
Location
AboutMe
Views
(Number of times the profile is viewed)UpVotes
(How many upvotes the user has cast)DownVotes
ProfileImageUrl
EmailHash
(now always blank)AccountId
(User's Stack Exchange Network profile ID)
Comments
Id
PostId
Score
Text
(Comment body)CreationDate
UserDisplayName
UserId
(Optional. Absent if user has been deleted)ContentLicense
Badges
Id
Name
(Name of the badge)Date
(e.g.2008-09-15T08:55:03.923
)Class
1 = Gold
2 = Silver
3 = BronzeTagBased
=True
if badge is for a tag, otherwise it is a named badge
CloseAsOffTopicReasonTypes
Id
IsUniversal
InputTitle
MarkdownInputGuidance
shown while flagging/votingMarkdownPostOwnerGuidance
shown when closed to OPMarkdownPublicGuidance
shown when closed to priviledged usersMarkdownConcensusDescription
(sic) (nullable) shown above the public or post owner guidance.CreationDate
CreationModeratorId
ApprovalDate
ApprovalModeratorId
DeactivationDate
DeactivationModeratorId
PendingFlags
Despite the name, this table in fact contains close-related flags and votes.
Id
FlagTypeId
(listed in theFlagTypes
table)
13 = canned flag for closure
14 = vote to close
15 = vote to reopenCreationDate
CloseReasonTypeId
(listed in theCloseReasonTypes
table)CloseAsOffTopicReasonTypeId
, ifCloseReasonTypeId = 102 (off-topic)
(listed in theCloseAsOffTopicReasonTypes
table)DuplicateOfQuestionId
, ifCloseReasonTypeId
is 1 or 101 (old duplicate or current duplicate)BelongsOnBaseHostAddress
, for votes to close and migrate
PostFeedback
Collects up and down votes from anonymous visitor and/or unregistered users. See here
PostHistory
(Note that the history of deleted posts is scrubbed from this table in SEDE.)
Id
PostHistoryTypeId
(listed in thePostHistoryTypes
table)
1 = Initial Title - initial title (questions only)
2 = Initial Body - initial post raw body text
3 = Initial Tags - initial list of tags (questions only)
4 = Edit Title - modified title (questions only)
5 = Edit Body - modified post body (raw markdown)
6 = Edit Tags - modified list of tags (questions only)
7 = Rollback Title - reverted title (questions only)
8 = Rollback Body - reverted body (raw markdown)
9 = Rollback Tags - reverted list of tags (questions only)
10 = Post Closed - post voted to be closed
11 = Post Reopened - post voted to be reopened
12 = Post Deleted - post voted to be removed
13 = Post Undeleted - post voted to be restored
14 = Post Locked - post locked by moderator
15 = Post Unlocked - post unlocked by moderator
16 = Community Owned - post now community owned
17 = Post Migrated - post migrated - now replaced by 35/36 (away/here)
18 = Question Merged - question merged with deleted question
19 = Question Protected - question was protected by a moderator.
20 = Question Unprotected - question was unprotected by a moderator.
21 = Post Disassociated - OwnerUserId removed from post by admin
22 = Question Unmerged - answers/votes restored to previously merged question
24 = Suggested Edit Applied
25 = Post Tweeted
31 = Comment discussion moved to chat
33 = Post notice added -comment
contains foreign key to PostNotices
34 = Post notice removed -comment
contains foreign key to PostNotices
35 = Post migrated away - replaces id 17
36 = Post migrated here - replaces id 17
37 = Post merge source
38 = Post merge destination
50 = Bumped by Community User
52 = Question became hot network question (main) / Hot Meta question (meta)
53 = Question removed from hot network/meta questions by a moderator
Additionally, in older dumps (all guesses, all seem no longer present in the wild):
23 = Unknown dev related event
26 = Vote nullification by dev (ERM?)
27 = Post unmigrated/hidden moderator migration?
28 = Unknown suggestion event
29 = Unknown moderator event (possibly de-wikification?)
30 = Unknown event (too rare to guess)
RevisionGUID
: At times more than one type of history record can be recorded by a single action. All of these will be grouped using the same RevisionGUIDCreationDate
(e.g.2009-03-05T22:28:34.823
)UserDisplayName
: populated if a user has been removed and no longer referenced by user IdComment
: This field will contain the comment made by the user who edited a post.If PostHistoryTypeId = 10, this field contains the CloseReasonId of the close reason (listed in
CloseReasonTypes
):
Old close reasons:
1 = Exact Duplicate
2 = Off-topic
3 = Subjective and argumentative
4 = Not a real question
7 = Too localized
10 = General reference
20 = Noise or pointless (Meta sites only)
Current close reasons:
101 = Duplicate
102 = Off-topic
103 = Unclear what you're asking
104 = Too broad
105 = Primarily opinion-basedIf
PostHistoryTypeId in (33,34)
this field contains thePostNoticeId
of thePostNotice
Text
: A raw version of the new value for a given revision
- IfPostHistoryTypeId in (10,11,12,13,14,15,19,20,35)
this column will contain a JSON encoded string with all users who have voted for thePostHistoryTypeId
- If it is a duplicate close vote, the JSON string will contain an array of original questions asOriginalQuestionIds
- IfPostHistoryTypeId = 17
this column will contain migration details of eitherfrom <url>
orto <url>
ContentLicense
PostLinks
Id
primary keyCreationDate
when the link was createdPostId
id of source postRelatedPostId
id of target/related postLinkTypeId
type of link
1 = Linked (PostId
contains a link toRelatedPostId
)
3 = Duplicate (PostId
is a duplicate ofRelatedPostId
)
PostNotices
Id
PostId
PostNoticeTypeId
1 = Citation needed
2 = Current event
3 = Insufficient explanation
10 = Current answers are outdated
11 = Draw attention
12 = Improve details
13 = Authoritative reference needed
14 = Canonical answer required
15 = Reward existing answer
20 = Content dispute
21 = Offtopic comments
22 = Historical significance
23 = Wiki AnswerCreationDate
DeletionDate
ExpiryDate
Body
(when present contains the custom text shown with the notice)OwnerUserId
DeletionUserId
PostNoticeTypes
Id
ClassId
1 = Historical lock
2 = Bounty
4 = Moderator noticeName
Body
(contains the default notice text)IsHidden
Predefined
PostNoticeDurationId
-1 = No duration specified
1 = 7 days (bounty)
PostTags
ReviewRejectionReasons
Canned rejection reasons for suggested edits. See Show all review rejection reasons
Id
Name
Description
PostTypeId
(for reasons that apply to Wiki (5) or Excerpt (6) post types only, otherwise null)
ReviewTaskResults
Id
ReviewTaskResultTypeId
(listed inReviewTaskResultTypes
)
1 = Skip
2 = Approve (suggested edits)
3 = Reject (suggested edits)
4 = Delete (low quality)
5 = Edit (first posts, late answers, low quality)
6 = Close (close, low quality)
7 = Looks OK (low quality)
8 = Do Not Close (close)
9 = Recommend Deletion (low quality answer)
10 = Recommend Close (low quality question)
11 = Other Action (first posts), previously "I'm Done"
12 = Reopen (reopen)
13 = Leave Closed (reopen)
14 = Edit and Reopen (reopen)
15 = Excellent (community evaluation)
16 = Satisfactory (community evaluation)
17 = Needs Improvement (community evaluation)
18 = No Action Needed (first posts, late answers)CreationDate
date only (2018-07-31 00:00:00
); time data looks to be purposefully removed to protect user privacyRejectionReasonId
(for suggested edits; listed inReviewRejectionReasons
)Comment
ReviewTasks
Id
ReviewTaskTypeId
(listed inReviewTaskTypes
)
1 = Suggested Edit
2 = Close Votes
3 = Low Quality Posts
4 = First Post
5 = Late Answer
6 = Reopen Vote
7 = Community Evaluation
8 = Link Validation
9 = Flagged Posts
10 = Triage
11 = Helper
12 = First Questions
13 = First AnswersCreationDate
date only (2018-07-31 00:00:00
)DeletionDate
date only (2018-07-31 00:00:00
)ReviewTaskStateId
(listed inReviewTaskStates
)
1 = Active
2 = Completed
3 = InvalidatedSuggestedEditId
(for suggested edits, which have their own numbering for historical reasons)CompletedByReviewTaskId
id associated to the ReviewTaskResult that stores the outcome of a completed review.
SuggestedEdits
If both approval and rejection date are null then this edit is still in review (and its corresponding entry in ReviewTasks
will have an active state as well).
Id
PostId
CreationDate
ApprovalDate
- NULL if not approved (yet).RejectionDate
- NULL if not rejected (yet).OwnerUserId
Comment
Text
Title
Tags
RevisionGUID
SuggestedEditVotes
Id
SuggestedEditId
UserId
VoteTypeId
(listed in theVoteTypes
table)
2 = Approve (technically UpMod)
3 = Reject (technically DownMod)CreationDate
TargetUserId
TargetRepChange
Tags
Id
TagName
Count
ExcerptPostId
(nullable) Id of Post that holds the excerpt text of the tagWikiPostId
(nullable) Id of Post that holds the wiki text of the tagIsModeratorOnly
IsRequired
TagSynonyms
Id
SourceTagName
(nullable)TargetTagName
(nullable)CreationDate
OwnerUserId
(nullable)AutoRenameCount
LastAutoRename
(nullable)Score
ApprovedByUserId
(nullable)ApprovalDate
(nullable)
Votes
Id
PostId
VoteTypeId
(listed in theVoteTypes
table)
1 = AcceptedByOriginator
2 = UpMod (AKA upvote)
3 = DownMod (AKA downvote)
4 = Offensive
5 = Favorite (AKA bookmark;UserId
will also be populated) feature removed after October 2022 / replaced by Saves
6 = Close (effective 2013-06-25: Close votes are only stored in table:PostHistory
)
7 = Reopen
8 = BountyStart (UserId
andBountyAmount
will also be populated)
9 = BountyClose (BountyAmount
will also be populated)
10 = Deletion
11 = Undeletion
12 = Spam
15 = ModeratorReview (i.e., a moderator looking at a flagged post)
16 = ApproveEditSuggestionUserId
(present only ifVoteTypeId in (5,8)
; -1 if user is deleted)CreationDate
Date only (2018-07-31 00:00:00
time data is purposefully removed to protect user privacy)BountyAmount
(present only ifVoteTypeId in (8,9)
)
xxxTypes
Not listed here:
xxxTypes
tables which list (Id, Name) pairs forPosts.PostTypeId
,Votes.VoteTypeId
, etc. See Show all types for an up-to-date list of all types.
All Tables/Columns/Type
Find the exact T-SQL datatype and length/precision of each specific column in this query:
List all Fields in all Tables on SEDE
TIMESTAMPS
All timestamps are UTC
, default format: yyyy-MM-dd hh:mm:ss
(stored with milliseconds).
Example of conversion current time to PST
(including DST) using At Time Zone
:
SELECT GetDate() At Time Zone 'UTC' At Time Zone 'Pacific Standard Time'
To list time zones: SELECT * FROM sys.time_zone_info
-
For PostNoticeTypes, there appears to be two
classId
s other than the mentioned 1, 2, & 4. Stack Overflow additionally as 8 and 0, it seems most (all?) other sites have just the 8. Is there an official name for this class of notices?– zcoop98Apr 2, 2021 at 22:42 -
1@zcoop98 I've run a check and you're right, only 1 site has 0 as classid. I checked but can't find info on what the name of those classid's is. Feel free to open a question to get that name. Here is the answer meta.stackexchange.com/a/278150 that announces the addition of those tables. Surprisingly enough I was the requestor to get those tables added in the first place.– reneApr 22, 2021 at 16:51
-
Why is there UserName in Post and Comment? Is it to avoid a join? Are the username entries in posts and comments updated if user updates the username? Jun 1, 2021 at 8:09
-
1@chaosifier Username is populated when a User is deleted. So once the Userid or OwnerUserId would be set to null the Usename field gets populated with the last known name (although most of the time the username is reset to default first)– reneJun 1, 2021 at 8:17
-
How can you tell if a vote is for a comment or for a post? It seems like the schema only handles votes for posts.– Alex JJun 10, 2021 at 19:23
-
2@AlexJ Comments only have upvotes and those are kept in the field ` Score` . There is no separate table for CommentVotes. So the votes(votetypeid in (2,3)) table has the rows for up and downvotes of posts alone.– reneJun 10, 2021 at 19:41
-
@rene Interesting, what keeps a user from making multiple upvotes on a given comment? How is the vote on a comment related back to the user that makes it?– Alex JJun 10, 2021 at 20:05
-
1
-
-
1
-
What does AcceptedAnswerId mean? I wish there was documentation specifically telling about it. Nov 20, 2022 at 15:59
-
1@desert_ranger see Which Stack Overflow table saves the data for accepted answers?– reneNov 20, 2022 at 19:11
The Schema Visualized
The Entity Relationship Diagram (ERD) above was constructed using the data found in the information_schema.columns table. (Thank you to ashleedawg for paring the table down to its bare essentials).
All the table, column and key information were compiled into a SQL script using python3. The results were cross validated with the accepted answer above, and the few idiosyncratic foreign key formations left over were hardcoded in at the end.)
The ERD was then reverse engineered using dbschema.
Please find the script that wrote the SQL and other related files in my GitHub repository.
The interactive HTML version of the ERD is posted here: https://sedeschema.github.io/
-
1
-
3for further information about ERD key relationship representations (and the start of a path down into the UML structure and format hole) please see here: dba.stackexchange.com/questions/76532/…– leerssejJan 3, 2021 at 15:13
-
@leerssej It is not explained in the provided reference what the dashed/solid line styles stand for. At least I couldn't find this. Jul 4 at 0:26
-
@paperskilltrees Please see the third sentence in the first response. Link here: dba.stackexchange.com/a/76547/109673– leerssejJul 7 at 22:03
-
@leerssej Thanks, however the linked answer does not explicitly mention a line being solid or dashed: "ER Diagrams were originally used only to represent the ER model. The ER model does not use foreign keys to represent relationships. It uses lines between boxes. The lines have some kind of indicator for cardinality at either end or both ends. Sometimes, a relationship will be indicated separately by a diamond." What is confusing to me is whether dashing conveys additional information to symbols at the ends of a line or not. ... [1/n] Jul 7 at 23:32
-
You seem to imply that dashing is an indicator of cardinality and should be considered together with the end symbols. This agrees with dbSchema documentation, although it does not show exactly the same notation you used in the picture (you have "P", "Z", or "1" at the ends; the docs show Crow's foot and notches). ... [2/3] Jul 7 at 23:32
-
On the other hand, this shows that dashed (non-solid) line indicates something on top of those symbols; something related to identifying or non-identifying relationships. This variety of notations and possibilities make the topic confusing for a non-expert, and it would be great to know at least something with certainty. [3/3] Jul 7 at 23:32
-
2@paperskilltrees Ah! Now I understand your question: what is the difference between the dashed lines and the solid lines? Thank you for the clarification! Maybe this stackoverflow.com/a/16697896/5088194 or this can help stackoverflow.com/a/16676282– leerssejJul 10 at 3:28