S SCHEMA.BIZ
Home Database Tools Templates

Database Schema Templates

Production-ready database schemas for common application types. Each template includes properly normalized tables, foreign keys, indexes, and design documentation. Copy the SQL or open in the visual designer.

Blog / CMS

Content

Users, posts, comments, categories, and tags with many-to-many relationships.

6 tables 26 columns 6 relationships
users categories posts comments tags post_tags

Design notes: Classic blog schema with a post_tags junction table for many-to-many tagging. Categories use a separate table for clean normalization. Posts have a slug for SEO-friendly URLs and a published flag for draft support.

View SQL (PostgreSQL) 
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "bio" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "categories" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "slug" VARCHAR(100) NOT NULL UNIQUE
);

CREATE TABLE "posts" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(255) NOT NULL,
  "slug" VARCHAR(255) NOT NULL UNIQUE,
  "body" TEXT,
  "published" BOOLEAN DEFAULT FALSE,
  "user_id" INTEGER NOT NULL,
  "category_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  "updated_at" TIMESTAMP,
  FOREIGN KEY ("user_id") REFERENCES "users"("id"),
  FOREIGN KEY ("category_id") REFERENCES "categories"("id")
);

CREATE TABLE "comments" (
  "id" SERIAL PRIMARY KEY,
  "body" TEXT NOT NULL,
  "post_id" INTEGER NOT NULL,
  "user_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("post_id") REFERENCES "posts"("id"),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE "tags" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE "post_tags" (
  "post_id" INTEGER NOT NULL,
  "tag_id" INTEGER NOT NULL,
  FOREIGN KEY ("post_id") REFERENCES "posts"("id"),
  FOREIGN KEY ("tag_id") REFERENCES "tags"("id")
);
Open in Designer

E-commerce Store

Commerce

Users, products, categories, orders, line items, and reviews.

6 tables 34 columns 7 relationships
users categories products orders order_items reviews

Design notes: Categories support self-referencing parent_id for nested hierarchies. Order items store unit_price at time of purchase so price changes don't affect past orders. Reviews are tied to both the product and the user.

View SQL (PostgreSQL) 
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "password_hash" VARCHAR(255) NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "categories" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "slug" VARCHAR(100) UNIQUE,
  "parent_id" INTEGER NOT NULL,
  FOREIGN KEY ("parent_id") REFERENCES "categories"("id")
);

CREATE TABLE "products" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(255) NOT NULL,
  "description" TEXT,
  "price" DECIMAL(10,2) NOT NULL,
  "sku" VARCHAR(50) UNIQUE,
  "stock" INTEGER DEFAULT 0,
  "category_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("category_id") REFERENCES "categories"("id")
);

CREATE TABLE "orders" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "status" VARCHAR(20) DEFAULT 'pending',
  "total" DECIMAL(10,2) NOT NULL,
  "shipping_address" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE "order_items" (
  "id" SERIAL PRIMARY KEY,
  "order_id" INTEGER NOT NULL,
  "product_id" INTEGER NOT NULL,
  "quantity" INTEGER NOT NULL,
  "unit_price" DECIMAL(10,2) NOT NULL,
  FOREIGN KEY ("order_id") REFERENCES "orders"("id"),
  FOREIGN KEY ("product_id") REFERENCES "products"("id")
);

CREATE TABLE "reviews" (
  "id" SERIAL PRIMARY KEY,
  "product_id" INTEGER NOT NULL,
  "user_id" INTEGER NOT NULL,
  "rating" INTEGER NOT NULL,
  "comment" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("product_id") REFERENCES "products"("id"),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);
Open in Designer

SaaS Application

Software

Multi-tenant SaaS with organizations, users, subscriptions, and invoices.

5 tables 30 columns 5 relationships
organizations users plans subscriptions invoices

Design notes: Multi-tenant architecture where users belong to organizations. Plans define feature tiers; subscriptions link organizations to plans with billing cycle tracking. Invoices provide a financial audit trail.

View SQL (PostgreSQL) 
CREATE TABLE "organizations" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "slug" VARCHAR(100) NOT NULL UNIQUE,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "role" VARCHAR(20) DEFAULT 'member',
  "organization_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("organization_id") REFERENCES "organizations"("id")
);

