/design-database-schema

Design Database Schema

Design optimized database schemas

Instructions

1. **Requirements Analysis and Data Modeling**

• Analyze business requirements and data relationships
• Identify entities, attributes, and relationships
• Define data types, constraints, and validation rules
• Plan for scalability and future requirements
• Consider data access patterns and query requirements

2. **Entity Relationship Design**

• Create comprehensive entity relationship diagrams:

**User Management Schema:**

   -- Users table with proper indexing and constraints
   CREATE TABLE users (
     id BIGSERIAL PRIMARY KEY,
     email VARCHAR(255) UNIQUE NOT NULL,
     username VARCHAR(50) UNIQUE NOT NULL,
     password_hash VARCHAR(255) NOT NULL,
     first_name VARCHAR(100) NOT NULL,
     last_name VARCHAR(100) NOT NULL,
     phone VARCHAR(20),
     date_of_birth DATE,
     email_verified BOOLEAN DEFAULT FALSE,
     phone_verified BOOLEAN DEFAULT FALSE,
     status user_status DEFAULT 'active',
     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     last_login_at TIMESTAMP WITH TIME ZONE,
     deleted_at TIMESTAMP WITH TIME ZONE,
     
     -- Constraints
     CONSTRAINT users_email_format CHECK (email ~* '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,}$'),
     CONSTRAINT users_username_format CHECK (username ~* '^[a-zA-Z0-9_]{3,50}$'),
     CONSTRAINT users_names_not_empty CHECK (LENGTH(TRIM(first_name)) > 0 AND LENGTH(TRIM(last_name)) > 0)
   );

   -- User status enum
   CREATE TYPE user_status AS ENUM ('active', 'inactive', 'suspended', 'pending_verification');

   -- User profiles table for extended information
   CREATE TABLE user_profiles (
     user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
     avatar_url VARCHAR(500),
     bio TEXT,
     website VARCHAR(255),
     location VARCHAR(255),
     timezone VARCHAR(50) DEFAULT 'UTC',
     language VARCHAR(10) DEFAULT 'en',
     notification_preferences JSONB DEFAULT '{}',
     privacy_settings JSONB DEFAULT '{}',
     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
   );

   -- User roles and permissions
   CREATE TABLE roles (
     id SERIAL PRIMARY KEY,
     name VARCHAR(50) UNIQUE NOT NULL,
     description TEXT,
     permissions JSONB DEFAULT '[]',
     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
   );

   CREATE TABLE user_roles (
     user_id BIGINT REFERENCES users(id) ON DELETE CASCADE,
     role_id INTEGER REFERENCES roles(id) ON DELETE CASCADE,
     assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     assigned_by BIGINT REFERENCES users(id),
     PRIMARY KEY (user_id, role_id)
   );

**E-commerce Schema Example:**

   -- Categories with hierarchical structure
   CREATE TABLE categories (
     id SERIAL PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     slug VARCHAR(255) UNIQUE NOT NULL,
     description TEXT,
     parent_id INTEGER REFERENCES categories(id),
     sort_order INTEGER DEFAULT 0,
     is_active BOOLEAN DEFAULT TRUE,
     meta_title VARCHAR(255),
     meta_description TEXT,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
   );

   -- Products table with comprehensive attributes
   CREATE TABLE products (
     id BIGSERIAL PRIMARY KEY,
     name VARCHAR(255) NOT NULL,
     slug VARCHAR(255) UNIQUE NOT NULL,
     sku VARCHAR(100) UNIQUE NOT NULL,
     description TEXT,
     short_description TEXT,
     price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
     compare_price DECIMAL(10,2) CHECK (compare_price >= price),
     cost_price DECIMAL(10,2) CHECK (cost_price >= 0),
     weight DECIMAL(8,2),
     dimensions JSONB, -- {length: x, width: y, height: z, unit: 'cm'}
     category_id INTEGER REFERENCES categories(id),
     brand_id INTEGER REFERENCES brands(id),
     vendor_id BIGINT REFERENCES vendors(id),
     status product_status DEFAULT 'draft',
     visibility product_visibility DEFAULT 'visible',
     inventory_tracking BOOLEAN DEFAULT TRUE,
     inventory_quantity INTEGER DEFAULT 0,
     low_stock_threshold INTEGER DEFAULT 5,
     allow_backorder BOOLEAN DEFAULT FALSE,
     requires_shipping BOOLEAN DEFAULT TRUE,
     is_digital BOOLEAN DEFAULT FALSE,
     tax_class VARCHAR(50) DEFAULT 'standard',
     featured BOOLEAN DEFAULT FALSE,
     tags TEXT[],
     attributes JSONB DEFAULT '{}',
     seo_title VARCHAR(255),
     seo_description TEXT,
     created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
     published_at TIMESTAMP WITH TIME ZONE,
     
     -- Full text search
     search_vector tsvector GENERATED ALWAYS AS (
       to_tsvector('english', COALESCE(name, '') || ' ' || COALESCE(description, '') || ' ' || COALESCE(sku, ''))
     ) STORED
   );

   -- Product status and visibility enums
   CREATE TYPE product_status AS ENUM ('draft', 'active', 'inactive', 'archived');
   CREATE TYPE product_visibility AS ENUM ('visible', 'hidden', 'catalog_only', 'search_only');

   -- Orders table with comprehensive tracking
   CREATE TABLE orders (
     id BIGSERIAL PRIMARY KEY,
     order_number VARCHAR(50) UNIQUE NOT NULL,
     user_id BIGINT REFERENCES users(id),
     status order_status DEFAULT 'pending',
     currency CHAR(3) DEFAULT 'USD',
     subtotal DECIMAL(10,2) NOT NULL DEFAULT 0,
     tax_total DECIMAL(10,2) NOT NULL DEFAULT 0,
     shipping_total DECIMAL(10,2) NOT NULL DEFAULT 0,
     discount_total DECIMAL(10,2) NOT NULL DEFAULT 0,
     total DECIMAL(10,2) NOT NULL DEFAULT 0,
     
     -- Billing information
     billing_first_name VARCHAR(100),
     billing_last_name VARCHAR(100),
     billing_company VARCHAR(255),
     billing_address_line_1 VARCHAR(255),
     billing_address_line_2 VAR