STAND WITH UKRAINE
dataLlama

Database Schema Design Prompt (LLaMA / Ollama)

Database schema mistakes are the most expensive to fix after launch because they require data migrations. This prompt surfaces design decisions (UUID vs. serial IDs, soft vs. hard deletes, indexing strategy) before they become permanent, and aligns the index strategy with actual access patterns rather than adding indexes speculatively. This variant is formatted for LLaMA / Ollama: Optimised for LLaMA 3, Mistral, and Ollama local models. Uses [INST] / <<SYS>> instruction format.

Prompt Template
[INST] <<SYS>>
You are a helpful, accurate, and detailed AI assistant. Follow the instructions carefully.
<</SYS>>

You are a senior database architect with expertise in relational database design.

Design a database schema for the following system:

System description: {{description}}
Entities to model: {{entities}}
Key relationships: {{relationships}}
Database: {{database}}
Scale expectations: {{scale}}
Access patterns: {{access_patterns}}

Provide:
1. **ERD description** — list all tables with their relationships (e.g., "users has many orders")
2. **DDL statements** — CREATE TABLE statements for all tables with appropriate types, constraints, and comments
3. **Indexes** — recommended indexes with justification based on the access patterns
4. **Normalisation** — confirm the schema is in 3NF or explain any intentional denormalisation
5. **Design decisions** — 3-5 key decisions made and the alternatives considered
6. **Migration strategy** — how to populate initial data if this is a new schema on an existing system [/INST]

Variables

{{description}}What the system does, e.g., "an e-commerce platform", "a project management tool"
{{entities}}Main data entities, e.g., "users, products, orders, line_items, reviews"
{{relationships}}How entities relate, e.g., "users place orders, orders contain line_items, products have many reviews"
{{database}}Target database: PostgreSQL, MySQL, SQLite, etc.
{{scale}}Expected scale, e.g., "10k users, 1M orders per year", "small team internal tool"
{{access_patterns}}How data is queried, e.g., "frequently look up orders by user, products by category and price"

Example

Input
description: A subscription-based SaaS with multiple pricing plans
entities: users, subscriptions, plans, invoices, usage_events
relationships: users have one subscription, subscriptions belong to a plan, subscriptions have many invoices
database: PostgreSQL
scale: 100k users, 500k invoices, 10M usage events per month
access_patterns: look up subscription by user ID, query invoices by date range, aggregate usage_events by user and month
Output
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email TEXT NOT NULL UNIQUE,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE plans (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  price_cents INT NOT NULL,
  billing_interval TEXT NOT NULL CHECK (billing_interval IN ('monthly', 'annual'))
);

-- Index for subscription lookup by user
CREATE INDEX idx_subscriptions_user_id ON subscriptions(user_id);

Related Tools

AI Prompt BuilderAI Token CounterAI Prompt Complexity Score

FAQ

When should I use UUID vs. auto-increment IDs?
Use UUIDs when IDs are exposed in URLs (prevents enumeration attacks) or when you need IDs before inserting (distributed systems, event sourcing). Use auto-increment when ID order matters for queries and you do not expose IDs publicly.
Should I use soft deletes (deleted_at column) or hard deletes?
Use soft deletes for user data (audit trail, accidental deletion recovery) and hard deletes for ephemeral data (logs, temp tables). Soft deletes require filtering deleted rows from every query — add a partial index WHERE deleted_at IS NULL to maintain performance.
How do I design a schema for multi-tenancy?
The simplest approach is a tenant_id column on every table with row-level security policies (PostgreSQL RLS). For stronger isolation, use separate schemas per tenant. Specify "multi-tenant" in the description and the AI will add the appropriate tenant isolation strategy.

Related Prompts

Database Schema Design Prompt

Database schema mistakes are the most expensive to fix after launch because they require d...

Database Schema Design Prompt (ChatGPT)

Database schema mistakes are the most expensive to fix after launch because they require d...

Database Schema Design Prompt (Claude)

Database schema mistakes are the most expensive to fix after launch because they require d...

Database Schema Design Prompt (Gemini)

Database schema mistakes are the most expensive to fix after launch because they require d...

SQL Query Generation Prompt

Generating SQL without schema context produces queries that reference non-existent columns...

Migration Guide Prompt

Migrations fail when the rollback plan is an afterthought. This prompt designs rollback ca...