CREATE TABLE "plans" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(50) NOT NULL,
  "price_monthly" DECIMAL(10,2) NOT NULL,
  "price_yearly" DECIMAL(10,2),
  "features" JSON,
  "max_users" INTEGER
);

CREATE TABLE "subscriptions" (
  "id" SERIAL PRIMARY KEY,
  "organization_id" INTEGER NOT NULL,
  "plan_id" INTEGER NOT NULL,
  "status" VARCHAR(20) DEFAULT 'active',
  "billing_cycle" VARCHAR(10) DEFAULT 'monthly',
  "started_at" TIMESTAMP DEFAULT NOW(),
  "expires_at" TIMESTAMP,
  FOREIGN KEY ("organization_id") REFERENCES "organizations"("id"),
  FOREIGN KEY ("plan_id") REFERENCES "plans"("id")
);

CREATE TABLE "invoices" (
  "id" SERIAL PRIMARY KEY,
  "organization_id" INTEGER NOT NULL,
  "subscription_id" INTEGER NOT NULL,
  "amount" DECIMAL(10,2) NOT NULL,
  "status" VARCHAR(20) DEFAULT 'pending',
  "issued_at" TIMESTAMP DEFAULT NOW(),
  "paid_at" TIMESTAMP,
  FOREIGN KEY ("organization_id") REFERENCES "organizations"("id"),
  FOREIGN KEY ("subscription_id") REFERENCES "subscriptions"("id")
);
Open in Designer

Social Network

Social

Users, posts, likes, follows, and direct messages.

5 tables 26 columns 7 relationships
users posts likes follows messages

Design notes: Follows table uses two foreign keys to the same users table for follower/following relationships. Likes are a simple junction between users and posts. Messages support read tracking for unread counts.

View SQL (PostgreSQL) 
CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "username" VARCHAR(50) NOT NULL UNIQUE,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "display_name" VARCHAR(100),
  "avatar_url" VARCHAR(500),
  "bio" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "posts" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "content" TEXT NOT NULL,
  "media_url" VARCHAR(500),
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE "likes" (
  "id" SERIAL PRIMARY KEY,
  "user_id" INTEGER NOT NULL,
  "post_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("user_id") REFERENCES "users"("id"),
  FOREIGN KEY ("post_id") REFERENCES "posts"("id")
);

CREATE TABLE "follows" (
  "id" SERIAL PRIMARY KEY,
  "follower_id" INTEGER NOT NULL,
  "following_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("follower_id") REFERENCES "users"("id"),
  FOREIGN KEY ("following_id") REFERENCES "users"("id")
);

CREATE TABLE "messages" (
  "id" SERIAL PRIMARY KEY,
  "sender_id" INTEGER NOT NULL,
  "receiver_id" INTEGER NOT NULL,
  "body" TEXT NOT NULL,
  "read" BOOLEAN DEFAULT FALSE,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("sender_id") REFERENCES "users"("id"),
  FOREIGN KEY ("receiver_id") REFERENCES "users"("id")
);
Open in Designer

Project Management

Software

Workspaces, projects, tasks, sprints, and assignments for team collaboration.

7 tables 42 columns 11 relationships
workspaces users projects sprints tasks task_assignments task_comments

Design notes: Tasks support status tracking, priority levels, and sprint assignment. Task assignments allow multiple people per task. Comments provide threaded discussion on tasks.

View SQL (PostgreSQL) 
CREATE TABLE "workspaces" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "slug" VARCHAR(100) NOT NULL UNIQUE,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "users" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "avatar_url" VARCHAR(500),
  "workspace_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id")
);

CREATE TABLE "projects" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "description" TEXT,
  "status" VARCHAR(20) DEFAULT 'active',
  "workspace_id" INTEGER NOT NULL,
  "owner_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("workspace_id") REFERENCES "workspaces"("id"),
  FOREIGN KEY ("owner_id") REFERENCES "users"("id")
);

CREATE TABLE "sprints" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "project_id" INTEGER NOT NULL,
  "start_date" DATE,
  "end_date" DATE,
  "status" VARCHAR(20) DEFAULT 'planned',
  FOREIGN KEY ("project_id") REFERENCES "projects"("id")
);

