Generate Database Designs with Genspark: A Practical Guide from Normalization to Optimization

📋 Table of Contents

Introduction: The Importance of Database Design

Database design is the most critical foundation of application development. Design mistakes are very difficult to correct later and can lead to performance issues and data integrity problems. Denormalized table designs may require extensive refactoring later on.

By leveraging Genspark for database design, we can now generate high-quality schemas, including normalization, relation design, and index optimization, in a short amount of time.

Important Recommendation 1: Genspark is a powerful partner for database design. Simply providing your requirements allows it to propose normalized schemas, appropriate indexes, and even constraint conditions. However, the final verification should always be done by a human.

Design Mistakes and Performance Issues Encountered with Manual Design

When designing database schemas manually in the early stages of a project, we encountered the following issues:

Problems Encountered

  • ❌ Insufficient Normalization: Duplicate information across multiple tables
  • ❌ N+1 Problem: Exploding queries due to relation design errors
  • ❌ Missing Indexes: Searches taking over 10 seconds with large datasets
  • ❌ Insufficient Constraints: Influx of invalid data
  • ❌ Lack of Documentation: Unclear relationships between tables

To resolve these issues, by asking Genspark to "propose an appropriate database design," we could sometimes have overlooked problems pointed out.

Case Study 1: Automatically Generating Normalized Schemas from Requirements

Here's an example where we requested database design for an e-commerce site.

Request to Genspark

Design an e-commerce site database schema with the following requirements:

## Requirements
- User registration and login
- Product management (categorization)
- Shopping cart
- Order and payment
- Product reviews
- Wishlist

## Constraints
- Use PostgreSQL
- Normalize to 3rd normal form
- Appropriate foreign key constraints
- Include index design
- Output in Prisma ORM format

Genspark generated a perfect schema like the following:

Generated Prisma Schema (Excerpt)

// schema.prisma

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

// User
model User {
  id            String   @id @default(uuid())
  email         String   @unique
  passwordHash  String   @map("password_hash")
  name          String
  createdAt     DateTime @default(now()) @map("created_at")
  updatedAt     DateTime @updatedAt @map("updated_at")
  
  // Relations
  orders        Order[]
  reviews       Review[]
  cartItems     CartItem[]
  wishlistItems WishlistItem[]
  
  @@index([email])
  @@map("users")
}

// Product Category (supports hierarchical structure)
model Category {
  id        String     @id @default(uuid())
  name      String
  slug      String     @unique
  parentId  String?    @map("parent_id")
  parent    Category?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children  Category[] @relation("CategoryHierarchy")
  
  // Relations
  products  Product[]
  
  @@index([slug])
  @@index([parentId])
  @@map("categories")
}

// Product
model Product {
  id          String   @id @default(uuid())
  name        String
  slug        String   @unique
  description String
  price       Decimal  @db.Decimal(10, 2)
  stock       Int      @default(0)
  categoryId  String   @map("category_id")
  category    Category @relation(fields: [categoryId], references: [id])
  imageUrl    String?  @map("image_url")
  isActive    Boolean  @default(true) @map("is_active")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
  
  // Relations
  cartItems     CartItem[]
  orderItems    OrderItem[]
  reviews       Review[]
  wishlistItems WishlistItem[]
  
  @@index([slug])
  @@index([categoryId])
  @@index([isActive])
  @@map("products")
}

// Shopping Cart
model CartItem {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  productId String   @map("product_id")
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  quantity  Int
  createdAt DateTime @default(now()) @map("created_at")
  
  @@unique([userId, productId])
  @@index([userId])
  @@map("cart_items")
}

// Order
model Order {
  id            String      @id @default(uuid())
  userId        String      @map("user_id")
  user          User        @relation(fields: [userId], references: [id])
  status        OrderStatus @default(PENDING)
  totalAmount   Decimal     @map("total_amount") @db.Decimal(10, 2)
  shippingAddress String    @map("shipping_address")
  createdAt     DateTime    @default(now()) @map("created_at")
  updatedAt     DateTime    @updatedAt @map("updated_at")
  
  // Relations
  orderItems    OrderItem[]
  
  @@index([userId])
  @@index([status])
  @@index([createdAt])
  @@map("orders")
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  DELIVERED
  CANCELLED
}

