database-schema-design

设计关系型数据库的表结构，创建数据表，并管理迁移流程，以支持可扩展的应用程序。

SKILL.md

--- frontmatter

name: database-schema-design
description: Design relational database schemas, create tables, and manage migrations for scalable applications.

Database Schema Design

Instructions

•
Schema planning
- •Identify core entities
- •Define relationships (1–1, 1–many, many–many)
- •Normalize data where appropriate
•
Table creation
- •Use clear, consistent naming conventions
- •Define primary and foreign keys
- •Apply proper data types and constraints
•
Migrations
- •Create reversible migrations
- •Version-control schema changes
- •Avoid breaking changes in production

Best Practices

•Use snake_case or camelCase consistently
•Index frequently queried columns
•Enforce data integrity with constraints
•Design with scalability in mind
•Document schema decisions

Example Structure

sql

-- users table
CREATE TABLE users (
  id SERIAL PRIMARY KEY,
  email VARCHAR(255) UNIQUE NOT NULL,
  password_hash TEXT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- posts table
CREATE TABLE posts (
  id SERIAL PRIMARY KEY,
  user_id INTEGER REFERENCES users(id),
  title VARCHAR(255) NOT NULL,
  content TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);