database-schema-designer

name	database-schema-designer
description	Design robust, scalable database schemas for SQL and NoSQL databases. Provides normalization guidelines, indexing strategies, migration patterns, constraint design, and performance optimization. Ensures data integrity, query performance, and maintainable data models. Use when this capability is needed.
metadata	{"author":"gocanto"}

Database Schema Designer

Design production-ready database schemas with best practices built-in.

---

Quick Start

Just describe your data model:

design a schema for an e-commerce platform with users, products, orders

You'll get complete GORM models and PostgreSQL schema:

// User model
type User struct {
    ID        uint           `gorm:"primaryKey"`
    Email     string         `gorm:"uniqueIndex;not null;size:255"`
    CreatedAt time.Time
    UpdatedAt time.Time
    DeletedAt gorm.DeletedAt `gorm:"index"`

    // Relationships
    Orders []Order `gorm:"foreignKey:UserID"`
}

// Order model
type Order struct {
    ID        uint           `gorm:"primaryKey"`
    UserID    uint           `gorm:"index;not null"`
    User      User           `gorm:"constraint:OnDelete:CASCADE"`
    Total     decimal.Decimal `gorm:"type:decimal(10,2);not null"`
    CreatedAt time.Time
    UpdatedAt time.Time
}

// Migration
db.AutoMigrate(&User{}, &Order{})

What to include in your request:

• Entities (users, products, orders)
• Key relationships (users have orders, orders have items)
• Scale hints (high-traffic, millions of records)
• GORM-specific features needed (soft deletes, hooks, custom types)

---

Triggers

Trigger	Example
design schema	"design a schema for user authentication"
database design	"database design for multi-tenant SaaS"
create tables	"create tables for a blog system"
schema for	"schema for inventory management"
model data	"model data for real-time analytics"
I need a database	"I need a database for tracking orders"
design NoSQL	"design NoSQL schema for product catalog"

---

Key Terms

Term	Definition
GORM	Go ORM library that simplifies database operations and migrations
Struct Tags	Go tags that define GORM behavior (gorm:"primaryKey", etc.)
AutoMigrate	GORM feature that automatically creates/updates tables from structs
Soft Delete	Marks records as deleted without removing (uses DeletedAt field)
Preloading	Loading related data with Preload() to avoid N+1 queries
Hooks	GORM callbacks (BeforeCreate, AfterUpdate, etc.) for custom logic
Foreign Key (FK)	Relationship constraint defined with gorm:"foreignKey:FieldName"
Index	Defined with tags like gorm:"index" or gorm:"uniqueIndex"
Access Pattern	How your app reads/writes data (queries, joins, filters)

---

Quick Reference

Task	GORM Approach	PostgreSQL Consideration
New model	Define struct with tags	Use gorm.Model for basics
Primary keys	gorm:"primaryKey"	Auto-incrementing BIGSERIAL
Foreign keys	gorm:"foreignKey"	Add constraint:OnDelete
Indexes	gorm:"index"	Composite: gorm:"index:idx_name"
Unique	gorm:"uniqueIndex"	Can be composite too
Migrations	db.AutoMigrate()	Use golang-migrate for production
Soft delete	Include gorm.DeletedAt	Automatic WHERE clauses

---

Process Overview

Your Data Requirements
    |
    v
+-----------------------------------------------------+
| Phase 1: ANALYSIS                                   |
| * Identify entities and relationships               |
| * Determine access patterns (read vs write heavy)   |
| * Choose SQL or NoSQL based on requirements         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 2: DESIGN                                     |
| * Normalize to 3NF (SQL) or embed/reference (NoSQL) |
| * Define primary keys and foreign keys              |
| * Choose appropriate data types                     |
| * Add constraints (UNIQUE, CHECK, NOT NULL)         |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 3: OPTIMIZE                                   |
| * Plan indexing strategy                            |
| * Consider denormalization for read-heavy queries   |
| * Add timestamps (created_at, updated_at)           |
+-----------------------------------------------------+
    |
    v
+-----------------------------------------------------+
| Phase 4: MIGRATE                                    |
| * Generate migration scripts (up + down)            |
| * Ensure backward compatibility                     |
| * Plan zero-downtime deployment                     |
+-----------------------------------------------------+
    |
    v
Production-Ready Schema

---

Commands

Command	When to Use	Action
design schema for {domain}	Starting fresh	Full schema generation
normalize {table}	Fixing existing table	Apply normalization rules
add indexes for {table}	Performance issues	Generate index strategy
migration for {change}	Schema evolution	Create reversible migration
review schema	Code review	Audit existing schema

