A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.
Instrucciones de origen · Vista previa de solo lectura
name
database-designer
description
A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.
zh_description
用于数据库、设计，支持开发、调试、评审和交付。
version
1.0.0
author
seaworld008
source
in-house
source_url
tags
["database", "designer", "development"]
created_at
2026-03-04
updated_at
2026-03-20
quality
5
complexity
intermediate
Database Designer - POWERFUL Tier Skill
Overview
A comprehensive database design skill that provides expert-level analysis, optimization, and migration capabilities for modern database systems. This skill combines theoretical principles with practical tools to help architects and developers create scalable, performant, and maintainable database schemas.
Core Competencies
Schema Design & Analysis
Normalization Analysis: Automated detection of normalization levels (1NF through BCNF)
Denormalization Strategy: Smart recommendations for performance optimization
Data Type Optimization: Identification of inappropriate types and size issues
Constraint Analysis: Missing foreign keys, unique constraints, and null checks
Naming Convention Validation: Consistent table and column naming patterns
ERD Generation: Automatic Mermaid diagram creation from DDL
Index Optimization
Index Gap Analysis: Identification of missing indexes on foreign keys and query patterns
Composite Index Strategy: Optimal column ordering for multi-column indexes
Index Redundancy Detection: Elimination of overlapping and unused indexes
Performance Impact Modeling: Selectivity estimation and query cost analysis
Index Type Selection: B-tree, hash, partial, covering, and specialized indexes
Default Choice: Best for range queries, sorting, and equality matches
Column Order: Most selective columns first for composite indexes
Prefix Matching: Supports leading column subset queries
Maintenance Cost: Balanced tree structure with logarithmic operations
Hash Indexes
Equality Queries: Optimal for exact match lookups
Memory Efficiency: Constant-time access for single-value queries
Range Limitations: Cannot support range or partial matches
Use Cases: Primary keys, unique constraints, cache keys
Composite Indexes
-- Query pattern determines optimal column order-- Query: WHERE status = 'active' AND created_date > '2023-01-01' ORDER BY priority DESCCREATE INDEX idx_task_status_date_priority
ON tasks (status, created_date, priority DESC);
-- Query: WHERE user_id = 123 AND category IN ('A', 'B') AND date_field BETWEEN '...' AND '...'CREATE INDEX idx_user_category_date
ON user_activities (user_id, category, date_field);
Covering Indexes
-- Include additional columns to avoid table lookupsCREATE INDEX idx_user_email_covering
ON users (email)
INCLUDE (first_name, last_name, status);
-- Query can be satisfied entirely from the index-- SELECT first_name, last_name, status FROM users WHERE email = 'user@example.com';
Partial Indexes
-- Index only relevant subset of dataCREATE INDEX idx_active_users_email
ON users (email)
WHERE status ='active';
-- Index for recent orders onlyCREATE INDEX idx_recent_orders_customer
ON orders (customer_id, created_at)
WHERE created_at >CURRENT_DATE-INTERVAL'30 days';
Query Analysis & Optimization
Query Patterns Recognition
Equality Filters: Single-column B-tree indexes
Range Queries: B-tree with proper column ordering
Text Search: Full-text indexes or trigram indexes
Join Operations: Foreign key indexes on both sides
Sorting Requirements: Indexes matching ORDER BY clauses
Index Selection Algorithm
1. Identify WHERE clause columns
2. Determine most selective columns first
3. Consider JOIN conditions
4. Include ORDER BY columns if possible
5. Evaluate covering index opportunities
6. Check for existing overlapping indexes
-- Normalized dimension tablesCREATE TABLE products (
id INTPRIMARY KEY,
name VARCHAR(200),
category_id INTREFERENCES product_categories(id),
brand_id INTREFERENCES brands(id)
);
CREATE TABLE product_categories (
id INTPRIMARY KEY,
name VARCHAR(100),
parent_category_id INTREFERENCES product_categories(id)
);
Document Model (JSON Storage)
-- Flexible document storage with indexingCREATE TABLE documents (
id UUID PRIMARY KEY,
document_type VARCHAR(50),
data JSONB,
created_at TIMESTAMPDEFAULT NOW(),
updated_at TIMESTAMPDEFAULT NOW()
);
-- Index on JSON propertiesCREATE INDEX idx_documents_user_id
ON documents USING GIN ((data->>'user_id'));
CREATE INDEX idx_documents_status
ON documents ((data->>'status'))
WHERE document_type ='order';
Graph Data Patterns
-- Adjacency list for hierarchical dataCREATE TABLE categories (
id INTPRIMARY KEY,
name VARCHAR(100),
parent_id INTREFERENCES categories(id),
level INT,
path VARCHAR(500) -- Materialized path: "/1/5/12/"
);
-- Many-to-many relationshipsCREATE TABLE relationships (
id UUID PRIMARY KEY,
from_entity_id UUID,
to_entity_id UUID,
relationship_type VARCHAR(50),
created_at TIMESTAMP,
INDEX (from_entity_id, relationship_type),
INDEX (to_entity_id, relationship_type)
);
Migration Strategies
Zero-Downtime Migration (Expand-Contract Pattern)
Phase 1: Expand
-- Add new column without constraintsALTER TABLE users ADDCOLUMN new_email VARCHAR(255);
-- Backfill data in batchesUPDATE users SET new_email = email WHERE id BETWEEN1AND1000;
-- Continue in batches...-- Add constraints after backfillALTER TABLE users ADD CONSTRAINT users_new_email_unique UNIQUE (new_email);
ALTER TABLE users ALTERCOLUMN new_email SETNOT NULL;
Phase 2: Contract
-- Update application to use new column-- Deploy application changes-- Verify new column is being used-- Remove old columnALTER TABLE users DROPCOLUMN email;
-- Rename new columnALTER TABLE users RENAME COLUMN new_email TO email;
Data Type Changes
-- Safe string to integer conversionALTER TABLE products ADDCOLUMN sku_number INTEGER;
UPDATE products SET sku_number =CAST(sku ASINTEGER) WHERE sku ~'^[0-9]+$';
-- Validate conversion success before dropping old column
Partitioning Strategies
Horizontal Partitioning (Sharding)
-- Range partitioning by dateCREATE TABLE sales_2023 PARTITIONOF sales
FORVALUESFROM ('2023-01-01') TO ('2024-01-01');
CREATE TABLE sales_2024 PARTITIONOF sales
FORVALUESFROM ('2024-01-01') TO ('2025-01-01');
-- Hash partitioning by user_idCREATE TABLE user_data_0 PARTITIONOF user_data
FORVALUESWITH (MODULUS 4, REMAINDER 0);
CREATE TABLE user_data_1 PARTITIONOF user_data
FORVALUESWITH (MODULUS 4, REMAINDER 1);
Vertical Partitioning
-- Separate frequently accessed columnsCREATE TABLE users_core (
id INTPRIMARY KEY,
email VARCHAR(255),
status VARCHAR(20),
created_at TIMESTAMP
);
-- Less frequently accessed profile dataCREATE TABLE users_profile (
user_id INTPRIMARY KEYREFERENCES users_core(id),
bio TEXT,
preferences JSONB,
last_login TIMESTAMP
);
Connection Management
Connection Pooling
Pool Size: CPU cores × 2 + effective spindle count
Connection Lifetime: Rotate connections to prevent resource leaks
Timeout Settings: Connection, idle, and query timeouts
Health Checks: Regular connection validation
Read Replicas Strategy
-- Write queries to primaryINSERT INTO users (email, name) VALUES ('user@example.com', 'John Doe');
-- Read queries to replicas (with appropriate read preference)SELECT*FROM users WHERE status ='active'; -- Route to read replica-- Consistent reads when requiredSELECT*FROM users WHERE id = LAST_INSERT_ID(); -- Route to primary
Caching Layers
Cache-Aside Pattern
defget_user(user_id):
# Try cache first
user = cache.get(f"user:{user_id}")
if user isNone:
# Cache miss - query database
user = db.query("SELECT * FROM users WHERE id = %s", user_id)
# Store in cache
cache.set(f"user:{user_id}", user, ttl=3600)
return user
Write-Through Cache
Consistency: Always keep cache and database in sync
Write Latency: Higher due to dual writes
Data Safety: No data loss on cache failures
Cache Invalidation Strategies
TTL-Based: Time-based expiration
Event-Driven: Invalidate on data changes
Version-Based: Use version numbers for consistency
Principle of least privilege: Grant minimal necessary permissions
Encrypt sensitive data: At rest and in transit
Audit access patterns: Monitor and log database access
Validate inputs: Prevent SQL injection attacks
Regular security updates: Keep database software current
Conclusion
Effective database design requires balancing multiple competing concerns: performance, scalability, maintainability, and business requirements. This skill provides the tools and knowledge to make informed decisions throughout the database lifecycle, from initial schema design through production optimization and evolution.
The included tools automate common analysis and optimization tasks, while the comprehensive guides provide the theoretical foundation for making sound architectural decisions. Whether building a new system or optimizing an existing one, these resources provide expert-level guidance for creating robust, scalable database solutions.