CREATE TABLE "tasks" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(255) NOT NULL,
  "description" TEXT,
  "status" VARCHAR(20) DEFAULT 'todo',
  "priority" VARCHAR(10) DEFAULT 'medium',
  "due_date" DATE,
  "project_id" INTEGER NOT NULL,
  "sprint_id" INTEGER NOT NULL,
  "reporter_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  "updated_at" TIMESTAMP,
  FOREIGN KEY ("project_id") REFERENCES "projects"("id"),
  FOREIGN KEY ("sprint_id") REFERENCES "sprints"("id"),
  FOREIGN KEY ("reporter_id") REFERENCES "users"("id")
);

CREATE TABLE "task_assignments" (
  "id" SERIAL PRIMARY KEY,
  "task_id" INTEGER NOT NULL,
  "user_id" INTEGER NOT NULL,
  FOREIGN KEY ("task_id") REFERENCES "tasks"("id"),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);

CREATE TABLE "task_comments" (
  "id" SERIAL PRIMARY KEY,
  "body" TEXT NOT NULL,
  "task_id" INTEGER NOT NULL,
  "user_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("task_id") REFERENCES "tasks"("id"),
  FOREIGN KEY ("user_id") REFERENCES "users"("id")
);
Open in Designer

Learning Management

Education

Courses, modules, lessons, enrollments, and progress tracking for online learning.

7 tables 38 columns 7 relationships
instructors students courses modules lessons enrollments lesson_progress

Design notes: Courses contain modules, which contain lessons in a specific order. Enrollments track student registration; progress tracks completion at the lesson level. Quizzes attach to lessons with scored attempts.

View SQL (PostgreSQL) 
CREATE TABLE "instructors" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "bio" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "students" (
  "id" SERIAL PRIMARY KEY,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "name" VARCHAR(100) NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "courses" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(255) NOT NULL,
  "description" TEXT,
  "price" DECIMAL(10,2) DEFAULT 0,
  "published" BOOLEAN DEFAULT FALSE,
  "instructor_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("instructor_id") REFERENCES "instructors"("id")
);

CREATE TABLE "modules" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(255) NOT NULL,
  "sort_order" INTEGER DEFAULT 0,
  "course_id" INTEGER NOT NULL,
  FOREIGN KEY ("course_id") REFERENCES "courses"("id")
);

CREATE TABLE "lessons" (
  "id" SERIAL PRIMARY KEY,
  "title" VARCHAR(255) NOT NULL,
  "content" TEXT,
  "video_url" VARCHAR(500),
  "duration_minutes" INTEGER,
  "sort_order" INTEGER DEFAULT 0,
  "module_id" INTEGER NOT NULL,
  FOREIGN KEY ("module_id") REFERENCES "modules"("id")
);

CREATE TABLE "enrollments" (
  "id" SERIAL PRIMARY KEY,
  "student_id" INTEGER NOT NULL,
  "course_id" INTEGER NOT NULL,
  "status" VARCHAR(20) DEFAULT 'active',
  "enrolled_at" TIMESTAMP DEFAULT NOW(),
  "completed_at" TIMESTAMP,
  FOREIGN KEY ("student_id") REFERENCES "students"("id"),
  FOREIGN KEY ("course_id") REFERENCES "courses"("id")
);

CREATE TABLE "lesson_progress" (
  "id" SERIAL PRIMARY KEY,
  "enrollment_id" INTEGER NOT NULL,
  "lesson_id" INTEGER NOT NULL,
  "completed" BOOLEAN DEFAULT FALSE,
  "completed_at" TIMESTAMP,
  FOREIGN KEY ("enrollment_id") REFERENCES "enrollments"("id"),
  FOREIGN KEY ("lesson_id") REFERENCES "lessons"("id")
);
Open in Designer

Inventory Management

Commerce

Products, warehouses, stock levels, suppliers, and purchase orders.

6 tables 35 columns 6 relationships
suppliers warehouses products inventory purchase_orders purchase_order_items

Design notes: Stock levels are tracked per warehouse per product via the inventory table. Purchase orders flow from suppliers and update inventory when received. Low stock alerts can be derived from the reorder_level field.

View SQL (PostgreSQL) 
CREATE TABLE "suppliers" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "contact_name" VARCHAR(100),
  "email" VARCHAR(255),
  "phone" VARCHAR(20),
  "address" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "warehouses" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "location" VARCHAR(255),
  "capacity" INTEGER
);

