Database Schema Prompt for Clean Data Models
A structured prompt that designs a normalized database schema for your app — tables, keys, and relations — with DDL and a real example.
Enhanced prompt — ready to use
ROLE You are a senior database architect with expertise in designing scalable and efficient database schemas. Your goal is to create a robust schema tailored to specific application requirements. TASK Design a comprehensive database schema that supports key entities, relationships, and constraints necessary for the application. Think through the data model step by step, considering normalization, indexing, data integrity, and scalability. CONTEXT The user requires a detailed database schema design for an unspecified application. The design should accommodate typical relational database best practices to ensure data consistency, performance, and future extensibility. REQUIREMENTS - Define entities (tables), their attributes (fields), primary and foreign keys. - Establish relationships between entities (one-to-one, one-to-many, many-to-many). - Ensure normalization to reduce redundancy. - Incorporate indexing strategies for optimal query performance. - Consider data integrity constraints and potential scalability challenges. - Think step by step and consider edge cases. - Before finalizing, verify your design for correctness and completeness. OUTPUT FORMAT Provide a detailed description of the schema design including: - An entity-relationship diagram (conceptual description if images are not possible) - A list of tables with their fields, data types, and keys - Explanation of relationships and constraints - Notes on indexing and performance considerations
That's what PromptAI produced from this vague prompt: “design a database schema”
Example result
Certainly! Since the application type is unspecified, I'll design a **flexible, modular, and scalable relational database schema** suitable for a generic multi-functional application that manages users, roles, permissions, content, transactions, and audit/history. This schema can be adapted to many domains such as SaaS platforms, enterprise applications, or content management systems. --- ## Step 1: Identify Core Entities 1. **Users** – represents people who interact with the system. 2. **Roles** – groups of permissions assigned to users. 3. **Permissions** – granular access controls in the system. 4. **User_Role** – mapping users to roles (many-to-many). 5. **Role_Permission** – mapping roles to permissions (many-to-many). 6. **Content_Items** – generalized content model to support extensibility. 7. **Content_Types** – to categorize content for future extensibility. 8. **Transactions** – represents any type of financial or logical transactions. 9. **Audit_Logs** – for tracking changes and important system events. 10. **Settings** – application-wide settings or user preferences. --- ## Step 2: Define Entities and Relationships ### Conceptual Entity-Relationship Diagram (textual): ``` [Users] 1---* [User_Role] *---1 [Roles] *---* [Role_Permission] *---1 [Permissions] [Users] 1---* [Content_Items] *---1 [Content_Types] [Users] 1---* [Transactions] [Audit_Logs] *---1 [Users] [Settings] (could be global or user-specific) ``` --- ## Step 3: Tables design with fields and keys --- ### 1. Users | Field | Data Type | Constraints | Notes | |-------------------|---------------------|--------------------------------|----------------------------------------| | user_id | UUID / BIGSERIAL | PRIMARY KEY | Use UUID for distributed scalablility | | username | VARCHAR(50) | UNIQUE, NOT NULL | Indexed for login search | | email | VARCHAR(255) | UNIQUE, NOT NULL | Indexed | | password_hash | VARCHAR(255) | NOT NULL | Store hashed passwords (never plaintext) | | first_name | VARCHAR(50) | NULLABLE | | | last_name | VARCHAR(50) | NULLABLE | | | is_active | BOOLEAN | DEFAULT TRUE | Soft deactivate without deleting | | created_at | TIMESTAMP WITH TZ | DEFAULT NOW() | Index for filtering recent users | | updated_at | TIMESTAMP WITH TZ | ON UPDATE CURRENT_TIMESTAMP | | Indexes: - user_id (PK) - username (UNIQUE) - email (UNIQUE) - created_at (for reporting on signups) --- ### 2. Roles | Field | Data Type | Constraints | Notes | |----------|---------------|-----------------------|-----------------------------| | role_id | SERIAL / UUID | PRIMARY KEY | UUID recommended for large scale | | name | VARCHAR(50) | UNIQUE, NOT NULL | e.g., admin, editor, viewer | | description | TEXT | NULLABLE | Explanation of role purpose | --- ### 3. Permissions | Field | Data Type | Constraints | Notes | |--------------|---------------|---------------------|----------------------------------| | permission_id | SERIAL / UUID | PRIMARY KEY | UUID recommended | | name | VARCHAR(100) | UNIQUE, NOT NULL | e.g., read_content, edit_user | | description | TEXT | NULLABLE | Explains permission use case | --- ### 4. User_Role (junction table for many-to-many) | Field | Data Type | Constraints | Notes | |---------|-----------|--------------------|------------------------------| | user_role_id | SERIAL | PRIMARY KEY | Surrogate key | | user_id | UUID | FOREIGN KEY -> Users(user_id), NOT NULL | Indexed | | role_id | UUID | FOREIGN KEY -> Roles(role_id), NOT NULL | Indexed | | assigned_at | TIMESTAMP WITH TZ | DEFAULT NOW() | Tracks assignment time | Unique Constraint: `(user
How to adapt it
- •Describe the entities and how they relate so keys and joins are correct.
- •Tell it your database (Postgres, MySQL) for accurate types and constraints.
Have your own rough prompt? Enhance it into a structured prompt like this in one click.
Enhance your own promptMore coding prompts
API Documentation Prompt for Clear Docs
A structured prompt that documents your API endpoint — params, responses, errors, and examples — in clean reference style, with a real example.
Bash Script Prompt for Shell Automation
A structured prompt that writes a safe, portable Bash script for your task — with checks, comments, and a real example output.
Bug Fix Prompt That Finds Root Causes
A structured prompt that diagnoses and fixes a bug — root cause, the fix, and why it works — instead of a guess, with a real example.
Code Comment Prompt for Useful Comments
A structured prompt that adds clear, non-obvious comments and docstrings to your code — explaining why, not just what — with a real example.
Code Review Prompt for Better Feedback
A structured prompt that turns ChatGPT into a thorough code reviewer — bugs, edge cases, readability, and security — with a real example review.
Dockerfile Prompt for Production Images
A structured prompt that writes a secure, optimized Dockerfile for your app — multi-stage, small, and cache-friendly — with a real example.