database-schema

name	database-schema
description	Design database tables, migrations, and schema architecture. Use for setting up data models and database structure.

Database Skill – Create Tables, Migrations, Schema Design

Instructions

1. Schema Design

   • Identify entities and relationships
   • Normalize data structure (3NF minimum)
   • Define primary and foreign keys
   • Plan indexes for query optimization

2. Table Creation

   • Use appropriate data types
   • Set constraints (NOT NULL, UNIQUE, CHECK)
   • Define default values
   • Add timestamps (created_at, updated_at)

3. Migrations

   • Write reversible migrations (up/down)
   • Use incremental changes
   • Include rollback strategies
   • Version control migration files

4. Relationships

   • One-to-many (foreign keys)
   • Many-to-many (junction tables)
   • One-to-one (rare, justify usage)
   • Cascade rules (ON DELETE, ON UPDATE)

Best Practices

• Naming conventions: Use snake_case for tables and columns
• Primary keys: Use auto-incrementing integers or UUIDs
• Indexing: Add indexes on foreign keys and frequently queried columns
• Data types: Choose the smallest appropriate type (INT vs BIGINT)
• Avoid: Over-normalization, premature optimization, EAV patterns
• Documentation: Comment complex constraints and business rules

Example Structure

 users (
  id BIGSERIAL ,
  email ()  ,
  username ()  ,
  password_hash () ,
  created_at   ,
  updated_at   
);

 INDEX idx_users_email  users(email);


 posts (
  id BIGSERIAL ,
  user_id  ,
  title () ,
  content TEXT,
  published_at ,
  created_at   ,
  updated_at   ,
   (user_id)  users(id)   CASCADE
);

 INDEX idx_posts_user_id  posts(user_id);
 INDEX idx_posts_published_at  posts(published_at);


 tags (
  id BIGSERIAL ,
  name ()  ,
  created_at   
);

 post_tags (
  post_id  ,
  tag_id  ,
   (post_id, tag_id),
   (post_id)  posts(id)   CASCADE,
   (tag_id)  tags(id)   CASCADE
);