SaaS Multi-Tenant Database Schema

Tables in this schema

Schema (SQL)

MySQL-dialect DDL. Open it in the editor for a visual ER diagram you can edit, or copy the SQL straight into your project.

CREATE TABLE organizations (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(150) NOT NULL,
  slug VARCHAR(160) NOT NULL UNIQUE,
  created_at DATETIME NOT NULL
);

CREATE TABLE users (
  id INT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(254) NOT NULL UNIQUE,
  full_name VARCHAR(120),
  created_at DATETIME NOT NULL
);

CREATE TABLE memberships (
  id INT PRIMARY KEY AUTO_INCREMENT,
  organization_id INT NOT NULL,
  user_id INT NOT NULL,
  role VARCHAR(20) NOT NULL,
  invited_at DATETIME,
  joined_at DATETIME,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (user_id) REFERENCES users(id)
);

CREATE TABLE plans (
  id INT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(60) NOT NULL,
  price_monthly DECIMAL(10,2) NOT NULL,
  seat_limit INT
);

CREATE TABLE subscriptions (
  id INT PRIMARY KEY AUTO_INCREMENT,
  organization_id INT NOT NULL,
  plan_id INT NOT NULL,
  status VARCHAR(20) NOT NULL,
  current_period_end DATETIME NOT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (plan_id) REFERENCES plans(id)
);

CREATE TABLE projects (
  id INT PRIMARY KEY AUTO_INCREMENT,
  organization_id INT NOT NULL,
  name VARCHAR(150) NOT NULL,
  created_by INT,
  created_at DATETIME NOT NULL,
  FOREIGN KEY (organization_id) REFERENCES organizations(id),
  FOREIGN KEY (created_by) REFERENCES users(id)
);