Design, Scale, and Optimize Databases Faster Using These 30 Advanced ChatGPT Prompts

You look at what the application needs, make some decisions about how to organize the data, and hope those decisions don’t come back to haunt you six months later when queries are crawling. That’s not a flaw in your process. That’s just how database design works.

The problem is that most of us don’t have a senior DBA nearby to talk through every decision. Should this be one table or three? Is this index helping or quietly slowing down writes? These aren’t questions you can Google with a single search term. They need context and back-and-forth.

This collection of prompts covers the full span of database design, from your first ER diagram to troubleshooting production issues. Each prompt uses placeholders so you can swap in your own details. Jump to whichever section matches the problem in front of you.

I. Requirements & Conceptual Modeling (The Blueprint)

Before writing a single CREATE TABLE statement, you need to nail down the access patterns and edge cases. These prompts force the AI to act as an interrogator and architect.

1. Act as a Senior Data Architect. I am designing a [Database Engine, e.g., PostgreSQL] database for a [App Type, e.g., multi-tenant SaaS billing system]. Before we write any schema, ask me exactly 5 targeted questions about my data access patterns, read/write ratios, and growth expectations so we can determine the optimal architectural approach.
2. I need to model [Complex Domain, e.g., a hospital appointment booking system with recurring visits]. Break down the core entities, identify the exact relationships (1:1, 1:N, M:N), and highlight the hardest edge cases I need to account for in this specific domain.
3. We are debating between a single-table design vs. highly normalized relational tables for storing [Type of Data, e.g., user custom attributes and settings]. Give me a brutal breakdown of the trade-offs regarding query performance, storage costs, and future flexibility.
4. Analyze my proposed domain entities: [List Entities]. Identify any polymorphic associations or temporal data challenges (like keeping historical records of changes) and suggest how to model them cleanly.

II. Schema Design & Table Structures

This is where the rubber meets the road. Use these to generate strict, constraint-heavy schemas.

