Social Media Database Schema database schema

What this schema models

A social network database schema modelled on how real platforms are actually built. 35 tables, 13 enums and 66 relationships covering identity and sessions, a directed follow graph with blocks and mutes, threaded posts with media and hashtags, likes, reposts and bookmarks, comments, polls, expiring stories, group and direct messaging, materialised feeds, notification preferences, groups, and a full trust-and-safety pipeline. THE THREE RULES 1. The graph is DIRECTED. follows is one-way and mutual following is two rows. blocks and mutes are separate concepts with different semantics: a block is mutual invisibility that should also tear down the follow rows, while a mute is invisible to the muted user, leaves the follow intact, and only filters the feed. Getting this wrong is the single most common mistake in social schema design. 2. Engagement is a ROW, never a counter. post_likes, reposts and bookmarks are tables with composite primary keys, which makes a like idempotent, un-doable and auditable. The count columns on posts are explicitly denormalised caches, rebuildable from those tables. 3. The feed is MATERIALISED. feed_entries is written on fan-out rather than computed by joining the graph at read time, because a query that walks follows on every page load is exactly the thing that stops scaling. It also stores WHY a post is in the timeline (followed, reposted, group, recommended) plus the source user, which is what lets the UI explain itself. OTHER DETAILS WORTH STEALING - users holds the handle and credentials; profiles is a one-to-one split out because it is read on every profile view and written far more often. A rename never touches the auth row. - posts self-references for threads AND carries a nullable group_id, so one table serves the public timeline and every group. There is no separate group_posts table. - Comments are deliberately NOT posts: they are never independently followable and never enter fan-out. - Message read state is a high-water mark per participant, not a row per message, so unread count is a cheap range query. - Stories write expires_at at creation rather than inferring it, and the index on that column is what lets a sweeper reap expired rows cheaply. - A suspension is a row in moderation_actions with an expires_at, not a boolean flag on users, so the history survives an appeal. - Mentions and hashtags are extracted at write time, so the mentions tab and tag pages are plain index lookups instead of text scans. WHAT YOU GET Clean DBML that imports straight into DB Planner, plus a laid-out board with cards colour-coded by zone — identity, graph, content, engagement, ephemeral, messaging, delivery, groups and trust & safety — with sticky notes explaining each design decision and every table carrying its own Note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a social app, students who need a realistic social network ER diagram for a DBMS project, and teams designing feed and notification infrastructure for the first time.

Tables (35)

Relationships (66)

• blocks.blocked_id references users.id many to one
• blocks.blocker_id references users.id many to one
• bookmarks.post_id references posts.id many to one
• bookmarks.user_id references users.id many to one
• comment_likes.comment_id references comments.id many to one
• comment_likes.user_id references users.id many to one
• comments.author_id references users.id many to one
• comments.parent_comment_id references comments.id many to one
• comments.post_id references posts.id many to one
• conversation_participants.conversation_id references conversations.id many to one
• conversation_participants.user_id references users.id many to one
• conversations.created_by references users.id many to one
• feed_entries.post_id references posts.id many to one
• feed_entries.source_user_id references users.id many to one
• feed_entries.user_id references users.id many to one
• follows.followee_id references users.id many to one
• follows.follower_id references users.id many to one
• group_members.group_id references groups.id many to one
• group_members.user_id references users.id many to one
• groups.owner_id references users.id many to one
• list_members.list_id references lists.id many to one
• list_members.user_id references users.id many to one
• lists.owner_id references users.id many to one
• message_attachments.message_id references messages.id many to one
• message_reads.conversation_id references conversations.id many to one
• message_reads.last_read_message_id references messages.id many to one
• message_reads.user_id references users.id many to one
• messages.conversation_id references conversations.id many to one
• messages.reply_to_id references messages.id many to one
• messages.sender_id references users.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_user_id references users.id many to one
• mutes.muted_id references users.id many to one
• mutes.muter_id references users.id many to one
• notification_preferences.user_id references users.id many to one
• notifications.actor_id references users.id many to one
• notifications.comment_id references comments.id many to one
• notifications.post_id references posts.id many to one
• notifications.user_id references users.id many to one
• poll_options.poll_id references polls.id many to one
• poll_votes.poll_option_id references poll_options.id many to one
• poll_votes.user_id references users.id many to one
• polls.post_id references posts.id many to one
• post_hashtags.hashtag_id references hashtags.id many to one
• post_hashtags.post_id references posts.id many to one
• post_likes.post_id references posts.id many to one
• post_likes.user_id references users.id many to one
• post_media.post_id references posts.id many to one
• post_mentions.post_id references posts.id many to one
• post_mentions.user_id references users.id many to one
• posts.author_id references users.id many to one
• posts.group_id references groups.id many to one
• posts.parent_post_id references posts.id many to one
• reports.comment_id references comments.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
• reposts.post_id references posts.id many to one
• reposts.user_id references users.id many to one
• sessions.user_id references users.id many to one
• stories.author_id references users.id many to one
• story_views.story_id references stories.id many to one
• story_views.viewer_id references users.id many to one
• users.id references profiles.user_id one 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 Social Media Database Schema schema?

35 tables and 66 relationships, across 203 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.