Social network Schema

Interactive diagram

Drag to pan, scroll to zoom. Open it in the full editor to edit, rearrange and export.

About this schema

Users follow other users (self-referencing many-to-many via follows), create posts, like and comment on them, and send each other direct messages.

Schema (SQL)

CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  handle VARCHAR(30) UNIQUE NOT NULL,
  display_name VARCHAR(80), avatar_url TEXT
);
CREATE TABLE follows (
  follower_id INT NOT NULL REFERENCES users(id),
  followee_id INT NOT NULL REFERENCES users(id),
  created_at TIMESTAMP,
  PRIMARY KEY (follower_id, followee_id)
);
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  author_id INT NOT NULL REFERENCES users(id),
  body VARCHAR(280), created_at TIMESTAMP
);
CREATE TABLE likes (
  user_id INT NOT NULL REFERENCES users(id),
  post_id INT NOT NULL REFERENCES posts(id),
  PRIMARY KEY (user_id, post_id)
);
CREATE TABLE comments (
  id SERIAL PRIMARY KEY,
  post_id INT NOT NULL REFERENCES posts(id),
  author_id INT NOT NULL REFERENCES users(id),
  body VARCHAR(280), created_at TIMESTAMP
);
CREATE TABLE messages (
  id SERIAL PRIMARY KEY,
  sender_id INT NOT NULL REFERENCES users(id),
  recipient_id INT NOT NULL REFERENCES users(id),
  body TEXT, sent_at TIMESTAMP
);

Open in the editor → to export it as PNG, SVG, Mermaid or DBML.

More examples