Database Schema Designer

Overview

Design normalized relational database schemas from business requirements, entity-relationship diagrams, or existing application code. This skill produces PostgreSQL or MySQL DDL with proper data types, constraints, indexes, and relationships following normalization principles (3NF by default) with strategic denormalization where performance requires it.

Prerequisites

• Business domain requirements or existing application models/classes to derive schema from
• psql or mysql CLI for testing schema DDL
• Target database engine and version (determines available data types and features)
• Expected data volumes and query patterns for sizing and index decisions
• Multi-tenancy requirements (shared schema, schema-per-tenant, or database-per-tenant)

Instructions

1. Identify all entities (nouns) from the business requirements. Each entity becomes a table. List every attribute (property) of each entity and classify as required or optional.

2. Define primary keys for each table. Prefer BIGSERIAL (PostgreSQL) or BIGINT AUTO_INCREMENT (MySQL) for surrogate keys. Use UUID (via gen_random_uuid()) for distributed systems or when IDs are exposed in URLs. Natural keys are acceptable when truly immutable and unique (ISO country codes, IATA airport codes).