1. Generate a production-ready DDL script for a [System Type, e.g., an inventory management system] using [Database Engine]. Include tables for [Core Entities]. Enforce strict data integrity using CHECK constraints, foreign keys with appropriate ON DELETE rules, and sensible default values. Do not use default auto-incrementing integers for public-facing IDs; suggest a safer alternative.
2. I need a robust way to handle hierarchical data for [Scenario, e.g., a nested category tree or organizational chart]. Compare Adjacency List, Materialized Path, and Nested Sets for my specific read/write patterns: [Describe your expected reads/writes], and write the schema for the winner.
3. I have a legacy, denormalized flat file with these columns: [List Columns]. Walk me through normalizing this dataset into 3rd Normal Form (3NF). Output the final tables with primary and foreign keys clearly defined.
4. I want to use [Database Engine's] JSON/JSONB features to store [Describe Data, e.g., varied product specifications]. Write the schema, and show me how to structure the JSON schema internally so that I can still efficiently index and query against these nested fields.

III. Bridging the Gap: Visuals, ORMs, and Mock Data

Modern developers rarely just write raw SQL and stop there. You need to visualize your architecture, map it to your application code, and fill it with test data. These prompts bridge the gap between database and backend.

1. The Visual Planner: Translate my database schema into a visual Entity-Relationship Diagram. Generate the exact Mermaid.js syntax for these tables: [Paste Schema/Description]. Output only the Mermaid code block so I can drop it directly into the Mermaid Live Editor to view my architecture.
2. The Application Mapper: I am using [ORM/Framework, e.g., Prisma / Django / Laravel Eloquent] for my backend. Take this SQL schema: [Paste Schema] and generate the exact model definitions. Ensure that all one-to-many and many-to-many relationship mappings, foreign keys, and cascading delete rules are correctly translated into the framework’s native syntax.
3. The Fake Data Seeder: Act as a mock data generator for my local testing environment. Read this table schema: [Paste Schema] and write the SQL INSERT statements to populate it with [Number] rows of highly realistic, structurally valid dummy data. Crucially, make sure foreign key dependencies between tables are respected (e.g., users exist before their orders) so the script runs without failing.

IV. Indexing & Query Tuning

Because an unindexed table is a ticking time bomb.

1. My database is suffering from slow reads on this query: [Paste SQL Query]. The table has [Number] million rows and my current indexes are [List current indexes]. Suggest an optimal covering index or composite index for this exact query, and explain the column order you chose.
2. Explain the execution plan for this [Database Engine] query. Here is the EXPLAIN ANALYZE output: [Paste Output]. Identify the exact bottleneck (e.g., sequential scans, heavy sorts) and give me the precise DDL to fix it.
3. I have a table with columns: [List Columns]. My application frequently runs queries filtering by [Column A], sorting by [Column B], and occasionally grouping by [Column C]. Design the perfect composite index strategy, keeping index bloat and write-penalties in mind.
4. Write a query to identify unused, overlapping, or redundant indexes in my [Database Engine] database so I can clean up my schema and improve write performance.

V. Advanced SQL & Logic

When simple JOINs aren’t enough, these prompts help you build complex analytical queries and views.

1. Write an optimized SQL query using Window Functions to calculate [Metric, e.g., the 7-day rolling average of sales per user] from this schema: [Paste Schema]. Ensure it handles gaps in data (days with zero sales) gracefully.
2. I am running into the classic N+1 query problem when fetching [Entity A] and their related [Entity B]. Write a single, highly efficient query to fetch this hierarchical data using JSON aggregation / Arrays / CTEs in [Database Engine].
3. Convert this slow, nested subquery into a more readable and performant Common Table Expression (CTE): [Paste Query].
4. I need to implement a fuzzy search feature on [Column Name] in my [Database Engine] database. Compare the performance of LIKE ‘%term%’, full-text search extensions (like pg_trgm), and dedicated search engines for a table of [Number] rows.

VI. Architecture, Scaling & Migrations

For when your application starts to grow and a single instance won’t cut it anymore.

1. Outline a zero-downtime database migration strategy using the Expand/Contract pattern. I need to [Describe Change, e.g., split a 'users' table into 'users' and 'profiles'] without locking the table or breaking the live [Language/Framework] application.
2. My [App Type] is becoming write-heavy and hitting IOPS limits on [Database Engine]. Walk me through the pros and cons of implementing read replicas vs. horizontal sharding based on this shard key: [Proposed Key].
3. Design a table partitioning strategy for a time-series table storing [Describe Data, e.g., IoT sensor logs] that grows by [Data Volume] per month. Write the DDL to automatically create partitions by [Timeframe, e.g., month] and drop partitions older than [Retention Period].
4. I am migrating from [Old DB, e.g., MongoDB] to [New DB, e.g., PostgreSQL]. Identify the massive paradigm shifts I need to account for, specifically regarding [Pain Point, e.g., schema validation, transaction guarantees, or joining data].

VII. Security & Data Integrity

Don’t end up on a data-breach headline.

1. I need to implement Row-Level Security (RLS) in [PostgreSQL / Database] for a multi-tenant application. Write the policies ensuring that users belonging to tenant_id can only SELECT, UPDATE, and DELETE their own rows in the [Table Name] table.
2. Design an audit-logging system for a highly sensitive [Domain, e.g., Payroll] database. I want to track exactly who changed a record, when, and the before/after state of the data. Should I use triggers, an append-only event table, or application-level logging? Write the implementation for the best approach.
3. I am storing [Sensitive Data, e.g., personal health information]. Provide a comprehensive strategy for encrypting this data at rest, in transit, and specifically at the column level within [Database Engine].
4. Review my current authentication schema: [Paste Schema]. Point out any security vulnerabilities (e.g., poor password hashing setups, lack of rate-limiting logs, missing constraints) and rewrite it to bank-level security standards.

VIII. NoSQL & Alternative Paradigms

Relational isn’t always the answer. Use these when working with document, graph, or key-value stores.

1. Act as a MongoDB expert. I am building a [System Type, e.g., social feed]. My read-to-write ratio is [Ratio]. Should I embed the [Child Entity, e.g., comments] directly inside the parent document, or use references? Give me the exact JSON schema structure based on my query constraints.
2. Design a graph database schema for Neo4j to model [Scenario, e.g., a recommendation engine based on user purchases and friendships]. Write the Cypher queries to find [Specific Insight, e.g., 'friends of friends who bought product X'].
3. I am using Redis as a caching layer in front of my [Primary DB]. Design a caching strategy for [Specific Data, e.g., user session states and product catalogs], including the exact cache invalidation strategy (e.g., TTL, write-through, or lazy loading) to avoid stale data.

Database design isn’t something you finish. It evolves as your application grows and your data gets messier. These prompts give you a faster way to think through problems when they show up.

Treat ChatGPT like a conversation, not a search engine. If a response misses the mark, push back with more context. That’s where the real value is.