// Order Item
model OrderItem {
  id        String  @id @default(uuid())
  orderId   String  @map("order_id")
  order     Order   @relation(fields: [orderId], references: [id], onDelete: Cascade)
  productId String  @map("product_id")
  product   Product @relation(fields: [productId], references: [id])
  quantity  Int
  price     Decimal @db.Decimal(10, 2)  // Price at the time of purchase
  
  @@index([orderId])
  @@map("order_items")
}

// Product Review
model Review {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  productId String   @map("product_id")
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  rating    Int      // 1-5
  comment   String?
  createdAt DateTime @default(now()) @map("created_at")
  
  @@unique([userId, productId])  // One review per user per product
  @@index([productId])
  @@index([rating])
  @@map("reviews")
}

// Wishlist
model WishlistItem {
  id        String   @id @default(uuid())
  userId    String   @map("user_id")
  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  productId String   @map("product_id")
  product   Product  @relation(fields: [productId], references: [id], onDelete: Cascade)
  createdAt DateTime @default(now()) @map("created_at")
  
  @@unique([userId, productId])
  @@index([userId])
  @@map("wishlist_items")
}

Strengths of the Generated Schema

  • ✅ Fully Normalized: No data duplication, 3rd normal form achieved
  • ✅ Appropriate Relations: Foreign key constraints, cascade delete settings
  • ✅ Index Optimization: Appropriate indexes on frequently searched columns
  • ✅ Unique Constraints: Expressing business rules with constraints
  • ✅ Enum Type Usage: Type-safe handling of order statuses, etc.
  • ✅ Timestamps: Automatic management of creation and update times
Important Recommendation 2: Genspark is well-versed in database design best practices. It covers points that humans often overlook, such as normalization, relation design, and constraint conditions. It is important to communicate clear requirements.

Case Study 2: Index Design and Query Optimization

After implementing the schema, performance issues arose. We consulted Genspark about the problem of "slow product search."

Consultation on Performance Issues

The following query is slow (over 10 seconds). Please optimize it.

```sql
SELECT p.*, c.name as category_name, AVG(r.rating) as avg_rating
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN reviews r ON p.id = r.product_id
WHERE p.name ILIKE '%keyword%'
  AND p.is_active = true
  AND p.price BETWEEN 1000 AND 5000
GROUP BY p.id, c.name
ORDER BY avg_rating DESC
LIMIT 20;
```

Tables: products (1 million records), reviews (5 million records)

Genspark provided optimization suggestions like the following:

Optimization Proposal

-- 1. Add full-text search index
CREATE INDEX idx_products_name_gin 
ON products USING gin(to_tsvector('english', name));

-- 2. Add composite index
CREATE INDEX idx_products_active_price 
ON products(is_active, price) 
WHERE is_active = true;

-- 3. Pre-calculate review aggregates (Materialized View)
CREATE MATERIALIZED VIEW product_ratings AS
SELECT 
  product_id,
  AVG(rating) as avg_rating,
  COUNT(*) as review_count
FROM reviews
GROUP BY product_id;

CREATE UNIQUE INDEX ON product_ratings(product_id);

-- 4. Optimized query
SELECT 
  p.*,
  c.name as category_name,
  pr.avg_rating,
  pr.review_count
FROM products p
LEFT JOIN categories c ON p.category_id = c.id
LEFT JOIN product_ratings pr ON p.id = pr.product_id
WHERE p.name @@ to_tsquery('english', 'keyword')
  AND p.is_active = true
  AND p.price BETWEEN 1000 AND 5000
ORDER BY pr.avg_rating DESC NULLS LAST
LIMIT 20;

-- Regular update of materialized view
REFRESH MATERIALIZED VIEW CONCURRENTLY product_ratings;

Improvement Result: 10 seconds → 50ms (200x faster)

Principles of Index Design

Principles of index design learned from Genspark:

  1. Index on WHERE clause condition columns: Conditions frequently searched
  2. Index on JOIN condition columns: Essential for foreign keys
  3. Index on ORDER BY columns: Speeds up sorting
  4. Composite index order: Place highly selective columns first
  5. Partial indexes: Indexing only specific conditions to reduce size
  6. Full-text search indexes: LIKE searches are slow; use GIN indexes
Important Recommendation 3: More indexes are not always better. You should consider the trade-off with write performance and create only truly necessary indexes. Genspark helps propose an appropriate balance.

