Social app schema (users, posts, follows) template

Tables in this data model

• users — 5 columns
• posts — 5 columns
• media — 4 columns
• follows — 3 columns
• likes — 2 columns
• hashtags — 2 columns
• post_hashtags — 2 columns
• direct_messages — 5 columns
• notifications — 4 columns

How to use this ER diagram template

1. Click Open in the editor. The ER diagram opens on a new page in FlowDiagram — nothing is uploaded and there is no sign-up.
2. Rename, add or delete shapes and drag them where you want; connectors stay attached as things move.
3. Drop in vendor logos from the library — 1,160 of them, including every AWS, Azure and Google Cloud service icon — or restyle it with the colour and theme controls.
4. Prefer text? Edit the SQL DDL below and paste it in through ☰ → Import / export SQL; the finished model exports back to DDL.
5. Export to PNG, SVG or PDF, or press Ctrl+L for a link that carries the whole diagram.

SQL DDL source

The whole template is the text below. Paste it into FlowDiagram, keep it in Git next to the code it describes, or hand it to an AI assistant to extend.

CREATE TABLE users (
  user_id    INTEGER      NOT NULL PRIMARY KEY,
  handle     VARCHAR(40)  NOT NULL UNIQUE,
  email      VARCHAR(255) NOT NULL UNIQUE,
  bio        VARCHAR(300),
  created_at TIMESTAMP    NOT NULL
);

CREATE TABLE posts (
  post_id    INTEGER      NOT NULL PRIMARY KEY,
  user_id    INTEGER      NOT NULL REFERENCES users(user_id),
  body       VARCHAR(280) NOT NULL,
  reply_to   INTEGER      REFERENCES posts(post_id),
  posted_at  TIMESTAMP    NOT NULL
);

CREATE TABLE media (
  media_id INTEGER      NOT NULL PRIMARY KEY,
  post_id  INTEGER      NOT NULL REFERENCES posts(post_id),
  url      VARCHAR(500) NOT NULL,
  kind     VARCHAR(10)  NOT NULL
);

CREATE TABLE follows (
  follower_id INTEGER   NOT NULL REFERENCES users(user_id),
  followee_id INTEGER   NOT NULL REFERENCES users(user_id),
  created_at  TIMESTAMP NOT NULL
);

CREATE TABLE likes (
  user_id INTEGER NOT NULL REFERENCES users(user_id),
  post_id INTEGER NOT NULL REFERENCES posts(post_id)
);

CREATE TABLE hashtags (
  hashtag_id INTEGER     NOT NULL PRIMARY KEY,
  tag        VARCHAR(80) NOT NULL UNIQUE
);

CREATE TABLE post_hashtags (
  post_id    INTEGER NOT NULL REFERENCES posts(post_id),
  hashtag_id INTEGER NOT NULL REFERENCES hashtags(hashtag_id)
);

CREATE TABLE direct_messages (
  message_id   INTEGER   NOT NULL PRIMARY KEY,
  sender_id    INTEGER   NOT NULL REFERENCES users(user_id),
  recipient_id INTEGER   NOT NULL REFERENCES users(user_id),
  body         TEXT      NOT NULL,
  sent_at      TIMESTAMP NOT NULL
);

CREATE TABLE notifications (
  notification_id INTEGER     NOT NULL PRIMARY KEY,
  user_id         INTEGER     NOT NULL REFERENCES users(user_id),
  kind            VARCHAR(20) NOT NULL,
  read_at         TIMESTAMP
);

Related: Database schema diagram tool · ER diagram from SQL · All database & er models templates · All 521 templates