CREATE TABLE "products" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(255) NOT NULL,
  "sku" VARCHAR(50) NOT NULL UNIQUE,
  "description" TEXT,
  "unit_price" DECIMAL(10,2) NOT NULL,
  "reorder_level" INTEGER DEFAULT 10,
  "supplier_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("supplier_id") REFERENCES "suppliers"("id")
);

CREATE TABLE "inventory" (
  "id" SERIAL PRIMARY KEY,
  "product_id" INTEGER NOT NULL,
  "warehouse_id" INTEGER NOT NULL,
  "quantity" INTEGER NOT NULL DEFAULT 0,
  "updated_at" TIMESTAMP,
  FOREIGN KEY ("product_id") REFERENCES "products"("id"),
  FOREIGN KEY ("warehouse_id") REFERENCES "warehouses"("id")
);

CREATE TABLE "purchase_orders" (
  "id" SERIAL PRIMARY KEY,
  "supplier_id" INTEGER NOT NULL,
  "status" VARCHAR(20) DEFAULT 'draft',
  "total" DECIMAL(10,2),
  "ordered_at" TIMESTAMP,
  "received_at" TIMESTAMP,
  FOREIGN KEY ("supplier_id") REFERENCES "suppliers"("id")
);

CREATE TABLE "purchase_order_items" (
  "id" SERIAL PRIMARY KEY,
  "purchase_order_id" INTEGER NOT NULL,
  "product_id" INTEGER NOT NULL,
  "quantity" INTEGER NOT NULL,
  "unit_cost" DECIMAL(10,2) NOT NULL,
  FOREIGN KEY ("purchase_order_id") REFERENCES "purchase_orders"("id"),
  FOREIGN KEY ("product_id") REFERENCES "products"("id")
);
Open in Designer

Restaurant Ordering

Commerce

Menu categories, items, modifiers, orders, and table management for restaurants.

8 tables 43 columns 6 relationships
menu_categories menu_items modifier_groups modifiers tables staff orders order_items

Design notes: Menu items belong to categories and can have modifier groups (sizes, toppings). Orders track table assignment, server, and status. Order items link to menu items with selected modifiers stored as JSON.

View SQL (PostgreSQL) 
CREATE TABLE "menu_categories" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "sort_order" INTEGER DEFAULT 0,
  "active" BOOLEAN DEFAULT TRUE
);

CREATE TABLE "menu_items" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(255) NOT NULL,
  "description" TEXT,
  "price" DECIMAL(10,2) NOT NULL,
  "image_url" VARCHAR(500),
  "available" BOOLEAN DEFAULT TRUE,
  "category_id" INTEGER NOT NULL,
  FOREIGN KEY ("category_id") REFERENCES "menu_categories"("id")
);

CREATE TABLE "modifier_groups" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "required" BOOLEAN DEFAULT FALSE,
  "max_selections" INTEGER DEFAULT 1
);

CREATE TABLE "modifiers" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "price_adjustment" DECIMAL(10,2) DEFAULT 0,
  "group_id" INTEGER NOT NULL,
  FOREIGN KEY ("group_id") REFERENCES "modifier_groups"("id")
);

CREATE TABLE "tables" (
  "id" SERIAL PRIMARY KEY,
  "number" INTEGER NOT NULL UNIQUE,
  "seats" INTEGER DEFAULT 4,
  "status" VARCHAR(20) DEFAULT 'available'
);

CREATE TABLE "staff" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "role" VARCHAR(20) NOT NULL,
  "pin" VARCHAR(10)
);

CREATE TABLE "orders" (
  "id" SERIAL PRIMARY KEY,
  "table_id" INTEGER NOT NULL,
  "server_id" INTEGER NOT NULL,
  "status" VARCHAR(20) DEFAULT 'open',
  "subtotal" DECIMAL(10,2),
  "tax" DECIMAL(10,2),
  "total" DECIMAL(10,2),
  "created_at" TIMESTAMP DEFAULT NOW(),
  "closed_at" TIMESTAMP,
  FOREIGN KEY ("table_id") REFERENCES "tables"("id"),
  FOREIGN KEY ("server_id") REFERENCES "staff"("id")
);

