schema-designer_skill

Overview

This skill helps design database schemas, create tables, and model data relationships across SQL and NoSQL engines. It guides schema decisions, produces DDL (CREATE/ALTER statements), and offers migration-safe advice tailored to your database (Postgres, MySQL, SQLite, ClickHouse, MongoDB, Redis, Elasticsearch, etc.). It activates when you ask to create tables, add columns, or model relationships.

How this skill works

First, it asks clarifying questions about the data, relationships, expected queries, and volume. It inspects existing schema objects when available to avoid conflicts and recommends normalization, keys, foreign keys, and indexes. Then it generates concrete DDL snippets and optional explanations for chosen types, constraints, and migration safety steps.

When to use it

• Design a new schema for an application (blog, e-commerce, analytics).
• Create tables for orders, users, products, or audit logs.
• Add or modify columns and need migration-safe steps.
• Model relationships: one-to-many, many-to-many, or polymorphic.
• Translate conceptual models into concrete DDL for a target DB engine.

Best practices

• Always define a primary key and prefer stable identifiers (serial, UUID).
• Enforce referential integrity with foreign keys where appropriate.
• Use NOT NULL for required fields and add sensible defaults for migrations.
• Index foreign keys and columns used in filters or joins, not everything.
• Choose appropriate column types (numbers as numeric types, timestamps with timezone when needed).
• Keep naming consistent and avoid reserved words.

Example use cases

• Generate CREATE TABLE statements for a users, orders, and order_items schema with indexes and FK constraints.
• Design a many-to-many relationship (users ↔ roles) with a join table and composite primary key.
• Add a nullable phone column or a new NOT NULL status column with a safe backfill plan.
• Create soft-delete and audit-log patterns for compliance and easy recovery.
• Recommend data types and indexing strategy for high-volume time-series or analytics tables (ClickHouse/Elasticsearch).