Crowsfoot

Social network database schema: ER diagram and SQL

Social features boil down to a few relationships: people write posts, follow each other, and like what they read. The follows table is the interesting one, because both of its foreign keys point back at users.

Open this schema in the editor Jump to the SQL
users PK id bigint handle varchar display_name varchar created_at timestamp posts PK id bigint FK user_id bigint body text created_at timestamp follows PK follower_id bigint PK followee_id bigint followed_at timestamp likes PK user_id bigint PK post_id bigint liked_at timestamp

Tables in this schema

users

Accounts.

posts

Short messages written by one user.

follows

Who follows whom. Both columns point at users, so this is a self-referencing many-to-many relationship.

likes

Which user liked which post.

How the tables relate

  • posts.user_id references users.id: one row in users can have many rows in posts.
  • follows is a junction table: each row links one users row to another, so users can relate to many users in both directions.
  • likes is a junction table between users and posts: many-to-many.

SQL (PostgreSQL)

Copy it into your database, or open it in Crowsfoot to edit the diagram, add notes, and export again.

CREATE TABLE users (
  id bigint PRIMARY KEY,
  handle varchar(30) NOT NULL,
  display_name varchar(80) NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE posts (
  id bigint PRIMARY KEY,
  user_id bigint NOT NULL REFERENCES users (id),
  body text NOT NULL,
  created_at timestamptz NOT NULL
);

CREATE TABLE follows (
  follower_id bigint NOT NULL REFERENCES users (id),
  followee_id bigint NOT NULL REFERENCES users (id),
  followed_at timestamptz NOT NULL,
  PRIMARY KEY (follower_id, followee_id)
);

CREATE TABLE likes (
  user_id bigint NOT NULL REFERENCES users (id),
  post_id bigint NOT NULL REFERENCES posts (id),
  liked_at timestamptz NOT NULL,
  PRIMARY KEY (user_id, post_id)
);

Design notes

  • follows has two foreign keys to the same table. Naming them follower_id and followee_id keeps queries readable.
  • Add a check constraint (follower_id <> followee_id) so nobody can follow themselves.
  • Counts such as likes per post are often cached in a column on posts once the table grows large.

More example schemas

New to ER diagrams? Read the crow’s foot notation guide.