Production-ready CREATE TABLE designs for common applications. Copy, paste, and customize for PostgreSQL, MySQL, or SQLite.
Posts, categories, tags, comments, and authors. A complete content management schema with slug-based URLs and publish states.
-- Blog Schema (PostgreSQL)
CREATE TABLE authors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
slug VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
author_id INTEGER REFERENCES authors(id) ON DELETE SET NULL,
category_id INTEGER REFERENCES categories(id) ON DELETE SET NULL,
slug VARCHAR(100) UNIQUE NOT NULL,
title VARCHAR(200) NOT NULL,
excerpt TEXT,
body TEXT NOT NULL,
status VARCHAR(20) DEFAULT 'draft',
published_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE tags (
id SERIAL PRIMARY KEY,
slug VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(50) NOT NULL
);
CREATE TABLE post_tags (
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
tag_id INTEGER REFERENCES tags(id) ON DELETE CASCADE,
PRIMARY KEY (post_id, tag_id)
);
CREATE TABLE comments (
id SERIAL PRIMARY KEY,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
author_name VARCHAR(100) NOT NULL,
author_email VARCHAR(255) NOT NULL,
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Products, variants, categories, customers, orders, and order items. Inventory-aware with SKU tracking and order statuses.
-- E-commerce Schema (PostgreSQL)
CREATE TABLE customers (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
phone VARCHAR(20),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
parent_id INTEGER REFERENCES categories(id),
name VARCHAR(100) NOT NULL,
slug VARCHAR(50) UNIQUE NOT NULL
);
CREATE TABLE products (
id SERIAL PRIMARY KEY,
category_id INTEGER REFERENCES categories(id),
sku VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(200) NOT NULL,
description TEXT,
price DECIMAL(10,2) NOT NULL,
stock INTEGER DEFAULT 0,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE product_variants (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
variant_name VARCHAR(100) NOT NULL,
sku VARCHAR(50) UNIQUE NOT NULL,
price_adjustment DECIMAL(10,2) DEFAULT 0,
stock INTEGER DEFAULT 0
);
CREATE TABLE orders (
id SERIAL PRIMARY KEY,
customer_id INTEGER REFERENCES customers(id),
status VARCHAR(20) DEFAULT 'pending',
total DECIMAL(10,2) NOT NULL,
shipping_address TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE order_items (
id SERIAL PRIMARY KEY,
order_id INTEGER REFERENCES orders(id) ON DELETE CASCADE,
product_id INTEGER REFERENCES products(id),
variant_id INTEGER REFERENCES product_variants(id),
quantity INTEGER NOT NULL,
unit_price DECIMAL(10,2) NOT NULL
);
CREATE TABLE reviews (
id SERIAL PRIMARY KEY,
product_id INTEGER REFERENCES products(id) ON DELETE CASCADE,
customer_id INTEGER REFERENCES customers(id) ON DELETE SET NULL,
rating INTEGER NOT NULL CHECK (rating BETWEEN 1 AND 5),
comment TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Teams, users, roles, permissions, invitations, and audit logs. Row-level security ready with tenant isolation.
-- SaaS Multi-Tenant Schema (PostgreSQL)
CREATE TABLE teams (
id SERIAL PRIMARY KEY,
slug VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
plan VARCHAR(20) DEFAULT 'free',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE users (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
password_hash VARCHAR(255) NOT NULL,
email_verified_at TIMESTAMP,
last_login_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE team_members (
id SERIAL PRIMARY KEY,
team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
role VARCHAR(20) DEFAULT 'member',
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (team_id, user_id)
);
CREATE TABLE invitations (
id SERIAL PRIMARY KEY,
team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
email VARCHAR(255) NOT NULL,
role VARCHAR(20) DEFAULT 'member',
token VARCHAR(64) UNIQUE NOT NULL,
expires_at TIMESTAMP NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE permissions (
id SERIAL PRIMARY KEY,
code VARCHAR(50) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL
);
CREATE TABLE role_permissions (
role VARCHAR(20) NOT NULL,
permission_id INTEGER REFERENCES permissions(id) ON DELETE CASCADE,
PRIMARY KEY (role, permission_id)
);
CREATE TABLE audit_logs (
id SERIAL PRIMARY KEY,
team_id INTEGER REFERENCES teams(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
action VARCHAR(50) NOT NULL,
entity_type VARCHAR(50),
entity_id INTEGER,
meta JSONB,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Conversations, participants, messages, read receipts, and attachments. Supports group chats and DMs.
-- Chat Schema (PostgreSQL)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
display_name VARCHAR(100),
avatar_url TEXT,
status VARCHAR(20) DEFAULT 'offline',
last_seen_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE conversations (
id SERIAL PRIMARY KEY,
title VARCHAR(100),
is_group BOOLEAN DEFAULT FALSE,
created_by INTEGER REFERENCES users(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE conversation_members (
conversation_id INTEGER REFERENCES conversations(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
joined_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (conversation_id, user_id)
);
CREATE TABLE messages (
id SERIAL PRIMARY KEY,
conversation_id INTEGER REFERENCES conversations(id) ON DELETE CASCADE,
sender_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
content TEXT NOT NULL,
message_type VARCHAR(20) DEFAULT 'text',
edited_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE message_reads (
message_id INTEGER REFERENCES messages(id) ON DELETE CASCADE,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
read_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (message_id, user_id)
);
CREATE TABLE attachments (
id SERIAL PRIMARY KEY,
message_id INTEGER REFERENCES messages(id) ON DELETE CASCADE,
file_name VARCHAR(255) NOT NULL,
file_url TEXT NOT NULL,
file_size INTEGER,
mime_type VARCHAR(100),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Short links, click analytics, custom aliases, and expiration dates. Minimal and fast.
-- URL Shortener Schema (PostgreSQL)
CREATE TABLE links (
id SERIAL PRIMARY KEY,
short_code VARCHAR(20) UNIQUE NOT NULL,
original_url TEXT NOT NULL,
custom_alias VARCHAR(50) UNIQUE,
click_count INTEGER DEFAULT 0,
expires_at TIMESTAMP,
created_by INTEGER,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE link_clicks (
id SERIAL PRIMARY KEY,
link_id INTEGER REFERENCES links(id) ON DELETE CASCADE,
ip_address INET,
user_agent TEXT,
referrer TEXT,
country VARCHAR(2),
clicked_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Projects, tasks, labels, assignees, comments, and time tracking. Kanban-ready with status columns.
-- Task Manager Schema (PostgreSQL)
CREATE TABLE projects (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
description TEXT,
status VARCHAR(20) DEFAULT 'active',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE labels (
id SERIAL PRIMARY KEY,
project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
name VARCHAR(50) NOT NULL,
color VARCHAR(7) DEFAULT '#6366f1'
);
CREATE TABLE tasks (
id SERIAL PRIMARY KEY,
project_id INTEGER REFERENCES projects(id) ON DELETE CASCADE,
title VARCHAR(200) NOT NULL,
description TEXT,
status VARCHAR(20) DEFAULT 'todo',
priority VARCHAR(10) DEFAULT 'medium',
assignee_id INTEGER,
due_date DATE,
position INTEGER DEFAULT 0,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE task_labels (
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
label_id INTEGER REFERENCES labels(id) ON DELETE CASCADE,
PRIMARY KEY (task_id, label_id)
);
CREATE TABLE time_entries (
id SERIAL PRIMARY KEY,
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
user_id INTEGER,
description TEXT,
started_at TIMESTAMP NOT NULL,
ended_at TIMESTAMP,
duration_minutes INTEGER
);
CREATE TABLE task_comments (
id SERIAL PRIMARY KEY,
task_id INTEGER REFERENCES tasks(id) ON DELETE CASCADE,
user_id INTEGER,
body TEXT NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Users, profiles, follows, posts, likes, and notifications. A lightweight Twitter-style schema.
-- Social Network Schema (PostgreSQL)
CREATE TABLE users (
id SERIAL PRIMARY KEY,
username VARCHAR(50) UNIQUE NOT NULL,
display_name VARCHAR(100),
bio TEXT,
avatar_url TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE follows (
follower_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
following_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (follower_id, following_id)
);
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
content TEXT NOT NULL,
reply_to_id INTEGER REFERENCES posts(id),
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE likes (
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, post_id)
);
CREATE TABLE bookmarks (
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (user_id, post_id)
);
CREATE TABLE notifications (
id SERIAL PRIMARY KEY,
user_id INTEGER REFERENCES users(id) ON DELETE CASCADE,
actor_id INTEGER REFERENCES users(id) ON DELETE SET NULL,
type VARCHAR(20) NOT NULL,
post_id INTEGER REFERENCES posts(id) ON DELETE CASCADE,
is_read BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Courses, lessons, enrollments, progress tracking, quizzes, and certificates.
-- LMS Schema (PostgreSQL)
CREATE TABLE instructors (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
email VARCHAR(255) UNIQUE NOT NULL,
bio TEXT,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE courses (
id SERIAL PRIMARY KEY,
instructor_id INTEGER REFERENCES instructors(id),
slug VARCHAR(50) UNIQUE NOT NULL,
title VARCHAR(200) NOT NULL,
description TEXT,
level VARCHAR(20) DEFAULT 'beginner',
status VARCHAR(20) DEFAULT 'draft',
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE lessons (
id SERIAL PRIMARY KEY,
course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
title VARCHAR(200) NOT NULL,
content TEXT,
video_url TEXT,
duration_minutes INTEGER,
position INTEGER DEFAULT 0,
is_free BOOLEAN DEFAULT FALSE,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE students (
id SERIAL PRIMARY KEY,
email VARCHAR(255) UNIQUE NOT NULL,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
CREATE TABLE enrollments (
id SERIAL PRIMARY KEY,
student_id INTEGER REFERENCES students(id) ON DELETE CASCADE,
course_id INTEGER REFERENCES courses(id) ON DELETE CASCADE,
status VARCHAR(20) DEFAULT 'active',
completed_at TIMESTAMP,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
UNIQUE (student_id, course_id)
);
CREATE TABLE progress (
id SERIAL PRIMARY KEY,
enrollment_id INTEGER REFERENCES enrollments(id) ON DELETE CASCADE,
lesson_id INTEGER REFERENCES lessons(id) ON DELETE CASCADE,
is_completed BOOLEAN DEFAULT FALSE,
completed_at TIMESTAMP,
UNIQUE (enrollment_id, lesson_id)
);
CREATE TABLE certificates (
id SERIAL PRIMARY KEY,
enrollment_id INTEGER REFERENCES enrollments(id) ON DELETE CASCADE,
certificate_number VARCHAR(50) UNIQUE NOT NULL,
issued_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);Paste your old and new CREATE TABLE dumps into SchemaLens and get an instant visual diff + migration script.
Open Schema Diff