--- name: role-database-schema-design description: | Database schema design principles and patterns. Normalization (1NF through 5NF/BCNF), strategic denormalization for read performance, multi-tenancy schema patterns (shared schema, schema-per-tenant, database-per-tenant), primary key strategies (UUID v7, ULID, KSUID, Snowflake ID, BIGSERIAL, CUID2, NanoID), soft delete patterns, temporal tables (SCD Type 1/2/3/4/6), audit trails, schema evolution (expand-contract pattern), naming conventions, data type best practices, and anti-patterns to avoid (EAV, polymorphic associations, god tables). Use when designing new schemas, reviewing existing schema design, planning schema migrations, or choosing PK strategies. allowed-tools: Read, Grep, Glob, Bash --- # Schema Design ## Reference Files Load from `references/` based on what's needed: ### references/normalization-multitenancy.md Normal forms (1NF–4NF) with violation examples, denormalization patterns (materialized views, trigger counters). Multi-tenancy: shared schema + RLS, schema-per-tenant, database-per-tenant decision guide. Primary key strategy comparison (BIGSERIAL, UUID v7, ULID, Snowflake ID). Load when: designing new schemas, choosing PK strategy, implementing multi-tenancy. ### references/temporal-audit-evolution.md Soft delete patterns (timestamp, archive table, partial indexes). SCD Type 2 temporal tables in PostgreSQL and SQL Server. Generic audit trigger with JSONB old/new values. Expand-contract pattern with safe migration operations table and zero-downtime column rename. Load when: implementing audit trails, versioned data, or zero-downtime schema changes. ### references/datatypes-antipatterns.md Data type decisions (money, timestamps, IP, email, JSON, enums, country, phone). PostgreSQL domain types and range exclusion constraints. Naming conventions table (tables, columns, PKs, FKs, indexes, booleans, timestamps). Anti-patterns: EAV, polymorphic associations, god tables, over-indexing, missing FKs. Load when: choosing column types, reviewing naming, or identifying schema problems. --- Source attribution: rnavarych Original project: https://github.com/rnavarych/alpha-engineer/blob/c5b74102c7fd584af3d344ba7c331a7bbec3c729/plugins/roles/role-database/skills/schema-design/SKILL.md License: MIT License information: https://opensource.org/license/mit Original upstream instructions; downloaded from Dave AI. ## Upstream license notice MIT License Copyright (c) 2026 Raman Navarych Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions: The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software. THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE. # Dave AI resource metadata Creator / publisher: rnavarych Original project: https://github.com/rnavarych/alpha-engineer/blob/c5b74102c7fd584af3d344ba7c331a7bbec3c729/plugins/roles/role-database/skills/schema-design/SKILL.md Attribution basis: Source repository record Fields of work: Data & Analytics, Development & Engineering Roles: Data Analyst, Data Engineer, Database Administrator Specialties: Database Tools: Read Country applicability: Country not specified Classification basis: Source catalog labels Instruction SHA-256: c4ab36608f08730c40c1e2807fb4aaf954470bee523286b61d92f2e634692337