database-schema-designer

Database Schema Designer

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

Instructions

When a user needs database schema design:

1. Gather Requirements:

   • What type of database (PostgreSQL, MySQL, MongoDB, etc.)?
   • What is the application domain?
   • What are the main entities/resources?
   • What queries will be most common?
   • Expected data volume and growth?
   • Performance requirements?
   • Specific constraints or compliance needs?

2. Design Schema Following Best Practices:

   For SQL Databases:

   • 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 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

   For NoSQL Databases:

   • Design for access patterns (query-first approach)
   • Embed vs reference decision
   • Plan for denormalization
   • Design indexes for common queries
   • Consider document size limits
   • Plan for eventual consistency

3. Generate Complete Schema:

   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