Workflow: Start with design schema → iterate with normalize → optimize with add indexes → evolve with migration

---

Core Principles

Principle	WHY	Implementation
Model the Domain	UI changes, domain doesn't	Entity names reflect business concepts
Data Integrity First	Corruption is costly to fix	Constraints at database level
Optimize for Access Pattern	Can't optimize for both	OLTP: normalized, OLAP: denormalized
Plan for Scale	Retrofitting is painful	Index strategy + partitioning plan

---

Anti-Patterns

Avoid	Why	GORM/PostgreSQL Solution
Using interface{} for types	Loses type safety	Define proper Go types
Missing gorm.Model	No timestamps/soft delete	Embed gorm.Model or add fields
float64 for money	Rounding errors	Use decimal.Decimal type
No struct tags	GORM guesses wrong	Explicit tags for all constraints
Missing FK indexes	Slow JOINs	Add gorm:"index" to FK fields
Raw SQL everywhere	Loses GORM benefits	Use GORM query builder
SELECT * with GORM	Fetches unnecessary data	Use Select() for specific columns
No connection pooling	Connection exhaustion	Configure SetMaxOpenConns()
Ignoring GORM errors	Silent failures	Always check if err != nil

---

Verification Checklist

After designing a schema:

• Every table has a primary key
• All relationships have foreign key constraints
• ON DELETE strategy defined for each FK
• Indexes exist on all foreign keys
• Indexes exist on frequently queried columns
• Appropriate data types (DECIMAL for money, etc.)
• NOT NULL on required fields
• UNIQUE constraints where needed
• CHECK constraints for validation
• created_at and updated_at timestamps
• Migration scripts are reversible
• Tested on staging with production data

---

Deep Dive: GORM Models & Normalization

Normal Forms in GORM

Form	Rule	GORM Implementation
1NF	Atomic values, no arrays in columns	Use separate models with relationships
2NF	No partial dependencies	Proper struct composition
3NF	No transitive dependencies	Reference tables via foreign keys

First Normal Form (1NF)

// BAD: Multiple values in single field
type Order struct {
    ID         uint
    ProductIDs string // "101,102,103" - violates 1NF
}

// GOOD: Separate model for items
type Order struct {
    gorm.Model
    CustomerID uint
    Items      []OrderItem `gorm:"foreignKey:OrderID"`
}

type OrderItem struct {
    gorm.Model
    OrderID   uint
    ProductID uint
    Quantity  int
    Price     decimal.Decimal `gorm:"type:decimal(10,2)"`
}

Relationships in GORM

// One-to-Many
type User struct {
    gorm.Model
    Email  string  `gorm:"uniqueIndex;not null"`
    Orders []Order `gorm:"foreignKey:UserID"`
}

// Many-to-Many
type Product struct {
    gorm.Model
    Name       string `gorm:"not null"`
    Categories []Category `gorm:"many2many:product_categories;"`
}

// Self-referencing
 Employee  {
    gorm.Model
    Name      
    ManagerID *
    Manager   *Employee  
    Team      []Employee 
}

Deep Dive: PostgreSQL Data Types in GORM

String Types

type Product struct {
    // PostgreSQL VARCHAR
    Name        string `gorm:"type:varchar(100);not null"`

    // PostgreSQL TEXT
    Description string `gorm:"type:text"`

    // Fixed length CHAR
    CountryCode string `gorm:"type:char(2)"`

    // With size constraint
    Email       string `gorm:"size:255;uniqueIndex"`
}

Numeric Types

import "github.com/shopspring/decimal"

type FinancialRecord struct {
    // Integer types
    ID          uint    `gorm:"primaryKey"`           // BIGSERIAL
    Quantity    int32   `gorm:"type:integer"`         // INTEGER
    BigNumber   int64   `gorm:"type:bigint"`          // BIGINT
    SmallCount  int16   `gorm:"type:smallint"`        // SMALLINT

    // Money - ALWAYS use decimal
    Price       decimal.Decimal `gorm:"type:decimal(10,2);not null"`

    // Float - only for scientific data
    Temperature float64 `gorm:"type:real"`            // REAL
    Precision   float64 `gorm:"type:double precision"` // DOUBLE PRECISION
}

Date/Time Types

 (
    
    
)

 Event  {
    gorm.Model 

    
    EventDate   time.Time 

    
    StartTime   time.Time 

    
    ScheduledAt time.Time 

    
    CompletedAt *time.Time 
}

Deep Dive: Indexing in GORM & PostgreSQL

Defining Indexes in GORM

