Database Schema Designer

Database Schema Designer

Design optimized, scalable database schemas with proper relationships and indexes.

Contents

• references/schema-templates.md — SQL/NoSQL schema templates, ASCII ERD, and migration script templates.
• references/output-format.md — labeled section structure for the final deliverable.
• references/best-practices.md — naming, data types, indexes, relationships, SQL/NoSQL design rules, and the output quality checklist.

Workflow

1. Gather requirements. Determine the database engine (PostgreSQL, MySQL, MongoDB, etc.), application domain, main entities, most common queries, expected data volume and growth, performance requirements, and any compliance constraints.

2. Design the schema. Apply the engine-appropriate rules in references/best-practices.md (SQL Database Design or NoSQL Database Design).

3. Generate the complete schema. Produce CREATE TABLE statements or collection documents using the templates in references/schema-templates.md.

4. Create the entity relationship diagram in text format following the ERD template in references/schema-templates.md.

5. Provide migration scripts with both up and down (rollback) paths, per the migration template in references/schema-templates.md.

6. Format the complete output using the labeled sections in references/output-format.md.

7. Verify the result against the output quality checklist in references/best-practices.md.

Example Triggers

• "Design a database schema for an e-commerce platform"
• "Create SQL tables for a blog system"
• "Help me design a MongoDB schema for a social network"
• "Optimize this database schema for performance"
• "Generate migration scripts for my schema"

Generate production-ready, optimized database schemas that scale.

Schema Design Best Practices

Naming Conventions

• Use snake_case for table and column names.
• Pluralize table names (users, posts).
• Use descriptive foreign key names (user_id, not uid).
• Prefix indexes (idx_table_column).
• Prefix constraints (fk_, uk_, ck_).

Data Types

• Use appropriate types (INT vs BIGINT, VARCHAR vs TEXT).
• Consider storage size.
• Use ENUM for fixed sets of values.
• Use JSON/JSONB for flexible attributes.
• Use proper date/time types (TIMESTAMP vs DATETIME).

Indexes

• Index foreign keys.
• Index columns in WHERE clauses.
• Add composite indexes for multi-column queries.
• Consider covering indexes.
• Monitor index usage and remove unused ones.

Relationships

• Always use foreign keys in relational databases.
• Cascade deletes where appropriate.
• Consider soft deletes for audit trails.
• Use junction tables for many-to-many.

Performance

• Denormalize for read-heavy workloads.
• Partition large tables.
• Use materialized views for complex queries.
• Consider read replicas.
• Plan for archival of old data.

SQL Database Design

• Identify entities and their attributes.
• Define primary keys (prefer UUIDs for distributed systems).
• Establish relationships (1:1, 1:N, N:M).
• Normalize to 3NF (unless denormalization is needed for performance).
• Add appropriate indexes.
• Define foreign key constraints.
• Include timestamps (created_at, updated_at).
• Add soft delete flags if needed.
• Plan for data archival.

NoSQL Database Design

• Design for access patterns (query-first approach).
• Decide embed vs reference per relationship.
• Plan for denormalization.
• Design indexes for common queries.
• Account for document size limits.
• Plan for eventual consistency.

Output Quality Checklist

Ensure every schema:

• Follows normalization principles (unless deliberately denormalized).
• Includes all necessary constraints.
• Has appropriate indexes.
• Uses proper data types.
• Includes timestamps.
• Has clear relationships.
• Considers scalability.
• Includes migration scripts.
• Follows naming conventions.
• Is documented with comments.
• Considers performance implications.
• Includes rollback capability.

Output Format

Structure the final deliverable using these labeled sections.

DATABASE SCHEMA DESIGN
Database: [PostgreSQL/MySQL/MongoDB/etc.]
Domain: [Application type]

== ENTITY RELATIONSHIP DIAGRAM ==

[ASCII ERD]

== TABLE DEFINITIONS ==

[SQL CREATE TABLE statements]

== RELATIONSHIPS ==

[Foreign key constraints]

== INDEXES ==

[Index definitions with rationale]

== MIGRATION SCRIPTS ==

[Up and down migrations]

== OPTIMIZATION NOTES ==

Performance Considerations:
- [Index strategy]
- [Partitioning recommendations]
- [Denormalization opportunities]

Scaling Strategy:
- [Sharding approach]
- [Read replicas]
- [Caching layer]

Data Integrity:
- [Constraint strategy]
- [Validation rules]
- [Audit logging]

Schema Templates

Reference templates for generating SQL and NoSQL schemas. Replace bracketed placeholders with domain-specific values.

SQL Schema Output

-- [Entity Name] Table
-- Purpose: [Description]

CREATE TABLE [table_name] (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  [field_name] [TYPE] [CONSTRAINTS],
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW(),
  deleted_at TIMESTAMP
);

-- Indexes
CREATE INDEX idx_[table]_[field] ON [table]([field]);
CREATE INDEX idx_[table]_[field1]_[field2] ON [table]([field1], [field2]);

-- Foreign Keys
ALTER TABLE [child_table]
  ADD CONSTRAINT fk_[constraint_name]
  FOREIGN KEY ([foreign_key_field])
  REFERENCES [parent_table](id)
  ON DELETE CASCADE;

NoSQL Schema Output (MongoDB example)

// [Collection Name]
// Purpose: [Description]

{
  _id: ObjectId,
  [field_name]: [type],

  // Embedded document
  [embedded_object]: {
    field1: type,
    field2: type
  },

  // Reference
  [related_id]: ObjectId,  // Ref to [other_collection]

  created_at: ISODate,
  updated_at: ISODate
}

// Indexes
db.[collection].createIndex({ field: 1 })
db.[collection].createIndex({ field1: 1, field2: -1 })
db.[collection].createIndex({ field: "text" })  // Text search

Entity Relationship Diagram (text format)

+---------------------+
|      users          |
+---------------------+
| id (PK)             |
| email (UNIQUE)      |
| name                |
| created_at          |
+----------+----------+
           |
           | 1:N
           |
+----------v----------+
|      posts          |
+---------------------+
| id (PK)             |
| user_id (FK)        |
| title               |
| content             |
| created_at          |
+----------+----------+
           |
           | N:M (via post_tags)
           |
+----------v----------+
|       tags          |
+---------------------+
| id (PK)             |
| name (UNIQUE)       |
+---------------------+

Migration Scripts

-- Migration: create_users_table
-- Date: 2024-01-15

BEGIN;

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP NOT NULL DEFAULT NOW(),
  updated_at TIMESTAMP NOT NULL DEFAULT NOW()
);

CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_created_at ON users(created_at);

COMMIT;

-- Rollback
BEGIN;
DROP TABLE users;
COMMIT;