Complete database schema for a Reddit-like community platform featuring user accounts, subreddit communities, post submissions, nested comment threads, karma voting on posts and comments, awards system, content moderation, user flairs, post flairs, saved content, and ban management.
Votes are rows, not counters. post_votes and comment_votes each store one row per voter with a vote_type, which is the only way to stop the same person voting twice and the only way to let them change their mind. The score column on posts and comments is the cached sum, kept because sorting a front page by a count that has to be recomputed from millions of rows is not a query anyone can afford.
Comments nest through parent_comment_id pointing back at comments, and carry a depth column beside it. depth is redundant — it can be derived by walking up — but it is stored so that rendering a thread can order and indent in one pass, and so a reply can be refused past a limit without a recursive read first.
Moderation is scoped to the subreddit, never to the platform. moderators, bans, post_flairs and user_flairs all key on subreddit_id, so the same account can be a moderator in one community, banned in another, and an ordinary member everywhere else. Only users.is_admin sits above that, and it is a single flag rather than a role table.
The honest number is count(upvotes) - count(downvotes) over the votes table, and on a busy post that is a scan nobody wants on every page render. posts keeps score, upvote_count and comment_count so a listing sorts on an indexed column. The price is that a vote write must touch two places, and the counts can drift — which is why they are periodically recomputed rather than trusted forever.
is_removed on posts and comments keeps the row in place when a moderator takes something down. Deleting it would orphan every reply beneath it and silently renumber the thread for everyone reading. Keeping the row means the children still hang off a valid parent, and the moderation log in reports still points at something that exists.
post_flairs is the set a subreddit defines; user_flairs is what a specific member wears in that subreddit. They look similar enough to merge, but one is a vocabulary and the other is an assignment — merging them would make renaming a community's flair rewrite what individual members are labelled.
banned_user_id and banned_by_id sit in the same row, with expires_at nullable for a permanent ban. Storing only the banned user would make a contested ban unanswerable, and putting the expiry on the user would mean a person banned from two communities could only be banned until one date.