type User struct {
    gorm.Model

    // Single column index
    Email    string `gorm:"index;not null"`

    // Unique index
    Username string `gorm:"uniqueIndex"`

    // Composite index
    FirstName string `gorm:"index:idx_name"`
    LastName  string `gorm:"index:idx_name"`

    // Index with options
    Phone    string `gorm:"index:,unique,sort:desc"`

    // Foreign key (auto-indexed by GORM)
    TeamID   uint `gorm:"index"`
}

// Custom index creation
db.Exec("CREATE INDEX CONCURRENTLY idx_users_email_lower ON users(LOWER(email))")

PostgreSQL Index Types

// B-Tree (default) - ranges, equality
type Product struct {
    Price decimal.Decimal `gorm:"index;type:decimal(10,2)"`
}

// GIN index for JSONB
type Document struct {
    Data json.RawMessage `gorm:"type:jsonb;index:,type:gin"`
}

// GiST for full-text search
db.Exec("CREATE INDEX idx_search ON products USING GiST(to_tsvector('english', name || ' ' || description))")

// Partial index
db.Exec("CREATE INDEX idx_active_users ON users(email) WHERE deleted_at IS NULL")

Query Optimization with GORM

// Use index hints
var users []User

// Efficient: uses index
db.Where(, ).Find(&users)


db.Where(, , ).Find(&users)


db.Where(, ).Find(&users)


db.Exec()

Deep Dive: Constraints in GORM

Primary Keys

// Auto-increment (default)
type User struct {
    ID uint `gorm:"primaryKey"` // BIGSERIAL in PostgreSQL
}

// UUID primary key
import "github.com/google/uuid"

type Document struct {
    ID uuid.UUID `gorm:"type:uuid;default:gen_random_uuid();primaryKey"`
}

// Composite primary key
type Enrollment struct {
    StudentID uint `gorm:"primaryKey"`
    CourseID  uint `gorm:"primaryKey"`
    Grade     string
}

Foreign Key Constraints

// Basic foreign key
type Order struct {
    gorm.Model
    UserID uint `gorm:"not null"`
    User   User `gorm:"foreignKey:UserID"`
}

// With ON DELETE behavior
type OrderItem struct {
    gorm.Model
    OrderID uint  `gorm:"not null"`
    Order   Order `gorm:"constraint:OnDelete:CASCADE,OnUpdate:CASCADE"`
}

// Optional relationship (nullable FK)
type Post struct {
    gorm.Model
    AuthorID *uint   // Nullable
    Author   *User   `gorm:"constraint:OnDelete:SET NULL"`
}

Unique Constraints

type Account struct {
    gorm.Model

    
    Email  

    
    Domain    
    Username  
}

Deep Dive: GORM Relationship Patterns

One-to-Many

// Parent model
type Customer struct {
    gorm.Model
    Name   string
    Orders []Order `gorm:"foreignKey:CustomerID"`
}

// Child model
type Order struct {
    gorm.Model
    CustomerID uint
    Customer   Customer `gorm:"constraint:OnDelete:CASCADE"`
    Items      []OrderItem
}

// Usage with preloading
var customer Customer
db.Preload("Orders.Items").First(&customer, 1)

Many-to-Many

// Automatic junction table
type Student struct {
    gorm.Model
    Name    string
    Courses []Course `gorm:"many2many:enrollments;"`
}

type Course struct {
    gorm.Model
    Name     string
    Students []Student `gorm:"many2many:enrollments;"`
}

// Custom junction table with extra fields
type Enrollment struct {
    StudentID   uint      `gorm:"primaryKey"`
    CourseID    uint      `gorm:"primaryKey"`
    EnrolledAt  time.Time
    Grade       string
    CompletedAt *time.Time
}

// Override default junction table
func (Enrollment) TableName() string {
    return "student_courses"
}

Self-Referencing

type Category  {
    gorm.Model
    Name       
    ParentID   *
    Parent     *Category  
    Children   []Category 
}


 rootCategories []Category
db.Where().Preload().Find(&rootCategories)

Deep Dive: GORM Hooks & Advanced Features

GORM Hooks

type User struct {
    gorm.Model
    Email        string
    PasswordHash string
    LastLoginAt  *time.Time
}

// BeforeCreate - runs before INSERT
func (u *User) BeforeCreate(tx *gorm.DB) error {
    u.Email = strings.ToLower(strings.TrimSpace(u.Email))
    return nil
}

// AfterCreate - runs after INSERT
func (u *User) AfterCreate(tx *gorm.DB) error {
    // Send welcome email
    go sendWelcomeEmail(u.Email)
    return nil
}

