A database schema for a Trello-clone project management app or managing and tracking tasks, timelines and deliverables using either MySQL, SQLite or PostgreSQL as primary database

project_management_app_design_database.sql

-- MySQL v8.0.16
-- PostgresSQL v16.9.2

CREATE DATABASE IF NOT EXISTS `test`
DEFAULT CHARACTER SET utf8 -- utf8mb4
DEFAULT COLLATE utf8_general_ci; -- utf8mb4_unicode_ci

SET default_storage_engine = INNODB;

CREATE TABLE organizations (
	id bigint NOT NULL,
	name varchar(150) NOT NULL,
	industry enum('education', 'financial services', 'legal', 'manufacturing', 'health', 'food processing', 'agro-materials', 'fashion design + tailoring') NOT NULL, 
	type enum('sole-proprietor_SME', 'corporation_STARTUP', 'sole-proprietor_SB', 'corporation_BIG_FIRM'),
	PRIMARY KEY (id)
);

CREATE TABLE users (
  id bigint NOT NULL,
  email varchar(90) NOT NULL,
  tenant_id bigint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (tenant_id) REFERENCES organizations (id)
);

CREATE TABLE projects (
  id bigint NOT NULL,
  name varchar(160) NOT NULL
  description text,
  tenant_id bigint NOT NULL,
  PRIMARY KEY (id),
  FOREIGN KEY (tenant_id) REFERENCES organizations (id)
);