Photo Sharing ER diagram and database schema

What this schema models

A complete photo sharing app database design, the kind of data model behind an app similar to Instagram: 28 tables, 13 enums and 60 relationships covering accounts, profiles and follows (with requests for private accounts), carousel posts with media variants, hashtags, mentions and photo tags, likes, threaded comments and saved collections, expiring stories with views and highlights, notifications and push devices, and blocks, mutes, reports and moderation actions. THE FOUR DESIGN RULES 1. Counters are caches; rows are the truth. Like, comment, follower and hashtag counts are denormalized for fast feeds and profile headers, but post_likes, comments, follows and post_hashtags are authoritative. Each cache is incremented in the same transaction and reconciled by a background job; when the two disagree, the rows win. 2. A post is a container, media are files. The post holds the caption and settings; each photo or video is an ordered post_media row (that is all a carousel is), with resized and transcoded variants by storage key. Bytes never live in the database, and a phone on a slow network never downloads the original. 3. A follow request is a state, not a separate table. Following a private account inserts the same follows row in state pending; approving is an UPDATE, and every visibility check reads only accepted rows. 4. Expiry and removal are timestamps, not deletes. Stories carry expires_at and feeds filter on it, so an expired story can still go into a highlight. Moderators never hard-delete: posts, comments and stories point at the moderation action that removed them, and a successful appeal is a new action that reverses it. DETAILS WORTH STEALING - Photo tags store x and y as fractions of the image (0 to 1), so a tag lands on the same spot in every resized variant. - Hashtags are derived from the caption on every save: the caption is the source, post_hashtags is its index. - Replies are one level deep: replying to a reply points at the same top-level comment and mentions the person. - Collections are folders over saves, joined by a composite foreign key that cascades, so unsaving removes a post from every collection at once. - Notifications carry a group_key, so "sam and 41 others liked your post" is grouped at read time and an undone like deletes exactly its own row. - Blocks are indexed in both directions because every read path checks both; a mute is a quiet unfollow the muted person never sees. WHAT YOU GET Clean DBML that imports directly into DB Planner, and a laid-out board with zones color-coded and framed: accounts and follows, posts and media, engagement, stories, notifications, and trust and safety. Every table carries a note. Postgres-flavoured types that port cleanly to MySQL or SQL Server. Useful for developers building a photo sharing or social media app, and for students who need a realistic photo sharing app ER diagram for a DBMS project. This is a schema design reference; feed ranking, media processing and content policy belong in your own services.

Tables (28)

Relationships (60)

• blocks.blocked_id references users.id many to one
• blocks.blocker_id references users.id many to one
• close_friends.friend_id references users.id many to one
• close_friends.owner_id references users.id many to one
• collection_posts.collection_id references collections.id many to one
• collection_posts.user_id references saves.user_id many to one
• collections.cover_post_id references posts.id many to one
• collections.owner_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
• comments.removed_by_action_id references moderation_actions.id many to one
• follows.followee_id references users.id many to one
• follows.follower_id references users.id many to one
• highlight_stories.highlight_id references highlights.id many to one
• highlight_stories.story_id references stories.id many to one
• highlights.owner_id references users.id many to one
• media_variants.post_media_id references post_media.id many to one
• mentions.comment_id references comments.id many to one
• mentions.mentioned_user_id references users.id many to one
• mentions.post_id references posts.id many to one
• moderation_actions.comment_id references comments.id many to one
• moderation_actions.moderator_id references users.id many to one
• moderation_actions.post_id references posts.id many to one
• moderation_actions.report_id references reports.id many to one
• moderation_actions.reverses_action_id references moderation_actions.id many to one
• moderation_actions.story_id references stories.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
• 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.recipient_id references users.id many to one
• notifications.story_id references stories.id many to one
• photo_tags.post_media_id references post_media.id many to one
• photo_tags.tagged_user_id references users.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
• posts.author_id references users.id many to one
• posts.place_id references places.id many to one
• posts.removed_by_action_id references moderation_actions.id many to one
• push_devices.user_id references users.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
• reports.story_id references stories.id many to one
• saves.post_id references posts.id many to one
• saves.user_id references users.id many to one
• stories.author_id references users.id many to one
• stories.removed_by_action_id references moderation_actions.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 ER diagram to see the tables and how they relate.
2. Copy it into your workspace. You get your own editable copy, in DBML.
3. Export it as PostgreSQL, MySQL, or SQL Server DDL, or as C# models.

The copy opens in DB Planner’s ER diagram tool, with the schema as text in the DBML editor beside it. Have a database already? Turn its SQL into an ER diagram instead.

Frequently asked questions

What is in the Photo Sharing database schema?

28 tables and 60 relationships, across 192 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, as PostgreSQL, MySQL, or SQL Server 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.