database-designer_skill

This skill designs scalable database schemas and multi-tenant migrations for PHP/CakePHP projects, ensuring normalized structures and maintainable migrations.

Shell

2

GitHub Stars

1

Bundled Files

2 months ago

Catalog Refreshed

4 months ago

First Indexed

Readme & install

Copy the install command, review bundled files from the catalogue, and read any extended description pulled from the listing source.

Installation

Preview and clipboard use veilstrat where the catalogue uses aiagentskills.

npx veilstrat add skill masanao-ohba/claude-manifests --skill database-designer

SKILL.md13.5 KB

Overview

This skill designs database schemas, migrations, and multi-tenant architectures tailored for PHP/CakePHP applications. It produces normalized table designs, CakePHP-compatible migration files, and migration strategies for shared or per-tenant databases. The output focuses on reliable foreign keys, indexes, soft delete and audit patterns to support maintainable applications.

How this skill works

I analyze application domain needs and produce SQL schema definitions and CakePHP migration classes following migration naming and file structure conventions. I recommend normalization levels, choose column types and constraints, and generate index and foreign key strategies. For multi-tenant setups I map patterns: shared tables with tenant_id, shared account schemas, or per-tenant databases with separate migrations. I also provide migration operations (add/modify/remove columns, indexes, foreign keys) and directory layout for multi-database deployments.

When to use it

Starting a new CakePHP application and defining the core data model
Converting undocumented legacy schemas into normalized, indexed structures
Implementing multi-tenant support (shared schema or per-tenant DB)
Planning migrations and rollback-safe schema changes
Optimizing queries and adding indexes for performance

Best practices

Use InnoDB for transactional integrity and foreign keys
Prefer utf8mb4 charset for full Unicode support
Apply appropriate normalization (1NF–3NF) but denormalize selectively for performance
Add indexes for frequent WHERE, JOIN and ORDER BY columns and consider composite order
Use del_flg for soft deletes and audit/history tables for traceability
Version all migrations with timestamped filenames and keep them in Git

Example use cases

Generate a users table and CakePHP migration including indexes, foreign keys, and del_flg
Design orders and order_items with optimized indexes, foreign keys and cascade rules
Plan multi-tenant architecture: shared tables with company_id or separate per-tenant databases and migration layout
Create an audit_log schema and migration to capture model changes with JSON payloads
Produce migration steps to add a column, change a type, and add/remove an index in CakePHP format

FAQ

Use shared tables with company_id for simple deployments and lower operational cost. Use per-tenant databases when tenant isolation, custom schemas, or scaling requirements demand stronger separation.

How do I handle schema changes safely in production?

Create timestamped migrations, apply changes in small steps, add nullable fields then backfill, add indexes without locking where possible, and test rollback paths in staging before production.