S SCHEMA.BIZ
Home Database Tools Templates

Database Schema Templates

Starter database schemas for common application types. Each template includes related tables with foreign keys and notes on its design. Copy the SQL, or adapt it in the visual designer.

Last updated:

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 (add a primary key on post_id and tag_id before production). 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 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 have a self-referencing parent_id for nested hierarchies; it is NOT NULL, so make it nullable before adding top-level categories. 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 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 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 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 hold discussion on each task.

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 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.

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 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 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. Modifiers (sizes, toppings) belong to modifier groups; the template doesn't link groups to menu items, so add a join table if you need that. 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 Designer

Real Estate Listings

Commerce

Properties, agencies, agents, listings, photos, and inquiries.

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 property.

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 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 Designer

Database Design Patterns & When to Use Each Template

A well-designed database schema is the backbone of any successful application. These templates follow common relational design patterns: normalized tables, 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 one way to separate patient identity from medical records and prescriptions.

Schema Design Principles

The templates follow these principles, with the exceptions noted:

  • Normalized design. Each table is designed around one entity. A few keep a redundant foreign key for convenience (the healthcare prescriptions and medical records both store patient_id, for example), so check them against your own normalization rules.
  • 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 mostly plural, and most foreign key columns are named entity_id (e.g., user_id, post_id); some use a role name instead, such as parent_id or owner_id.
  • Timestamps. Half of the tables (31 of 62) have a created_at column and three have updated_at. Add audit timestamps wherever you need them.
  • 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.

Load any template from the Schema Designer's Templates menu 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.

Frequently Asked Questions

Are these schemas ready to use in production?

They are starting points to review and adapt, not finished schemas. Every template enforces referential integrity with foreign keys and uses snake_case names. Only 31 of the 62 tables have a created_at column, no template adds indexes beyond those that primary keys and unique constraints create, and a few have known gaps: the e-commerce categories.parent_id is NOT NULL, so there can be no top-level category, and the blog post_tags table has no primary key. Add the columns, constraints, and indexes your application needs before production.

Which database dialects are supported?

This page shows each template as PostgreSQL. For MySQL, SQLite, or SQL Server, load the template from the Schema Designer's Templates menu and switch the dialect there. It rewrites auto-increment columns (SERIAL, AUTO_INCREMENT, IDENTITY), boolean types, and identifier quoting for each dialect; review SQLite and SQL Server output before running it.

How do I customize a template for my application?

Open the Schema Designer and pick the template from its Templates menu (the designer starts with the Blog template). From there you can add or remove tables, change column types, and edit constraints and foreign keys. Indexes aren't editable yet, and renaming a column doesn't update the foreign keys that point at it. Once you are happy with the result, export the SQL in your preferred dialect. To compare your customized version against the original, paste both into the Schema Diff tool to see the migration script you would need to apply.

Are the templates licensed for commercial use?

Yes. You can use, modify, redistribute, and ship the template SQL in commercial products. There is no attribution requirement, no per-seat license, and no ongoing fee.

Do the templates include indexes?

Only the indexes that primary keys and unique constraints create automatically. The templates don't include CREATE INDEX statements, not even on foreign-key columns, because the right indexes depend on your queries. Add indexes for your access patterns, such as foreign keys and the columns you filter on (status, created_at), before production. The Query Visualizer can show you which tables and columns a query touches.

Is anything sent to a server when I open or export a template?

No template data is sent anywhere: the template definitions are bundled into the page, the SQL is generated when the site is built, and Copy SQL copies it in your browser. Like every page, this one is measured by Google Analytics. We also record which template you view, copy or open the Designer from, and the Schema Designer records which template you load.