Case Study 3: ERD Generation and Documentation Automation

To visualize the database schema, we asked Genspark to generate an ERD (Entity Relationship Diagram).

ERD Generation Request

Please convert the above Prisma schema into an ERD in Mermaid format.
Clearly display the relationships between tables.

Genspark generated the following Mermaid notation:

Generated Mermaid ERD

```mermaid
erDiagram
    User ||--o{ Order : places
    User ||--o{ Review : writes
    User ||--o{ CartItem : has
    User ||--o{ WishlistItem : has
    
    Category ||--o{ Product : contains
    Category ||--o{ Category : "parent/child"
    
    Product ||--o{ CartItem : "added to"
    Product ||--o{ OrderItem : "ordered"
    Product ||--o{ Review : "reviewed"
    Product ||--o{ WishlistItem : "wished"
    
    Order ||--|{ OrderItem : contains
    
    User {
        uuid id PK
        string email UK
        string passwordHash
        string name
        datetime createdAt
        datetime updatedAt
    }
    
    Category {
        uuid id PK
        string name
        string slug UK
        uuid parentId FK
    }
    
    Product {
        uuid id PK
        string name
        string slug UK
        text description
        decimal price
        int stock
        uuid categoryId FK
        string imageUrl
        boolean isActive
        datetime createdAt
        datetime updatedAt
    }
    
    Order {
        uuid id PK
        uuid userId FK
        enum status
        decimal totalAmount
        string shippingAddress
        datetime createdAt
        datetime updatedAt
    }
    
    OrderItem {
        uuid id PK
        uuid orderId FK
        uuid productId FK
        int quantity
        decimal price
    }
    
    Review {
        uuid id PK
        uuid userId FK
        uuid productId FK
        int rating
        string comment
        datetime createdAt
    }
    
    CartItem {
        uuid id PK
        uuid userId FK
        uuid productId FK
        int quantity
        datetime createdAt
    }
    
    WishlistItem {
        uuid id PK
        uuid userId FK
        uuid productId FK
        datetime createdAt
    }
```

Automated Documentation Generation

Furthermore, we also had detailed documentation generated for each table.

Generate detailed documentation for each table from the Prisma schema
in Markdown format.
Please include descriptions for each column, constraints, and indexes.

The generated documentation was shared with the entire team and helped deepen understanding of the database.

Important Recommendation 4: ERDs and documentation are essential for shared understanding across the team. By having Genspark automatically generate them, you can always maintain up-to-date documentation even when the schema changes.

Complete Workflow for Database Design

Here is a summary of the complete database design workflow leveraging Genspark.

Phase 1: Requirements Definition and Schema Generation

  1. Organize business requirements
  2. Communicate detailed requirements to Genspark
  3. Review the generated schema
  4. Request corrections as needed

Phase 2: Migration Creation

# Generate Prisma Migration
npx prisma migrate dev --name init

# Verify the generated migration file
cat prisma/migrations/*/migration.sql

Phase 3: Index Optimization

  1. List anticipated queries
  2. Analyze query plans with EXPLAIN
  3. Request index suggestions from Genspark
  4. Create migrations to add indexes

Phase 4: Documentation Generation

  1. Generate ERD (Mermaid format)
  2. Generate table definition document (Markdown format)
  3. Add to GitHub repository README

Phase 5: Continuous Improvement

  1. Monitor slow queries in the production environment
  2. Consult Genspark for optimization
  3. Always create migrations when the schema changes
  4. Update documentation simultaneously
Important Recommendation 5: Database design is not a one-time task but should evolve continuously. Genspark can accelerate the cycle of schema changes, optimization, and documentation updates.

Summary: The Future of AI and Database Design

Genspark significantly improves design quality in database design. It covers points that humans often overlook, from normalization and index design to documentation generation.

Results of Genspark Utilization

  • Design Time: 3 days → Half a day (6x faster)
  • Design Quality: Significantly improved (normalization, constraints, indexes)
  • Query Performance: 200x faster (index optimization)
  • Documentation: Always kept up-to-date

Actions You Can Start Today

  • ✅ Have Genspark review existing schemas
  • ✅ Consult on slow query optimization
  • ✅ Automatically generate ERDs and documentation
  • ✅ Co-design new projects with Genspark

Database design is entering a new era through collaboration with AI.

References