Forum Database Schema database schema

What this schema models

A discussion forum database schema designed around the two decisions that determine whether a message board stays fast as it grows. 15 tables and 8 enums covering members and reputation, nested boards, topics and threaded replies with edit history, voting, watch subscriptions with unread tracking, cross-cutting tags, badges, and a full moderation pipeline with reports, actions and scoped bans. THE TWO DECISIONS 1. A TOPIC is not a POST. The topic is the thread container that carries the title, the board, the counters and the sort keys; posts are the replies, with is_first_post marking the opening body so the topic row never has to carry text. Collapsing both into one self-referencing table looks elegant for about a week, and then every board listing query needs a subselect just to find the root row. 2. Denormalised counters are DELIBERATE, not sloppy. A forum index page shows reply count, view count, vote score and last poster for dozens of topics at once. Computing those with aggregates on every page load is the single most common reason a forum falls over under load. They are documented as caches that can always be rebuilt from votes, posts and topic_views. The index that matters most is (forum_id, is_pinned, last_post_at) on topics — that composite IS the board listing query: pinned threads first, then most recent activity. DETAILS WORTH STEALING - forums.last_topic_id is deliberately NOT a foreign key. It is a display cache, and a cascade from it would be a liability during moderation. - votes uses a composite primary key on (user_id, post_id) with a value of +1 or -1, so a vote is idempotent and flipping it is an UPDATE rather than an accumulating pile of rows. - topic_subscriptions stores last_read_post_id as a high-water mark, not a row per post read. Unread count is a range query above that point. - topic_views is pre-aggregated per topic per day rather than one row per hit, and topics.view_count is its running total. - Boards nest hierarchically; tags cut across them. Both exist because a rigid board tree cannot express a cross-cutting subject and a flat tag list cannot express permissions. - A ban is a row in user_bans with a start, an expiry and an optional forum scope — not a boolean on users — so a history of sanctions survives and a per-board ban is possible. - moderation_actions is append-only and logs locking, pinning, moving, merging and deleting, which means moderator activity is itself reviewable. - Posts carry edit history in post_revisions. A forum without that cannot settle an argument about what somebody actually wrote. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — people, structure, content, engagement and moderation — with sticky notes explaining each decision and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a community platform or Q&A site, anyone replacing phpBB or Discourse with something custom, and students who need a realistic forum ER diagram for a DBMS project.

Tables (15)

Relationships (31)

• forums.parent_id references forums.id many to one
• moderation_actions.moderator_id references users.id many to one
• moderation_actions.report_id references reports.id many to one
• moderation_actions.target_post_id references posts.id many to one
• moderation_actions.target_topic_id references topics.id many to one
• moderation_actions.target_user_id references users.id many to one
• post_revisions.editor_id references users.id many to one
• post_revisions.post_id references posts.id many to one
• posts.author_id references users.id many to one
• posts.reply_to_post_id references posts.id many to one
• posts.topic_id references topics.id many to one
• reports.post_id references posts.id many to one
• reports.reported_user_id references users.id many to one
• reports.reporter_id references users.id many to one
• reports.topic_id references topics.id many to one
• topic_subscriptions.last_read_post_id references posts.id many to one
• topic_subscriptions.topic_id references topics.id many to one
• topic_subscriptions.user_id references users.id many to one
• topic_tags.tag_id references tags.id many to one
• topic_tags.topic_id references topics.id many to one
• topic_views.topic_id references topics.id many to one
• topics.author_id references users.id many to one
• topics.forum_id references forums.id many to one
• topics.last_post_user_id references users.id many to one
• user_badges.badge_id references badges.id many to one
• user_badges.user_id references users.id many to one
• user_bans.forum_id references forums.id many to one
• user_bans.issued_by references users.id many to one
• user_bans.user_id references users.id many to one
• votes.post_id references posts.id many to one
• votes.user_id references users.id many to one

How to use this template

1. Open the diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy.
3. Export it as PostgreSQL, MySQL, or MSSQL DDL, or as C# models.

Frequently asked questions

What is in the Forum Database Schema schema?

15 tables and 31 relationships, across 112 columns. The table names and the full relationship list are shown on this page.

Can I export it to PostgreSQL or MySQL?

Yes. Every DB Planner template exports as DBML, PostgreSQL, MySQL, or MSSQL DDL, and as C# models.

Is this template free?

Yes. You can open it in the browser without an account, and copy it into your own workspace with a free DB Planner account.

Can I change it after copying it?

Yes. A copy is yours: you can rename tables, add columns, and export it. Changes the author makes later do not affect your copy.