CREATE TABLE "order_items" (
  "id" SERIAL PRIMARY KEY,
  "order_id" INTEGER NOT NULL,
  "menu_item_id" INTEGER NOT NULL,
  "quantity" INTEGER NOT NULL DEFAULT 1,
  "unit_price" DECIMAL(10,2) NOT NULL,
  "modifiers" JSON,
  "notes" TEXT,
  FOREIGN KEY ("order_id") REFERENCES "orders"("id"),
  FOREIGN KEY ("menu_item_id") REFERENCES "menu_items"("id")
);
Open in Designer

Real Estate Listings

Commerce

Properties, agents, listings, inquiries, and open house scheduling.

6 tables 45 columns 5 relationships
agencies agents properties listings property_images inquiries

Design notes: Properties store physical details separately from listing information (price, status). This allows relisting without losing property data. Property images are stored in a separate table for multiple photos per listing.

View SQL (PostgreSQL) 
CREATE TABLE "agencies" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "phone" VARCHAR(20),
  "address" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "agents" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "email" VARCHAR(255) NOT NULL UNIQUE,
  "phone" VARCHAR(20),
  "license_number" VARCHAR(50),
  "agency_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("agency_id") REFERENCES "agencies"("id")
);

CREATE TABLE "properties" (
  "id" SERIAL PRIMARY KEY,
  "address" VARCHAR(255) NOT NULL,
  "city" VARCHAR(100) NOT NULL,
  "state" VARCHAR(50),
  "zip" VARCHAR(10),
  "property_type" VARCHAR(30) NOT NULL,
  "bedrooms" INTEGER,
  "bathrooms" DECIMAL(3,1),
  "sqft" INTEGER,
  "lot_size" DECIMAL(10,2),
  "year_built" INTEGER,
  "description" TEXT
);

CREATE TABLE "listings" (
  "id" SERIAL PRIMARY KEY,
  "property_id" INTEGER NOT NULL,
  "agent_id" INTEGER NOT NULL,
  "price" DECIMAL(12,2) NOT NULL,
  "status" VARCHAR(20) DEFAULT 'active',
  "listing_type" VARCHAR(10) DEFAULT 'sale',
  "listed_at" TIMESTAMP DEFAULT NOW(),
  "sold_at" TIMESTAMP,
  FOREIGN KEY ("property_id") REFERENCES "properties"("id"),
  FOREIGN KEY ("agent_id") REFERENCES "agents"("id")
);

CREATE TABLE "property_images" (
  "id" SERIAL PRIMARY KEY,
  "property_id" INTEGER NOT NULL,
  "url" VARCHAR(500) NOT NULL,
  "caption" VARCHAR(255),
  "sort_order" INTEGER DEFAULT 0,
  FOREIGN KEY ("property_id") REFERENCES "properties"("id")
);

CREATE TABLE "inquiries" (
  "id" SERIAL PRIMARY KEY,
  "listing_id" INTEGER NOT NULL,
  "name" VARCHAR(100) NOT NULL,
  "email" VARCHAR(255) NOT NULL,
  "phone" VARCHAR(20),
  "message" TEXT,
  "status" VARCHAR(20) DEFAULT 'new',
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("listing_id") REFERENCES "listings"("id")
);
Open in Designer

Healthcare / Patient Records

Healthcare

Patients, doctors, appointments, medical records, and prescriptions.

6 tables 46 columns 8 relationships
departments doctors patients appointments medical_records prescriptions

Design notes: Patients and doctors are separate entities since a doctor is also staff but has different attributes. Appointments link patients to doctors at specific times. Medical records create a permanent history; prescriptions link to specific visits.

View SQL (PostgreSQL) 
CREATE TABLE "departments" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "floor" VARCHAR(10)
);

CREATE TABLE "doctors" (
  "id" SERIAL PRIMARY KEY,
  "name" VARCHAR(100) NOT NULL,
  "email" VARCHAR(255) UNIQUE,
  "specialty" VARCHAR(100) NOT NULL,
  "license_number" VARCHAR(50) UNIQUE,
  "department_id" INTEGER NOT NULL,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("department_id") REFERENCES "departments"("id")
);

CREATE TABLE "patients" (
  "id" SERIAL PRIMARY KEY,
  "first_name" VARCHAR(50) NOT NULL,
  "last_name" VARCHAR(50) NOT NULL,
  "date_of_birth" DATE NOT NULL,
  "gender" VARCHAR(10),
  "phone" VARCHAR(20),
  "email" VARCHAR(255),
  "address" TEXT,
  "insurance_provider" VARCHAR(100),
  "insurance_number" VARCHAR(50),
  "created_at" TIMESTAMP DEFAULT NOW()
);