// BeforeUpdate - runs before UPDATE
func (u *User) BeforeUpdate(tx *gorm.DB) error {
    if tx.Statement.Changed("Email") {
        u.Email = strings.ToLower(strings.TrimSpace(u.Email))
    }
    return nil
}

// BeforeSave - runs before both CREATE and UPDATE
func (u *User) BeforeSave(tx *gorm.DB) error {
    // Validation logic
    if u.Email == "" {
        return errors.New("email cannot be empty")
    }
    return nil
}

Scopes for Reusable Queries

// Define reusable query scopes
  *gorm.DB {
     db.Where(, )
}

 *gorm.DB {
     db.Order()
}

  *gorm.DB {
      *gorm.DB {
         db.Where(, start, end)
    }
}


 users []User
db.Scopes(Active, OrderByCreated).Find(&users)
db.Scopes(WithinDateRange(startDate, endDate)).Find(&orders)

Deep Dive: Migrations with GORM & golang-migrate

GORM AutoMigrate (Development)

// Simple auto-migration for development
func InitDB() (*gorm.DB, error) {
    db, err := gorm.Open(postgres.Open(dsn), &gorm.Config{})
    if err != nil {
        return nil, err
    }

    // AutoMigrate creates/updates tables
    err = db.AutoMigrate(
        &User{},
        &Order{},
        &Product{},
        &OrderItem{},
    )
    return db, err
}

// Custom migration with raw SQL
func CustomMigration(db *gorm.DB) error {
    return db.Exec(`
        CREATE INDEX CONCURRENTLY IF NOT EXISTS idx_orders_created
        ON orders(created_at DESC) WHERE deleted_at IS NULL
    `).Error
}

Production Migrations with golang-migrate

# Install golang-migrate
go install -tags 'postgres' github.com/golang-migrate/migrate/v4/cmd/migrate@latest

# Create migration files
migrate create -ext sql -dir migrations -seq create_users_table

-- migrations/000001_create_users_table.up.sql
CREATE TABLE IF NOT EXISTS users (
    id BIGSERIAL PRIMARY KEY,
    email VARCHAR(255) UNIQUE NOT NULL,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    deleted_at TIMESTAMPTZ
);

 INDEX idx_users_email  users(email);
 INDEX idx_users_deleted_at  users(deleted_at);


  IF  users;

Deep Dive: Performance Optimization with GORM & PostgreSQL

Query Analysis in PostgreSQL

// Enable query logging in GORM
db = db.Debug()

// Analyze query performance
var explain []map[string]interface{}
db.Raw("EXPLAIN (ANALYZE, BUFFERS) SELECT * FROM users WHERE email = ?", email).Scan(&explain)

// Check slow queries
db.Raw(`
    SELECT query, calls, mean_exec_time, max_exec_time
    FROM pg_stat_statements
    WHERE mean_exec_time > 100
    ORDER BY mean_exec_time DESC
    LIMIT 10
`).Scan(&slowQueries)

Solving N+1 Query Problem

// BAD: N+1 queries
var users []User
db.Find(&users)
for _, user := range users {
    var orders []Order
    db.Where("user_id = ?", user.ID).Find(&orders)
}

// GOOD: Eager loading with Preload
var users []User
db.Preload("Orders").Find(&users)

// BETTER: Selective preloading
db.Preload("Orders", "status = ?", "pending").
   Preload("Orders.Items").
   Find(&users)

// BEST: Joins for read-only data
var results []struct {
    UserName  string
    OrderID   uint
    Total     decimal.Decimal
}
db.Table("users").
    Select("users.name as user_name, orders.id as order_id, orders.total").
    Joins("LEFT JOIN orders ON orders.user_id = users.id").
    Scan(&results)

Connection Pool Optimization

sqlDB, err := db.DB()

// SetMaxIdleConns sets the maximum number of connections in the idle pool
sqlDB.SetMaxIdleConns(10)


sqlDB.SetMaxOpenConns()


sqlDB.SetConnMaxLifetime(time.Hour)


sqlDB.SetConnMaxIdleTime( * time.Minute)

---

Extension Points

1. PostgreSQL Advanced Features: JSONB operations, full-text search, PostGIS for geospatial
2. Advanced Patterns: Time-series with TimescaleDB, event sourcing, CQRS, multi-tenancy with RLS
3. GORM Plugins: Soft delete, optimistic locking, sharding, tracing
4. Performance Monitoring: pg_stat_statements, pgBadger, GORM prometheus metrics
5. Testing Strategies: Testcontainers for Go, fixture management, transaction rollback tests

---

Source: gocanto/skills — distributed by TomeVault.