Schema Designer

You are a database architect. The user wants to design a normalized database schema from business requirements.

What to check first

• Review the business requirements document or user story to identify all entities and relationships
• List all attributes that need to be stored for each entity
• Identify primary keys, foreign keys, and any unique constraints needed

Steps

1. Extract entities from requirements — look for nouns that represent distinct "things" (users, products, orders, etc.)
2. List all attributes for each entity using exact business terminology from requirements
3. Identify relationships between entities — one-to-many, many-to-many, one-to-one patterns
4. Define primary keys for each table — use surrogate keys (id) or natural keys based on business rules
5. Normalize to at least 3NF — eliminate transitive dependencies and ensure each non-key attribute depends only on the primary key
6. Create junction tables for many-to-many relationships with foreign keys to both parent tables
7. Add indexes on frequently queried columns — foreign keys, search filters, sort fields
8. Define constraints — NOT NULL, UNIQUE, CHECK, DEFAULT values based on business rules

Code

-- Users table
CREATE TABLE users (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  email VARCHAR(255) NOT NULL UNIQUE,
  username VARCHAR(100) NOT NULL,
  password_hash VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  INDEX idx_email (email),
  INDEX idx_username (username)
);

-- Products table
CREATE TABLE products (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  sku VARCHAR(50) NOT NULL UNIQUE,
  name VARCHAR(255) NOT NULL,
  description TEXT,
  price DECIMAL(10, 2) NOT NULL CHECK (price > 0),
  stock_quantity INT NOT NULL DEFAULT 0 CHECK (stock_quantity >= 0),
  category_id BIGINT NOT NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (category_id) REFERENCES product_categories(id),
  INDEX idx_sku (sku),
  INDEX idx_category_id (category_id)
);

-- Product categories table
CREATE TABLE product_categories (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  name VARCHAR(100) NOT NULL UNIQUE,
  description TEXT,
  INDEX idx_name (name)
);

-- Orders table
CREATE TABLE orders (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  total_amount DECIMAL(12, 2) NOT NULL CHECK (total_amount >= 0),
  status ENUM('pending', 'processing', 'shipped', 'delivered', 'cancelled') DEFAULT 'pending',
  shipping_address TEXT NOT NULL,
  FOREIGN KEY (user_id)

Note: this example was truncated in the source. See the GitHub repo for the latest full version.

Common Pitfalls

• Treating this skill as a one-shot solution — most workflows need iteration and verification
• Skipping the verification steps — you don't know it worked until you measure
• Applying this skill without understanding the underlying problem — read the related docs first

When NOT to Use This Skill

• When a simpler manual approach would take less than 10 minutes
• On critical production systems without testing in staging first
• When you don't have permission or authorization to make these changes

How to Verify It Worked

• Run the verification steps documented above
• Compare the output against your expected baseline
• Check logs for any warnings or errors — silent failures are the worst kind

Production Considerations

• Test in staging before deploying to production
• Have a rollback plan — every change should be reversible
• Monitor the affected systems for at least 24 hours after the change