How do I use the "Design Database Schema" prompt?

Copy the prompt template, fill in the variables (APPLICATION_TYPE, EXPECTED_SCALE, PRIMARY_USE_CASES, KEY_ENTITIES), and paste it into ChatGPT, Claude, or Gemini.

What AI tools work with this coding prompt?

This prompt works with chatgpt, claude, gemini. Simply copy and paste the template into any of these AI assistants.

Tips for getting the best results?

Always specify the expected data volume and query patterns - this helps the AI recommend appropriate data types and indexing strategies Include your performance requirements (response times, concurrent users) to get better optimization suggestions Ask for both normalized and potential denormalized versions if you expect high read volumes Request migration scripts if you're modifying an existing schema rather than creating from scratch Consider asking for database-specific optimizations (MySQL, PostgreSQL, etc.) if you know your target platform

coding intermediate

Design Database Schema

Get AI help designing optimal database schemas with proper relationships, normalization, and indexing strategies for your application.

Works with: chatgptclaudegemini

Prompt Template

You are an experienced database architect. I need help designing a comprehensive database schema for [APPLICATION_TYPE]. Application Details: - Type: [APPLICATION_TYPE] - Expected scale: [EXPECTED_SCALE] - Primary use cases: [PRIMARY_USE_CASES] - Key entities involved: [KEY_ENTITIES] Please provide: 1. **Entity-Relationship Design**: Identify all entities, their attributes, and relationships. Include cardinality (one-to-one, one-to-many, many-to-many). 2. **Normalized Schema**: Create tables following 3NF normalization principles. For each table, specify: - Table name and purpose - All columns with appropriate data types - Primary keys and foreign keys - Constraints (NOT NULL, UNIQUE, CHECK) 3. **Indexing Strategy**: Recommend indexes for: - Primary lookups - Common query patterns - Foreign key relationships - Performance-critical searches 4. **SQL DDL Statements**: Provide complete CREATE TABLE statements with all constraints and relationships. 5. **Potential Issues**: Identify potential scalability concerns, bottlenecks, or areas that might need denormalization later. 6. **Sample Queries**: Show 3-4 common queries this schema would handle efficiently. Focus on data integrity, performance, and maintainability. Consider future scalability and common access patterns for [APPLICATION_TYPE] applications.

Variables to Customize

[APPLICATION_TYPE]

The type of application you're building the database for

Example: e-commerce platform with inventory management

[EXPECTED_SCALE]

The expected size and usage scale of your application

Example: 10,000 products, 50,000 users, 1,000 orders per day

[PRIMARY_USE_CASES]

The main functions and operations your application will perform

Example: product browsing, order processing, inventory tracking, user account management

[KEY_ENTITIES]

The main business objects or entities in your system

Example: users, products, categories, orders, inventory, suppliers, reviews

Example Output

## Entity-Relationship Design **Core Entities:** - Users (customers, admins) - Products (items for sale) - Categories (product organization) - Orders (purchase transactions) - Order_Items (individual items in orders) - Inventory (stock management) - Suppliers (product sources) ## Normalized Schema ### Users Table ```sql CREATE TABLE users ( user_id INT PRIMARY KEY AUTO_INCREMENT, email VARCHAR(255) UNIQUE NOT NULL, password_hash VARCHAR(255) NOT NULL, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, is_active BOOLEAN DEFAULT TRUE ); ``` ### Products Table ```sql CREATE TABLE products ( product_id INT PRIMARY KEY AUTO_INCREMENT, name VARCHAR(255) NOT NULL, description TEXT, price DECIMAL(10,2) NOT NULL, category_id INT, supplier_id INT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, FOREIGN KEY (category_id) REFERENCES categories(category_id), FOREIGN KEY (supplier_id) REFERENCES suppliers(supplier_id) ); ``` ## Indexing Strategy - Index on users.email for login queries - Index on products.category_id for category browsing - Index on orders.user_id for order history - Composite index on (product_id, created_at) for recent product queries

Pro Tips for Best Results

Always specify the expected data volume and query patterns - this helps the AI recommend appropriate data types and indexing strategies
Include your performance requirements (response times, concurrent users) to get better optimization suggestions
Ask for both normalized and potential denormalized versions if you expect high read volumes
Request migration scripts if you're modifying an existing schema rather than creating from scratch
Consider asking for database-specific optimizations (MySQL, PostgreSQL, etc.) if you know your target platform