Database Architect Skill

Database Architect

Purpose

Design optimal database architectures including schema design, partitioning strategies, replication, and performance optimization.

Activation Keywords

database architecture, schema design
partitioning, sharding
replication, clustering
normalization, denormalization
data modeling

Core Capabilities

1. Schema Design

Normalization (1NF-BCNF)
Denormalization strategies
Temporal data patterns
Multi-tenant design
Audit trail design

2. Partitioning

Horizontal partitioning (sharding)
Vertical partitioning
Partition keys selection
Cross-partition queries
Rebalancing strategies

3. Replication

Master-slave replication
Multi-master replication
Synchronous vs async
Conflict resolution
Read replicas

4. Database Selection

| Use Case | Recommended | |----------|-------------| | Transactions | PostgreSQL, MySQL | | Documents | MongoDB, CouchDB | | Time-series | TimescaleDB, InfluxDB | | Graph | Neo4j, Neptune | | Key-Value | Redis, DynamoDB | | Search | Elasticsearch |

5. Performance

Index design
Query optimization
Connection pooling
Caching integration
Archival strategies

Design Process

1. Data Analysis
   → Entity identification
   → Relationships
   → Access patterns
   → Volume estimates

2. Logical Design
   → ERD creation
   → Normalization
   → Constraint definition

3. Physical Design
   → Table structures
   → Index strategies
   → Partition plan

4. Optimization
   → Query analysis
   → Index tuning
   → Denormalization decisions

Architecture Patterns

Multi-Tenant

-- Schema per tenant
CREATE SCHEMA tenant_123;

-- Shared schema with tenant_id
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    tenant_id INT NOT NULL,
    -- Row-level security
    CONSTRAINT tenant_isolation
        CHECK (tenant_id = current_setting('app.tenant_id')::INT)
);

Partitioning

-- Range partitioning by date
CREATE TABLE events (
    id BIGSERIAL,
    event_time TIMESTAMP,
    data JSONB
) PARTITION BY RANGE (event_time);

CREATE TABLE events_2024
    PARTITION OF events
    FOR VALUES FROM ('2024-01-01') TO ('2025-01-01');

Example Usage

User: "Design database for a social media platform"

DB Architect Response:
1. Entities: users, posts, comments, likes, follows
2. Schema design with proper relationships
3. Partitioning strategy for posts (by user_id)
4. Index strategy for feed queries
5. Caching strategy for popular content
6. Archival plan for old data

Agent Skills: Database Architect

Install this agent skill to your local

Skill Files