Natural Language Schema Design

Problem Being Solved

Core Problem: Database schema design requires specialized expertise that most developers lack, leading to poorly designed schemas that cause performance and scalability issues.

Root Cause: Traditional schema design requires deep knowledge:

• Normalization Theory: 1NF through 5NF, when to denormalize
• Index Strategy: B-tree vs hash, composite indexes, covering indexes
• Data Type Selection: VARCHAR vs TEXT, INT vs BIGINT, DECIMAL precision
• Relationship Modeling: Foreign keys, junction tables, inheritance
• Performance Implications: N+1 queries, join costs, storage overhead

HeliosDB Solution: AI-Powered Schema Design

Architecture

HeliosDB NL Schema Design:
┌─────────────────────────────────────────────────────────────┐
│                 Natural Language Input                       │
│  "I need a database for an e-commerce store with products,  │
│   customers, orders, and reviews. Products have categories  │
│   and multiple images. Customers can have multiple          │
│   addresses. Orders track shipping status."                 │
└────────────────────────────┬────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────┐
│              Entity Recognition Engine                       │
│  - Identify entities: products, customers, orders, reviews  │
│  - Extract attributes: categories, images, addresses        │
│  - Determine relationships: 1:N, N:M                        │
└────────────────────────────┬────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────┐
│              Schema Generation Engine                        │
│  - Apply normalization rules                                │
│  - Select optimal data types                                │
│  - Generate foreign keys                                    │
│  - Create junction tables for N:M                           │
└────────────────────────────┬────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────┐
│              Optimization Engine                             │
│  - Generate indexes for common queries                      │
│  - Add constraints (NOT NULL, CHECK, UNIQUE)                │
│  - Optimize for expected workload                           │
└────────────────────────────┬────────────────────────────────┘
                             │
                             ▼
┌─────────────────────────────────────────────────────────────┐
│              Validation & Output                             │
│  - Validate DDL syntax                                      │
│  - Generate ERD visualization                               │
│  - Output: PostgreSQL, MySQL, or SQLite                     │
└─────────────────────────────────────────────────────────────┘

Key Capabilities

1. Natural Language to Schema

-- Natural language input
SELECT heliosdb_design_schema($$
  I need a database for a SaaS project management tool:
  - Users belong to organizations (multi-tenant)
  - Projects belong to organizations
  - Tasks belong to projects and can be assigned to users
  - Tasks have status (todo, in_progress, done), priority, due date
  - Comments can be added to tasks by users
  - Track time spent on tasks
  - Users have roles within organizations (admin, member, viewer)
$$);

-- Generated schema (PostgreSQL):
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

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

CREATE TABLE organization_members (
    organization_id UUID REFERENCES organizations(id) ON DELETE CASCADE,
    user_id UUID REFERENCES users(id) ON DELETE CASCADE,
    role VARCHAR(20) NOT NULL CHECK (role IN ('admin', 'member', 'viewer')),
    joined_at TIMESTAMPTZ DEFAULT NOW(),
    PRIMARY KEY (organization_id, user_id)
);

CREATE TABLE projects (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    name VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) DEFAULT 'active',
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tasks (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    status VARCHAR(20) NOT NULL DEFAULT 'todo'
        CHECK (status IN ('todo', 'in_progress', 'done')),
    priority INTEGER DEFAULT 0,
    due_date DATE,
    assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
    created_by UUID NOT NULL REFERENCES users(id),
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE task_comments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),
    content TEXT NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE time_entries (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    user_id UUID NOT NULL REFERENCES users(id),
    duration_minutes INTEGER NOT NULL CHECK (duration_minutes > 0),
    description TEXT,
    logged_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for common queries
CREATE INDEX idx_org_members_user ON organization_members(user_id);
CREATE INDEX idx_projects_org ON projects(organization_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id);
CREATE INDEX idx_tasks_status ON tasks(status);
CREATE INDEX idx_tasks_due_date ON tasks(due_date);
CREATE INDEX idx_comments_task ON task_comments(task_id);
CREATE INDEX idx_time_entries_task ON time_entries(task_id);

2. Iterative Refinement

-- Add requirements
SELECT heliosdb_refine_schema($$
  Actually, tasks should support subtasks (nested tasks).
  Also add labels/tags to tasks (many-to-many).
  And we need file attachments on tasks.
$$);

-- Updated schema includes:
ALTER TABLE tasks ADD COLUMN parent_task_id UUID REFERENCES tasks(id);
CREATE INDEX idx_tasks_parent ON tasks(parent_task_id);

CREATE TABLE labels (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    organization_id UUID NOT NULL REFERENCES organizations(id),
    name VARCHAR(50) NOT NULL,
    color VARCHAR(7),
    UNIQUE (organization_id, name)
);

CREATE TABLE task_labels (
    task_id UUID REFERENCES tasks(id) ON DELETE CASCADE,
    label_id UUID REFERENCES labels(id) ON DELETE CASCADE,
    PRIMARY KEY (task_id, label_id)
);

CREATE TABLE attachments (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id UUID NOT NULL REFERENCES tasks(id) ON DELETE CASCADE,
    filename VARCHAR(255) NOT NULL,
    file_path VARCHAR(1000) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100),
    uploaded_by UUID NOT NULL REFERENCES users(id),
    uploaded_at TIMESTAMPTZ DEFAULT NOW()
);

