Database Schema Design

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:

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

Entity-Relationship Patterns

Entity-Relationship Patterns

PostgreSQL - One-to-Many:

-- One user has many orders
CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email VARCHAR(255) UNIQUE NOT NULL,
  name VARCHAR(255) NOT NULL,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE TABLE orders (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL,
  order_date TIMESTAMP DEFAULT NOW(),
  total DECIMAL(10,2),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
  INDEX idx_user_id (user_id)
);

PostgreSQL - One-to-One:

-- One user has one profile
CREATE TABLE user_profiles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID UNIQUE NOT NULL,
  bio TEXT,
  avatar_url VARCHAR(500),
  FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

PostgreSQL - Many-to-Many:

-- Students and courses (many-to-many)
CREATE TABLE students (
  id UUID PRIMARY KEY,
  name VARCHAR(255)
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  title VARCHAR(255)
);

-- Junction table
CREATE TABLE course_enrollments (
  id UUID PRIMARY KEY,
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  enrolled_at TIMESTAMP DEFAULT NOW(),
  FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
  FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE,
  UNIQUE(student_id, course_id)
);

First Normal Form (1NF)

First Normal Form (1NF)

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
);

Second Normal Form (2NF)

Second Normal Form (2NF)

PostgreSQL - Remove Partial Dependencies:

-- NOT 2NF: non-key attribute depends on part of composite key
CREATE TABLE enrollment_bad (
  student_id UUID,
  course_id UUID,
  professor_name VARCHAR(255),  -- depends on course_id only
  PRIMARY KEY (student_id, course_id)
);

-- 2NF: separate tables
CREATE TABLE enrollments (
  id UUID PRIMARY KEY,
  student_id UUID NOT NULL,
  course_id UUID NOT NULL,
  FOREIGN KEY (student_id) REFERENCES students(id),
  FOREIGN KEY (course_id) REFERENCES courses(id),
  UNIQUE(student_id, course_id)
);

CREATE TABLE courses (
  id UUID PRIMARY KEY,
  name VARCHAR(255),
  professor_id UUID NOT NULL,
  FOREIGN KEY (professor_id) REFERENCES professors(id)
);

Third Normal Form (3NF)

Third Normal Form (3NF)

PostgreSQL - Remove Transitive Dependencies:

-- NOT 3NF: transitive dependency (customer_city depends on customer_state)
CREATE TABLE orders_bad (
  id UUID PRIMARY KEY,
  customer_city VARCHAR(100),
  customer_state VARCHAR(50),
  state_tax_rate DECIMAL(5,3)  -- depends on customer_state
);

-- 3NF: separate tables
CREATE TABLE states (
  id UUID PRIMARY KEY,
  code VARCHAR(2) UNIQUE,
  name VARCHAR(100),
  tax_rate DECIMAL(5,3)
);

CREATE TABLE orders (
  id UUID PRIMARY KEY,
  customer_city VARCHAR(100),
  state_id UUID NOT NULL,
  FOREIGN KEY (state_id) REFERENCES states(id)
);