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

Resources

• PostgreSQL Data Types
• MySQL Data Types
• Database Normalization Guide
• Draw.io - Schema diagram tool