When a user needs database schema design:
- 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?
- 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
- Generate Complete Schema:
SQL Schema Output:
```sql
-- [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):
```javascript
// [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
```
- Create Entity Relationship Diagram (text format):
```
βββββββββββββββββββββββ
β users β
βββββββββββββββββββββββ€
β id (PK) β
β email (UNIQUE) β
β name β
β created_at β
ββββββββββββ¬βββββββββββ
β
β 1:N
β
ββββββββββββΌβββββββββββ
β posts β
βββββββββββββββββββββββ€
β id (PK) β
β user_id (FK) β
β title β
β content β
β created_at β
ββββββββββββ¬βββββββββββ
β
β N:M (via post_tags)
β
ββββββββββββΌβββββββββββ
β tags β
βββββββββββββββββββββββ€
β id (PK) β
β name (UNIQUE) β
βββββββββββββββββββββββ
```
- Provide Migration Scripts:
```sql
-- 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;
```
```sql
-- Rollback
BEGIN;
DROP TABLE users;
COMMIT;
```
- Format Complete Output:
```
ποΈ 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 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
- Composite indexes for multi-column queries
- Consider covering indexes
- Monitor index usage and remove unused ones
Relationships:
- Always use foreign keys in relational DBs
- 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
|