Blog Database Schema database schema

What this schema models

A blog database schema designed around the two things that actually decide whether a content site survives: stable URLs and a real revision history. 15 tables and 6 enums covering authors and roles, posts with full version history, hierarchical categories and flat tags, a reusable media library, threaded comments with moderation, double opt-in subscribers, SEO metadata, redirects and pre-aggregated view analytics. THE TWO DECISIONS 1. The SLUG is the product. Every public URL is a slug, slugs are unique, and once a post is published its slug is treated as immutable. Changing one writes a row in redirects rather than editing the post in place. A blog that quietly changes URLs throws away every backlink it ever earned — the redirects table with its 301/302 status_code and hit_count is what makes a restructure survivable, and shows you which old URLs still carry traffic. 2. Post body is VERSIONED. posts holds the current live state; post_revisions holds every prior version with an editor, a revision number and a change summary. Publishing is a timestamp, not a separate table — and a published_at in the future IS the scheduling mechanism, so there is no queue table to drift out of sync with reality. DETAILS WORTH STEALING - Categories are few, curated and hierarchical; tags are many, flat and author-created. They are deliberately separate tables because they behave differently, and post_categories.is_primary picks the one used for breadcrumbs and the canonical URL. - seo_meta uses meta_title varchar(70) and meta_description varchar(160) on purpose — those are roughly what a search result actually renders, so the schema itself stops an editor writing something that will be truncated. - media is a reusable library with a many-to-many join, so one image can appear in several posts without being re-uploaded. alt_text is treated as required in practice: it is both accessibility and image search. - Comments allow guests, which is why author_name and author_email are denormalised onto the row and user_id is nullable. Self-referencing parent_id gives threads. - Subscribers use double opt-in: confirmed_at null means pending and must never be mailed. Unsubscribe is a timestamp, never a delete, so a careless re-import cannot resurrect someone who opted out. - post_views is pre-aggregated per post per day per referrer rather than one row per hit. Raw hit logs belong in an analytics pipeline, not in the blog database. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — people, content, taxonomy, media, comments, and SEO & analytics — 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 blog or publishing platform, anyone moving off WordPress or Ghost to something custom, students who need a realistic blog ER diagram, and content teams who want the SEO model designed in from the start rather than bolted on later.

Tables (15)

Relationships (19)

• categories.parent_id references categories.id many to one
• comment_reports.comment_id references comments.id many to one
• comments.parent_id references comments.id many to one
• comments.post_id references posts.id many to one
• comments.user_id references users.id many to one
• media.uploaded_by references users.id many to one
• post_categories.category_id references categories.id many to one
• post_categories.post_id references posts.id many to one
• post_media.media_id references media.id many to one
• post_media.post_id references posts.id many to one
• post_revisions.editor_id references users.id many to one
• post_revisions.post_id references posts.id many to one
• post_tags.post_id references posts.id many to one
• post_tags.tag_id references tags.id many to one
• post_views.post_id references posts.id many to one
• posts.author_id references users.id many to one
• posts.featured_media_id references media.id many to one
• seo_meta.category_id references categories.id many to one
• seo_meta.post_id references posts.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 Blog Database Schema schema?

15 tables and 19 relationships, across 114 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.