Social Network ER Diagram

A social network stores two kinds of relationships: content relationships (posts, comments, likes) and graph relationships between users — following and messaging. Both follow and likes are self-referencing or user-to-user joins, which makes this schema a good study of many-to-many patterns.

This schema holds two families of relationships. Content edges are ordinary one-to-many: a user authors posts, a post collects comments. Graph edges are user-to-user: follows has follower_id and followee_id, both foreign keys into users, and its composite primary key (follower_id, followee_id) makes a duplicate follow impossible. Because both columns point at the same table, the diagram draws two edges converging on follows — the visual signature of a self-referencing many-to-many. Nothing stops a user from following themselves; that takes a CHECK (follower_id <> followee_id).

likes is the same composite-key trick applied to content: (user_id, post_id) guarantees one like per user per post, and the kept created_at matters because activity feeds order by recency of the like, not of the post. Messaging is normalized one level deeper than it first appears: conversations is a bare container, conversation_participants is the join that lets a conversation have two members or twenty, and messages only knows its conversation and its sender_id. Nothing about the message table changes between a DM and a group chat.

Deletion cascades are aggressive here because a social graph is mostly derived state: deleting a user removes their posts, comments, likes, follows, memberships and notifications in one statement. messages.sender_id is the exception — no rule, so a conversation's history survives a departing member. notifications.payload is JSON because notification types differ too much to give each its own columns: a like notification and a follow notification share the table and differ only in payload shape. read_at being nullable is the unread marker.

Feeds are the first thing to denormalize: assembling a timeline by joining follows to posts works at small scale, then a feed_items table (user_id, post_id, score, created_at) written at post creation takes over. blocks (blocker_id, blocked_id, unique on the pair) is a near-copy of follows and belongs beside it, since every feed and message query must respect it. Counter columns such as likes_count on posts are a later, measured optimization — the join table stays the source of truth.

The diagram

1:N 1:N 1:N 1:1 1:1 1:1 1:1 1:N 1:N 1:N N:M users PKid integer Uusername varchar(30) Uemail varchar(255) bio text created_at timestamp posts PKid integer FKauthor_id integer content text created_at timestamp comments PKid integer FKpost_id integer FKauthor_id integer body text created_at timestamp likes PKuser_id integer PKpost_id integer created_at timestamp follows PKfollower_id integer PKfollowee_id integer created_at timestamp conversations PKid integer created_at timestamp conversation_participants PKconversation_id integer PKuser_id integer messages PKid integer FKconversation_id integer FKsender_id integer body text sent_at timestamp notifications PKid integer FKuser_id integer type varchar(30) payload json read_at timestamp created_at timestamp

Tables (9)

users

  • idintegerPK
  • usernamevarchar(30)U
  • emailvarchar(255)U
  • biotext
  • created_attimestamp

posts

  • idintegerPK
  • author_idintegerFK
  • contenttext
  • created_attimestamp

comments

  • idintegerPK
  • post_idintegerFK
  • author_idintegerFK
  • bodytext
  • created_attimestamp

likes

  • user_idintegerPK
  • post_idintegerPK
  • created_attimestamp

follows

  • follower_idintegerPK
  • followee_idintegerPK
  • created_attimestamp

conversations

  • idintegerPK
  • created_attimestamp

conversation_participants

  • conversation_idintegerPK
  • user_idintegerPK

messages

  • idintegerPK
  • conversation_idintegerFK
  • sender_idintegerFK
  • bodytext
  • sent_attimestamp

notifications

  • idintegerPK
  • user_idintegerFK
  • typevarchar(30)
  • payloadjson
  • read_attimestamp
  • created_attimestamp

Relationships (11)

  • 1:Nusers.id→posts.author_idON DELETE CASCADE
  • 1:Nposts.id→comments.post_idON DELETE CASCADE
  • 1:Nusers.id→comments.author_idON DELETE CASCADE
  • 1:1users.id→likes.user_idON DELETE CASCADE
  • 1:1posts.id→likes.post_idON DELETE CASCADE
  • 1:1users.id→follows.follower_idON DELETE CASCADE
  • 1:1users.id→follows.followee_idON DELETE CASCADE
  • 1:Nconversations.id→messages.conversation_idON DELETE CASCADE
  • 1:Nusers.id→messages.sender_id
  • 1:Nusers.id→notifications.user_idON DELETE CASCADE
  • N:Mconversations.id→users.id

The SQL

Copy this, paste it in the SQL tab, or just open it below.
SQL
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  username VARCHAR(30) NOT NULL UNIQUE,
  email VARCHAR(255) NOT NULL UNIQUE,
  bio TEXT,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  content TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  author_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  body TEXT NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE likes (
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  post_id INTEGER NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (user_id, post_id)
);

CREATE TABLE follows (
  follower_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  followee_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  created_at TIMESTAMP NOT NULL DEFAULT now(),
  PRIMARY KEY (follower_id, followee_id)
);

CREATE TABLE conversations (
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE conversation_participants (
  conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  PRIMARY KEY (conversation_id, user_id)
);

CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  conversation_id INTEGER NOT NULL REFERENCES conversations(id) ON DELETE CASCADE,
  sender_id INTEGER NOT NULL REFERENCES users(id),
  body TEXT NOT NULL,
  sent_at TIMESTAMP NOT NULL DEFAULT now()
);

CREATE TABLE notifications (
  id SERIAL PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  type VARCHAR(30) NOT NULL,
  payload JSON,
  read_at TIMESTAMP,
  created_at TIMESTAMP NOT NULL DEFAULT now()
);

Keep learning