Database Schema Design

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

Normalization Strategy

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)

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)

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

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

Constraint Strategy

PostgreSQL - Data Integrity:

-- NOT NULL constraints
CREATE TABLE products (
  id UUID PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  sku VARCHAR(100) NOT NULL,
  price DECIMAL(10,2) NOT NULL
);

-- UNIQUE constraints
ALTER TABLE products
ADD CONSTRAINT unique_sku UNIQUE(sku);

-- CHECK constraints
ALTER TABLE products
ADD CONSTRAINT price_positive CHECK (price > 0);

ALTER TABLE orders
ADD CONSTRAINT valid_status
CHECK (status IN ('pending', 'processing', 'completed', 'cancelled'));

-- DEFAULT values
CREATE TABLE audit_logs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  table_name VARCHAR(100) NOT NULL,
  operation VARCHAR(10) NOT NULL,
  user_id UUID,
  timestamp TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Data Type Selection

PostgreSQL - Optimal Data Types:

CREATE TABLE users (
  -- Identifiers
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),

  -- Text fields
  email VARCHAR(255),          -- Fixed length for emails
  name TEXT,                   -- Unbounded text
  bio TEXT,

  -- Numeric data
  age SMALLINT,                -- 0-32767
  balance DECIMAL(15,2),       -- Financial data (precise)
  rating NUMERIC(3,1),         -- Range 0.0-9.9

  -- Boolean
  is_active BOOLEAN DEFAULT true,
  email_verified BOOLEAN,

  -- Dates and Times
  birth_date DATE,
  last_login TIMESTAMP WITH TIME ZONE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  -- JSON/Binary
  metadata JSONB,
  profile_image BYTEA,

  -- Arrays (PostgreSQL specific)
  tags TEXT[] DEFAULT ARRAY[]::TEXT[]
);

MySQL - Compatible Data Types:

CREATE TABLE users (
  id CHAR(36) PRIMARY KEY,       -- UUID as CHAR

  email VARCHAR(255),
  name VARCHAR(255),

  age TINYINT UNSIGNED,
  balance DECIMAL(15,2),

  is_active BOOLEAN DEFAULT true,

  birth_date DATE,
  last_login TIMESTAMP,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  metadata JSON,

  KEY idx_email (email)
);

Schema Evolution

PostgreSQL - Backward Compatible Changes:

-- Add column with default
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- Add column for new feature
ALTER TABLE orders
ADD COLUMN notes TEXT DEFAULT '';

-- Add constraint on new column
ALTER TABLE orders
ADD CONSTRAINT check_notes CHECK (LENGTH(notes) <= 500);

-- Deprecate column safely
ALTER TABLE users RENAME COLUMN old_field TO old_field_deprecated;

MySQL - Schema Changes:

-- Add column with default
ALTER TABLE users ADD COLUMN phone VARCHAR(20) DEFAULT '';

-- Add multiple columns
ALTER TABLE orders
ADD COLUMN notes TEXT DEFAULT '',
ADD COLUMN internal_status VARCHAR(50);

-- Modify column
ALTER TABLE users MODIFY COLUMN bio TEXT;

Performance Considerations

PostgreSQL - Partitioning Large Tables:

-- Partition by date range for time-series data
CREATE TABLE events (
  id UUID PRIMARY KEY,
  user_id UUID NOT NULL,
  event_type VARCHAR(100),
  created_at TIMESTAMP NOT NULL
) PARTITION BY RANGE (DATE_TRUNC('month', created_at));

CREATE TABLE events_2024_01 PARTITION OF events
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

Schema Design Checklist

Identify entities and relationships
Apply normalization rules (1NF, 2NF, 3NF)
Define primary keys for all tables
Create foreign keys for relationships
Add constraints for data integrity
Select appropriate data types
Plan indexes for common queries
Design for scalability (denormalization if needed)
Document table purposes and relationships
Plan for schema evolution

Common Pitfalls

❌ Don't skip normalization for convenience ❌ Don't use VARCHAR(MAX) for all text fields ❌ Don't forget to add foreign key constraints ❌ Don't use natural keys as primary keys ❌ Don't store calculated values in base tables

✅ DO use UUIDs or sequences for primary keys ✅ DO normalize data appropriately ✅ DO add CHECK constraints for data validity ✅ DO create indexes on foreign keys ✅ DO use TIMESTAMP for audit trails

database-schema-design