CREATE TABLE "appointments" (
  "id" SERIAL PRIMARY KEY,
  "patient_id" INTEGER NOT NULL,
  "doctor_id" INTEGER NOT NULL,
  "scheduled_at" TIMESTAMP NOT NULL,
  "duration_minutes" INTEGER DEFAULT 30,
  "status" VARCHAR(20) DEFAULT 'scheduled',
  "reason" TEXT,
  "created_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("patient_id") REFERENCES "patients"("id"),
  FOREIGN KEY ("doctor_id") REFERENCES "doctors"("id")
);

CREATE TABLE "medical_records" (
  "id" SERIAL PRIMARY KEY,
  "patient_id" INTEGER NOT NULL,
  "doctor_id" INTEGER NOT NULL,
  "appointment_id" INTEGER NOT NULL,
  "diagnosis" TEXT,
  "notes" TEXT,
  "vitals" JSON,
  "recorded_at" TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY ("patient_id") REFERENCES "patients"("id"),
  FOREIGN KEY ("doctor_id") REFERENCES "doctors"("id"),
  FOREIGN KEY ("appointment_id") REFERENCES "appointments"("id")
);

CREATE TABLE "prescriptions" (
  "id" SERIAL PRIMARY KEY,
  "medical_record_id" INTEGER NOT NULL,
  "patient_id" INTEGER NOT NULL,
  "medication" VARCHAR(255) NOT NULL,
  "dosage" VARCHAR(100) NOT NULL,
  "frequency" VARCHAR(100),
  "start_date" DATE NOT NULL,
  "end_date" DATE,
  "notes" TEXT,
  FOREIGN KEY ("medical_record_id") REFERENCES "medical_records"("id"),
  FOREIGN KEY ("patient_id") REFERENCES "patients"("id")
);
Open in Designer

Database Design Patterns & When to Use Each Template

A well-designed database schema is the backbone of any successful application. These templates represent proven patterns that have been refined across thousands of production deployments. Each follows core relational design principles: proper normalization, referential integrity through foreign keys, and thoughtful data type selection.

Choosing the Right Template

Start by identifying which template most closely matches your application's domain. You'll almost certainly need to customize it, but starting from a proven foundation saves hours of design work and helps you avoid common pitfalls.

  • Blog / CMS — Best for content-heavy sites. The many-to-many post_tags pattern is worth studying even if you're building something else — it's the standard approach for tagging in relational databases.
  • E-commerce — The order_items pattern (storing unit_price at purchase time rather than joining to the current product price) is a critical design decision that preserves historical pricing accuracy.
  • SaaS — The multi-tenant organization model is the most common pattern for B2B SaaS. Users belong to organizations, and all data is scoped to the organization level.
  • Project Management — Demonstrates a task tracking system with sprints, assignments, and comments. The pattern of a reporter (who created the task) separate from assignees is a common requirement.
  • Healthcare — Shows how to handle domain-specific regulatory requirements like separating patient identity from medical records, and maintaining a full audit trail through timestamped records.

Schema Design Principles

Every template in this library follows these core principles:

  • Third Normal Form (3NF). Each table stores facts about exactly one entity. No data is duplicated across tables unless denormalization is justified by read performance requirements.
  • Referential integrity. All relationships are enforced with foreign keys, preventing orphaned records and data inconsistencies at the database level.
  • Consistent naming. Snake_case for all identifiers. Table names are plural. Foreign key columns are named entity_id (e.g., user_id, post_id).
  • Timestamps. Every table includes created_at for audit purposes. Tables with mutable data include updated_at.
  • Meaningful defaults. Status columns default to initial states (e.g., 'pending', 'draft', 'active'). Boolean flags default to their most common value.

Customizing Templates

These templates are starting points, not finished products. Common customizations include adding application-specific columns, adjusting data types for your expected data volume, adding indexes on columns you'll frequently query by, and extending the schema with additional tables for features unique to your application.

Open any template in the Schema Designer to modify it visually, then export the customized SQL for your preferred database dialect. Use the Schema Diff tool to compare your customized version against the original template.