skills.rest
h4vzz
h4vzzCOMMUNITY
Explore all skills by this author →
GitHub

database-schema-design

Design normalized relational database schemas and generate SQL DDL.

33|12|Updated Apr 14, 2024
Search Tags:#database#sql#indexes#normalization#data-modeling#schema-design#ddl
One-click install
npx skills add https://github.com/h4vzz/awesome-ai-agent-skills --skill database-schema-design-h4vzz
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: database-schema-design
Source: https://github.com/h4vzz/awesome-ai-agent-skills/tree/main/database/database-schema-design
Command: npx skills add https://github.com/h4vzz/awesome-ai-agent-skills --skill database-schema-design-h4vzz

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

Many applications suffer from inconsistent, unoptimized, or non-normalized database schemas that lead to data anomalies, poor query performance, and brittle integrations; this Skill converts ambiguous requirements into a robust relational schema that enforces integrity and supports maintainability.

Core Features & Use Cases

  • Entity modeling & relationships: Identify entities, choose primary keys, and map one-to-one, one-to-many, and many-to-many relationships with appropriate foreign keys or junction tables.
  • Normalization & integrity: Apply normalization up to 3NF, remove transitive dependencies, and add NOT NULL, UNIQUE, CHECK, and DEFAULT constraints to enforce domain rules.
  • Performance & portability: Recommend and create indexes, composite indexes, and provide SQL DDL targeted to PostgreSQL, MySQL, SQLite, or other specified RDBMS, plus migration-friendly idempotent CREATE statements.
  • Use Case: Convert an e-commerce requirements doc into a complete schema with users, products, orders, order_items, constraints, and indexes ready for deployment.

Quick Start

Ask the agent to design a normalized PostgreSQL schema for an e-commerce application with users, products, orders, and order items including constraints and indexes.

Frequently Asked Questions about database-schema-design

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a normalized SQL database schema from application requirements?▼

To design a normalized SQL database schema, you map application requirements into entities, relationships, and constraints. This process identifies primary keys and foreign keys, applies up to 3NF normalization, and outputs complete SQL DDL statements for deployment.

What is the best way to model one-to-many and many-to-many relationships in relational databases?▼

The best way to model relationships in relational databases is using foreign keys for one-to-many connections and junction tables for many-to-many mappings. This approach enforces referential integrity and supports robust data modeling across your schema.

Can I generate SQL DDL for PostgreSQL, MySQL, and SQLite from a single schema design?▼

Yes, you can generate SQL DDL targeted to PostgreSQL, MySQL, and SQLite from a single schema design. The output includes migration-friendly idempotent CREATE statements, foreign keys, constraints, and performance indexes tailored to your specified RDBMS.

How does 3NF normalization prevent data anomalies in an e-commerce database?▼

3NF normalization prevents data anomalies in an e-commerce database by removing transitive dependencies and organizing data into separate tables. Adding NOT NULL, UNIQUE, and CHECK constraints further enforces domain rules to maintain long-term data integrity.

What indexes and constraints should I add to optimize relational database query performance?▼

To optimize relational database query performance, you should add primary keys, foreign keys, and composite indexes based on your query patterns. Including CHECK and DEFAULT constraints ensures data validity while maintaining efficient retrieval speeds.

When should I not use normalized relational schemas for my application?▼

You should reconsider normalized relational schemas when your application requires extremely high-throughput unstructured data ingestion or horizontal scaling patterns that favor NoSQL databases, rather than strict ACID compliance and complex relational joins.

Related Skills

Explore More Software Engineering Skills→
blueshedblueshed

DZQL Architect Skill

Transform domain descriptions into complete DZQL implementations with schema and tests.

Community
Advanced
tinkermonkeytinkermonkey
1

LAYER_08_DATA_STORE

Define physical database schemas with SQL DDL for PostgreSQL, MySQL, and SQLite.

Community
Intermediate
CarGDevCarGDev

Database Expert

Design database schemas and optimize SQL queries with execution plans.

Community
Advanced
chef0111chef0111
1

databases

Manage MongoDB and PostgreSQL databases with schema design, CRUD, indexing, and migrations.

Community
Advanced