3. Multi-Dialect Output

-- Generate for different databases
SELECT heliosdb_design_schema(
  description => 'E-commerce product catalog...',
  dialect => 'mysql'  -- or 'postgresql', 'sqlite'
);

-- MySQL output uses:
-- AUTO_INCREMENT instead of SERIAL
-- DATETIME instead of TIMESTAMPTZ
-- ENGINE=InnoDB
-- MySQL-specific index syntax

4. ERD Visualization

-- Generate visual ERD
SELECT heliosdb_generate_erd(
  schema_name => 'ecommerce',
  format => 'mermaid'  -- or 'svg', 'png', 'dbml'
);

-- Returns Mermaid diagram:
-- erDiagram
--     CUSTOMERS ||--o{ ORDERS : places
--     ORDERS ||--|{ ORDER_ITEMS : contains
--     PRODUCTS ||--o{ ORDER_ITEMS : "ordered in"
--     ...

Concrete Examples

Example 1: Startup MVP Design

Scenario: Non-technical founder needs database for MVP

-- Founder's input (no SQL knowledge needed)
SELECT heliosdb_design_schema($$
  I'm building an app for dog walkers.
  Dog owners can list their dogs with breed, age, and special needs.
  Dog walkers have profiles with experience, rates, and availability.
  Owners can book walks with walkers.
  After walks, walkers log the walk details and owners can rate them.
  Owners can save favorite walkers.
$$);

-- Output: Complete schema with:
-- - 8 tables properly normalized
-- - All relationships defined
-- - Appropriate indexes
-- - Check constraints for validation
-- - Ready for production

Example 2: Enterprise System Migration

Scenario: Legacy system documentation to new schema

-- Input: Existing system description
SELECT heliosdb_design_schema($$
  Migrate our legacy CRM:
  - Companies with contacts (multiple per company)
  - Deals/opportunities with stages and values
  - Activities (calls, emails, meetings) linked to contacts/deals
  - Products and quotes linked to deals
  - Teams of sales reps with territories
  - Commission tracking per deal per rep
$$);

-- Output: Modern schema with:
-- - Proper UUID primary keys (not auto-increment)
-- - Soft delete support
-- - Audit columns (created_at, updated_at, created_by)
-- - Multi-tenant ready (tenant_id)
-- - JSONB for flexible attributes
-- - Partitioning recommendations for large tables

Example 3: Healthcare Compliance

Scenario: HIPAA-compliant patient management

SELECT heliosdb_design_schema($$
  Healthcare patient management system (HIPAA compliant):
  - Patients with demographics and insurance
  - Providers (doctors, nurses) with specialties
  - Appointments with status and notes
  - Medical records with diagnoses and treatments
  - Prescriptions with medications and pharmacies
  - Lab results with reference ranges
  - Audit trail for all PHI access
$$);

-- Output includes:
-- - PHI columns marked for encryption
-- - Audit table for access logging
-- - Row-level security policies
-- - Data retention considerations
-- - Compliant naming conventions
-- - HIPAA-specific constraints

Conclusion

HeliosDB's natural language schema design democratizes database expertise. Organizations can:

• Improve Quality: Best practices automatically applied
• Democratize Access: Non-experts can design schemas
• Reduce Costs: No DBA needed for initial design