database-schema-design

name	database-schema-design
description	Design database schemas with normalization, relationships, and constraints. Use when creating new database schemas, designing tables, or planning data models for PostgreSQL and MySQL.

Database Schema Design

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Design scalable, normalized database schemas with proper relationships, constraints, and data types. Includes normalization techniques, relationship patterns, and constraint strategies.

When to Use

• New database schema design
• Data model planning
• Table structure definition
• Relationship design (1:1, 1:N, N:N)
• Normalization analysis
• Constraint and trigger planning
• Performance optimization at schema level

Quick Start

PostgreSQL - Eliminate Repeating Groups:

-- NOT 1NF: repeating group in single column
CREATE TABLE orders_bad (
  id UUID PRIMARY KEY,
  customer_name VARCHAR(255),
  product_ids VARCHAR(255)  -- "1,2,3" - repeating group
);

-- 1NF: separate table for repeating data
CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_name VARCHAR(255),
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE order_items (
  id UUID PRIMARY KEY,
  order_id UUID NOT NULL,
  product_id UUID NOT NULL,
  quantity INTEGER NOT NULL,
  FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE
);

Reference Guides

Detailed implementations in the references/ directory:

Guide	Contents
First Normal Form (1NF)	First Normal Form (1NF)
Second Normal Form (2NF)	Second Normal Form (2NF)
Third Normal Form (3NF)	Third Normal Form (3NF)
Entity-Relationship Patterns	Entity-Relationship Patterns

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values