PostgreSQL 기반 서비스별 스키마 설계 완벽 가이드 ⋆ Blog * JackerLab

서론

현대 웹 애플리케이션 개발에서 데이터베이스 스키마 설계는 시스템의 성능과 확장성을 결정하는 핵심 요소입니다. 이 가이드에서는 PostgreSQL을 기반으로 한 다양한 서비스 유형별 스키마 설계 방법과 실제 구현 예제를 제공합니다.

공통 설정

모든 스키마에서 사용할 공통 함수를 먼저 정의합니다.

-- updated_at 자동 업데이트 함수
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ language 'plpgsql';

1. 사용자 관리 시스템 (User Management System)

개요

모든 웹 애플리케이션의 기본이 되는 사용자 인증, 권한 관리, 프로필 관리 시스템입니다.

스키마 관계도

users (1) ←→ (1) user_profiles
users (1) ←→ (N) user_sessions
users (N) ←→ (N) user_roles ←→ (N) roles
roles (N) ←→ (N) role_permissions ←→ (N) permissions

스키마 구현

사용자 기본 정보

-- 사용자 테이블
CREATE TABLE users (
    id BIGSERIAL PRIMARY KEY,
    username VARCHAR(50) UNIQUE NOT NULL,
    email VARCHAR(100) UNIQUE NOT NULL,
    password_hash VARCHAR(255) NOT NULL,
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended')),
    email_verified BOOLEAN DEFAULT FALSE,
    last_login_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_users_username ON users(username);
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_users_status ON users(status);

사용자 프로필

-- 사용자 프로필 테이블
CREATE TABLE user_profiles (
    user_id BIGINT PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
    first_name VARCHAR(50),
    last_name VARCHAR(50),
    avatar_url VARCHAR(255),
    bio TEXT,
    phone VARCHAR(20),
    birth_date DATE,
    gender VARCHAR(10) CHECK (gender IN ('male', 'female', 'other')),
    location VARCHAR(100),
    website VARCHAR(255),
    preferences JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_profiles_location ON user_profiles(location);
CREATE INDEX idx_user_profiles_preferences ON user_profiles USING gin(preferences);

권한 관리

-- 역할 테이블
CREATE TABLE roles (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    permissions JSONB DEFAULT '[]',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 권한 테이블
CREATE TABLE permissions (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    resource VARCHAR(50) NOT NULL,
    action VARCHAR(50) NOT NULL,
    description TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_permissions_resource_action ON permissions(resource, action);

-- 사용자-역할 연결
CREATE TABLE user_roles (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    assigned_by BIGINT REFERENCES users(id),
    assigned_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE,
    PRIMARY KEY (user_id, role_id)
);

-- 역할-권한 연결
CREATE TABLE role_permissions (
    role_id INT NOT NULL REFERENCES roles(id) ON DELETE CASCADE,
    permission_id INT NOT NULL REFERENCES permissions(id) ON DELETE CASCADE,
    granted_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (role_id, permission_id)
);

세션 관리

-- 세션 테이블
CREATE TABLE user_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    ip_address INET,
    user_agent TEXT,
    device_info JSONB,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE NOT NULL,
    last_activity_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_sessions_user_id ON user_sessions(user_id);
CREATE INDEX idx_user_sessions_expires_at ON user_sessions(expires_at);

트리거 설정

CREATE TRIGGER update_users_updated_at 
    BEFORE UPDATE ON users 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_profiles_updated_at 
    BEFORE UPDATE ON user_profiles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_roles_updated_at 
    BEFORE UPDATE ON roles 
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

2. 게시판 시스템 (Board System)

개요

계층적 카테고리, 게시글, 댓글, 태그 기능을 포함한 완전한 게시판 시스템입니다.

스키마 관계도

categories (1) ←→ (N) posts
users (1) ←→ (N) posts
posts (1) ←→ (N) comments
users (1) ←→ (N) comments
posts (N) ←→ (N) post_tags ←→ (N) tags
categories (1) ←→ (N) categories (self-reference)
comments (1) ←→ (N) comments (self-reference)

스키마 구현

카테고리 관리

-- 카테고리 테이블 (계층 구조)
CREATE TABLE categories (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    description TEXT,
    parent_id INT REFERENCES categories(id) ON DELETE SET NULL,
    level INT DEFAULT 1,
    sort_order INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_categories_parent_id ON categories(parent_id);
CREATE INDEX idx_categories_slug ON categories(slug);
CREATE INDEX idx_categories_level ON categories(level);

게시글 관리

-- 게시글 테이블
CREATE TABLE posts (
    id BIGSERIAL PRIMARY KEY,
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    author_id BIGINT NOT NULL REFERENCES users(id),
    category_id INT REFERENCES categories(id) ON DELETE SET NULL,
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'archived', 'deleted')),
    visibility VARCHAR(20) DEFAULT 'public' CHECK (visibility IN ('public', 'private', 'protected')),
    featured BOOLEAN DEFAULT FALSE,
    allow_comments BOOLEAN DEFAULT TRUE,
    view_count INT DEFAULT 0,
    like_count INT DEFAULT 0,
    comment_count INT DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_posts_author_id ON posts(author_id);
CREATE INDEX idx_posts_category_id ON posts(category_id);
CREATE INDEX idx_posts_status ON posts(status);
CREATE INDEX idx_posts_featured ON posts(featured);
CREATE INDEX idx_posts_published_at ON posts(published_at);
CREATE INDEX idx_posts_title_content ON posts USING gin(to_tsvector('korean', title || ' ' || content));

댓글 시스템

-- 댓글 테이블 (계층 구조)
CREATE TABLE comments (
    id BIGSERIAL PRIMARY KEY,
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    author_id BIGINT NOT NULL REFERENCES users(id),
    parent_id BIGINT REFERENCES comments(id) ON DELETE CASCADE,
    content TEXT NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected', 'deleted')),
    like_count INT DEFAULT 0,
    reply_count INT DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_comments_post_id ON comments(post_id);
CREATE INDEX idx_comments_author_id ON comments(author_id);
CREATE INDEX idx_comments_parent_id ON comments(parent_id);
CREATE INDEX idx_comments_status ON comments(status);

태그 시스템

-- 태그 테이블
CREATE TABLE tags (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL,
    description TEXT,
    color VARCHAR(7) DEFAULT '#000000',
    usage_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 게시글-태그 연결
CREATE TABLE post_tags (
    post_id BIGINT NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id INT NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);

CREATE INDEX idx_tags_usage_count ON tags(usage_count);

3. 전자상거래 시스템 (E-commerce System)

개요

상품 관리, 주문 처리, 장바구니 기능을 포함한 완전한 전자상거래 시스템입니다.

스키마 관계도

categories (1) ←→ (N) product_categories ←→ (N) products
users (1) ←→ (N) orders
orders (1) ←→ (N) order_items ←→ (N) products
users (1) ←→ (N) cart_items ←→ (N) products

스키마 구현

상품 관리

-- 상품 테이블
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,
    slug VARCHAR(200) UNIQUE NOT NULL,
    description TEXT,
    short_description VARCHAR(500),
    sku VARCHAR(100) UNIQUE NOT NULL,
    price DECIMAL(10,2) NOT NULL,
    sale_price DECIMAL(10,2),
    cost_price DECIMAL(10,2),
    stock_quantity INT DEFAULT 0,
    min_stock_level INT DEFAULT 0,
    weight DECIMAL(8,2),
    dimensions JSONB, -- {"length": 10, "width": 5, "height": 3, "unit": "cm"}
    status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'discontinued')),
    featured BOOLEAN DEFAULT FALSE,
    digital BOOLEAN DEFAULT FALSE,
    images JSONB DEFAULT '[]',
    attributes JSONB DEFAULT '{}',
    variants JSONB DEFAULT '[]',
    seo_data JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_products_sku ON products(sku);
CREATE INDEX idx_products_status ON products(status);
CREATE INDEX idx_products_featured ON products(featured);
CREATE INDEX idx_products_price ON products(price);
CREATE INDEX idx_products_stock_quantity ON products(stock_quantity);
CREATE INDEX idx_products_name_description ON products USING gin(to_tsvector('korean', name || ' ' || description));

주문 관리

-- 주문 테이블
CREATE TABLE orders (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id),
    order_number VARCHAR(50) UNIQUE NOT NULL,
    status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'confirmed', 'processing', 'shipped', 'delivered', 'cancelled', 'refunded')),
    currency VARCHAR(3) DEFAULT 'KRW',
    subtotal DECIMAL(10,2) NOT NULL,
    tax_amount DECIMAL(10,2) DEFAULT 0,
    shipping_amount DECIMAL(10,2) DEFAULT 0,
    discount_amount DECIMAL(10,2) DEFAULT 0,
    total_amount DECIMAL(10,2) NOT NULL,
    payment_status VARCHAR(20) DEFAULT 'pending' CHECK (payment_status IN ('pending', 'paid', 'failed', 'refunded', 'partially_refunded')),
    payment_method VARCHAR(50),
    shipping_address JSONB,
    billing_address JSONB,
    customer_notes TEXT,
    admin_notes TEXT,
    metadata JSONB DEFAULT '{}',
    shipped_at TIMESTAMP WITH TIME ZONE,
    delivered_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_orders_user_id ON orders(user_id);
CREATE INDEX idx_orders_order_number ON orders(order_number);
CREATE INDEX idx_orders_status ON orders(status);
CREATE INDEX idx_orders_payment_status ON orders(payment_status);
CREATE INDEX idx_orders_created_at ON orders(created_at);

주문 상품 및 장바구니

-- 주문 상품 테이블
CREATE TABLE order_items (
    id BIGSERIAL PRIMARY KEY,
    order_id BIGINT NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id),
    quantity INT NOT NULL,
    unit_price DECIMAL(10,2) NOT NULL,
    total_price DECIMAL(10,2) NOT NULL,
    product_snapshot JSONB, -- 주문 시점의 상품 정보
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_order_items_order_id ON order_items(order_id);
CREATE INDEX idx_order_items_product_id ON order_items(product_id);

-- 장바구니 테이블
CREATE TABLE cart_items (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    product_id BIGINT NOT NULL REFERENCES products(id) ON DELETE CASCADE,
    quantity INT NOT NULL,
    selected_attributes JSONB DEFAULT '{}',
    added_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(user_id, product_id)
);

CREATE INDEX idx_cart_items_user_id ON cart_items(user_id);

4. 콘텐츠 관리 시스템 (CMS)

개요

다양한 콘텐츠 타입을 지원하는 유연한 CMS 시스템입니다.

스키마 관계도

content_types (1) ←→ (N) contents
content_types (1) ←→ (N) content_type_fields
contents (1) ←→ (N) content_revisions
contents (1) ←→ (N) content_workflow_states
taxonomies (1) ←→ (N) taxonomy_terms
contents (N) ←→ (N) content_taxonomy_relations ←→ (N) taxonomy_terms

스키마 구현

콘텐츠 타입 관리

-- 콘텐츠 타입 정의
CREATE TABLE content_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    is_active BOOLEAN DEFAULT TRUE,
    is_hierarchical BOOLEAN DEFAULT FALSE,
    supports_comments BOOLEAN DEFAULT TRUE,
    supports_revisions BOOLEAN DEFAULT TRUE,
    field_schema JSONB DEFAULT '{}',
    list_fields JSONB DEFAULT '[]',
    permissions JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_content_types_name ON content_types(name);
CREATE INDEX idx_content_types_active ON content_types(is_active);

콘텐츠 관리

-- 메인 콘텐츠 테이블
CREATE TABLE contents (
    id BIGSERIAL PRIMARY KEY,
    content_type_id INT NOT NULL REFERENCES content_types(id),
    title VARCHAR(200) NOT NULL,
    slug VARCHAR(200) NOT NULL,
    content_data JSONB DEFAULT '{}',
    excerpt TEXT,
    author_id BIGINT NOT NULL REFERENCES users(id),
    parent_id BIGINT REFERENCES contents(id),
    status VARCHAR(20) DEFAULT 'draft' CHECK (status IN ('draft', 'published', 'scheduled', 'archived', 'deleted')),
    visibility VARCHAR(20) DEFAULT 'public' CHECK (visibility IN ('public', 'private', 'protected', 'password')),
    featured BOOLEAN DEFAULT FALSE,
    template VARCHAR(100),
    seo_title VARCHAR(200),
    seo_description TEXT,
    og_data JSONB DEFAULT '{}',
    view_count INT DEFAULT 0,
    search_vector tsvector,
    published_at TIMESTAMP WITH TIME ZONE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_contents_content_type ON contents(content_type_id);
CREATE INDEX idx_contents_author ON contents(author_id);
CREATE INDEX idx_contents_status ON contents(status);
CREATE INDEX idx_contents_search_vector ON contents USING gin(search_vector);
CREATE UNIQUE INDEX idx_contents_type_slug ON contents(content_type_id, slug);

버전 관리

-- 콘텐츠 버전 관리
CREATE TABLE content_revisions (
    id BIGSERIAL PRIMARY KEY,
    content_id BIGINT NOT NULL REFERENCES contents(id) ON DELETE CASCADE,
    revision_number INT NOT NULL,
    title VARCHAR(200) NOT NULL,
    content_data JSONB DEFAULT '{}',
    author_id BIGINT NOT NULL REFERENCES users(id),
    change_summary TEXT,
    is_auto_save BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(content_id, revision_number)
);

CREATE INDEX idx_content_revisions_content ON content_revisions(content_id);
CREATE INDEX idx_content_revisions_author ON content_revisions(author_id);

분류 체계

-- 분류 체계 (카테고리, 태그 등)
CREATE TABLE taxonomies (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    is_hierarchical BOOLEAN DEFAULT FALSE,
    content_types JSONB DEFAULT '[]',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 분류 용어
CREATE TABLE taxonomy_terms (
    id SERIAL PRIMARY KEY,
    taxonomy_id INT NOT NULL REFERENCES taxonomies(id) ON DELETE CASCADE,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    description TEXT,
    parent_id INT REFERENCES taxonomy_terms(id),
    sort_order INT DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    usage_count INT DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(taxonomy_id, slug)
);

-- 콘텐츠-분류 연결
CREATE TABLE content_taxonomy_relations (
    content_id BIGINT NOT NULL REFERENCES contents(id) ON DELETE CASCADE,
    taxonomy_term_id INT NOT NULL REFERENCES taxonomy_terms(id) ON DELETE CASCADE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (content_id, taxonomy_term_id)
);

5. 파일 관리 시스템 (File Management System)

개요

미디어 파일 업로드, 관리, 연결을 위한 시스템입니다.

스키마 관계도

users (1) ←→ (N) files
files (1) ←→ (N) file_attachments
files (N) ←→ (N) file_shares ←→ (N) users
media_folders (1) ←→ (N) media_files

스키마 구현

-- 파일 테이블
CREATE TABLE files (
    id BIGSERIAL PRIMARY KEY,
    original_name VARCHAR(255) NOT NULL,
    stored_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(500) NOT NULL,
    file_size BIGINT NOT NULL,
    mime_type VARCHAR(100) NOT NULL,
    file_extension VARCHAR(10) NOT NULL,
    file_hash VARCHAR(64), -- SHA-256 해시
    uploader_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    storage_type VARCHAR(20) DEFAULT 'local' CHECK (storage_type IN ('local', 's3', 'gcs', 'azure')),
    metadata JSONB DEFAULT '{}',
    is_public BOOLEAN DEFAULT FALSE,
    is_temporary BOOLEAN DEFAULT FALSE,
    download_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_files_uploader_id ON files(uploader_id);
CREATE INDEX idx_files_mime_type ON files(mime_type);
CREATE INDEX idx_files_file_hash ON files(file_hash);

-- 파일 첨부 테이블
CREATE TABLE file_attachments (
    id BIGSERIAL PRIMARY KEY,
    file_id BIGINT NOT NULL REFERENCES files(id) ON DELETE CASCADE,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT NOT NULL,
    attachment_type VARCHAR(20) NOT NULL CHECK (attachment_type IN ('image', 'document', 'avatar', 'thumbnail', 'gallery')),
    sort_order INT DEFAULT 0,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_file_attachments_file_id ON file_attachments(file_id);
CREATE INDEX idx_file_attachments_entity ON file_attachments(entity_type, entity_id);

6. 알림 시스템 (Notification System)

개요

다양한 채널을 통한 알림 발송 및 설정 관리 시스템입니다.

스키마 관계도

users (1) ←→ (N) notifications
users (1) ←→ (N) notification_settings
notification_templates (1) ←→ (N) notifications

스키마 구현

-- 알림 테이블
CREATE TABLE notifications (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    type VARCHAR(50) NOT NULL,
    title VARCHAR(200) NOT NULL,
    message TEXT NOT NULL,
    data JSONB DEFAULT '{}',
    read_at TIMESTAMP WITH TIME ZONE,
    action_url VARCHAR(500),
    priority VARCHAR(20) DEFAULT 'normal' CHECK (priority IN ('low', 'normal', 'high', 'urgent')),
    delivery_channels JSONB DEFAULT '["web"]',
    delivery_status JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    expires_at TIMESTAMP WITH TIME ZONE
);

CREATE INDEX idx_notifications_user_id ON notifications(user_id);
CREATE INDEX idx_notifications_type ON notifications(type);
CREATE INDEX idx_notifications_read_at ON notifications(read_at);
CREATE INDEX idx_notifications_priority ON notifications(priority);

-- 알림 설정 테이블
CREATE TABLE notification_settings (
    user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    notification_type VARCHAR(50) NOT NULL,
    channels JSONB DEFAULT '{"web": true, "email": true, "push": false, "sms": false}',
    enabled BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (user_id, notification_type)
);

7. 사용자 맞춤 대시보드 시스템 (Dashboard System)

개요

사용자가 위젯을 자유롭게 배치하고 설정할 수 있는 대시보드 시스템입니다.

스키마 관계도

users (1) ←→ (N) dashboards
dashboards (1) ←→ (N) widget_instances
widget_types (1) ←→ (N) widget_instances
data_sources (1) ←→ (N) widget_data_sources ←→ (N) widget_instances
dashboards (1) ←→ (N) dashboard_filters

스키마 구현

위젯 및 데이터 소스 관리

-- 위젯 타입 정의
CREATE TABLE widget_types (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    description TEXT,
    icon VARCHAR(50),
    category VARCHAR(50),
    component_name VARCHAR(100),
    default_config JSONB DEFAULT '{}',
    config_schema JSONB DEFAULT '{}',
    is_active BOOLEAN DEFAULT TRUE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

-- 데이터 소스 정의
CREATE TABLE data_sources (
    id SERIAL PRIMARY KEY,
    name VARCHAR(100) UNIQUE NOT NULL,
    display_name VARCHAR(100) NOT NULL,
    type VARCHAR(50) NOT NULL,
    connection_config JSONB DEFAULT '{}',
    refresh_interval INT DEFAULT 300,
    is_active BOOLEAN DEFAULT TRUE,
    created_by BIGINT REFERENCES users(id),
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

대시보드 관리

-- 대시보드 정의
CREATE TABLE dashboards (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    slug VARCHAR(100) NOT NULL,
    description TEXT,
    owner_id BIGINT NOT NULL REFERENCES users(id),
    layout_config JSONB DEFAULT '{}',
    theme_config JSONB DEFAULT '{}',
    refresh_interval INT DEFAULT 300,
    is_public BOOLEAN DEFAULT FALSE,
    is_template BOOLEAN DEFAULT FALSE,
    view_count INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(owner_id, slug)
);

CREATE INDEX idx_dashboards_owner ON dashboards(owner_id);
CREATE INDEX idx_dashboards_public ON dashboards(is_public);
CREATE INDEX idx_dashboards_template ON dashboards(is_template);

위젯 인스턴스

-- 위젯 인스턴스 (대시보드에 실제 배치된 위젯)
CREATE TABLE widget_instances (
    id BIGSERIAL PRIMARY KEY,
    dashboard_id BIGINT NOT NULL REFERENCES dashboards(id) ON DELETE CASCADE,
    widget_type_id INT NOT NULL REFERENCES widget_types(id),
    title VARCHAR(200) NOT NULL,
    position_config JSONB DEFAULT '{}',
    widget_config JSONB DEFAULT '{}',
    data_config JSONB DEFAULT '{}',
    style_config JSONB DEFAULT '{}',
    is_visible BOOLEAN DEFAULT TRUE,
    sort_order INT DEFAULT 0,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_widget_instances_dashboard ON widget_instances(dashboard_id);
CREATE INDEX idx_widget_instances_widget_type ON widget_instances(widget_type_id);

-- 위젯 데이터 소스 연결
CREATE TABLE widget_data_sources (
    id SERIAL PRIMARY KEY,
    widget_instance_id BIGINT NOT NULL REFERENCES widget_instances(id) ON DELETE CASCADE,
    data_source_id INT NOT NULL REFERENCES data_sources(id),
    query_config JSONB DEFAULT '{}',
    is_primary BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    UNIQUE(widget_instance_id, data_source_id)
);

8. 활동 로그 및 감사 시스템 (Audit System)

개요

시스템의 모든 활동을 기록하고 추적하는 감사 시스템입니다.

스키마 관계도

users (1) ←→ (N) activity_logs
settings (독립적)
system_logs (독립적)

스키마 구현

-- 활동 로그 테이블
CREATE TABLE activity_logs (
    id BIGSERIAL PRIMARY KEY,
    user_id BIGINT REFERENCES users(id) ON DELETE SET NULL,
    session_id UUID,
    action VARCHAR(100) NOT NULL,
    entity_type VARCHAR(50) NOT NULL,
    entity_id BIGINT,
    old_values JSONB,
    new_values JSONB,
    changes JSONB,
    ip_address INET,
    user_agent TEXT,
    metadata JSONB DEFAULT '{}',
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_activity_logs_user_id ON activity_logs(user_id);
CREATE INDEX idx_activity_logs_action ON activity_logs(action);
CREATE INDEX idx_activity_logs_entity ON activity_logs(entity_type, entity_id);
CREATE INDEX idx_activity_logs_created_at ON activity_logs(created_at);

-- 시스템 설정 테이블
CREATE TABLE settings (
    key_name VARCHAR(100) PRIMARY KEY,
    value TEXT NOT NULL,
    data_type VARCHAR(20) DEFAULT 'string' CHECK (data_type IN ('string', 'number', 'boolean', 'json', 'array')),
    description TEXT,
    category VARCHAR(50) DEFAULT 'general',
    is_public BOOLEAN DEFAULT FALSE,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_settings_category ON settings(category);
CREATE INDEX idx_settings_is_public ON settings(is_public);

필드 분리 vs JSON 사용 전략

필드 분리 방식을 선택해야 하는 경우

장점

쿼리 성능: 개별 컬럼에 대한 인덱스 최적화
데이터 타입 강제: 스키마 레벨에서 데이터 유효성 검증
JOIN 최적화: 관계형 데이터베이스의 강점 활용
통계 정보: 옵티마이저가 더 정확한 실행 계획 수립

사용 예시

-- 필드 분리 방식 (빠름)
SELECT * FROM users WHERE age > 30 AND city = 'Seoul';

-- 인덱스 활용 가능
CREATE INDEX idx_users_age ON users(age);
CREATE INDEX idx_users_city ON users(city);

JSON 방식을 선택해야 하는 경우

장점

유연성: 스키마 변경 없이 새로운 필드 추가 가능
저장 공간: 널 값이 많은 경우 공간 효율적
복잡한 구조: 중첩된 데이터 구조 표현 용이
개발 속도: 빠른 프로토타이핑 가능

사용 예시

-- JSON 방식 (유연함)
SELECT * FROM users WHERE profile->>'age'::int > 30 AND profile->>'city' = 'Seoul';

-- JSON 인덱스로 최적화
CREATE INDEX idx_users_profile_age ON users ((profile->>'age')::int);
CREATE INDEX idx_users_profile_city ON users ((profile->>'city'));

권장 하이브리드 접근법

핵심 필드는 분리하고, 부가 정보는 JSON으로 저장하는 방식을 권장합니다.

-- 하이브리드 예시
CREATE TABLE products (
    id BIGSERIAL PRIMARY KEY,
    name VARCHAR(200) NOT NULL,        -- 필드 분리 (검색 필수)
    price DECIMAL(10,2) NOT NULL,      -- 필드 분리 (정렬/집계)
    status VARCHAR(20) NOT NULL,       -- 필드 분리 (필터링)
    category_id INT NOT NULL,          -- 필드 분리 (JOIN)
    
    -- JSON으로 유연한 데이터 저장
    attributes JSONB DEFAULT '{}',     -- 상품별 다른 속성
    seo_data JSONB DEFAULT '{}',       -- SEO 관련 정보
    settings JSONB DEFAULT '{}'        -- 각종 설정
);

스키마 설계 베스트 프랙티스

1. 네이밍 규칙

테이블명: 복수형 소문자, 언더스코어 사용 (예: users, order_items)
컬럼명: 소문자, 언더스코어 사용 (예: user_id, created_at)
인덱스명: idx_ 접두사 사용 (예: idx_user_id)

2. 데이터 타입 선택

-- 권장 데이터 타입
BIGSERIAL         -- 자동 증가 ID
VARCHAR(길이)     -- 제한된 길이 문자열
TEXT              -- 가변 길이 긴 문자열
DECIMAL(10,2)     -- 정확한 소수 (가격 등)
TIMESTAMP WITH TIME ZONE  -- 타임존 포함 시간
JSONB             -- JSON 데이터 (PostgreSQL 최적화)
BOOLEAN           -- 참/거짓
INET              -- IP 주소
UUID              -- 고유 식별자

3. 인덱스 전략

-- 기본 인덱스 패턴
CREATE INDEX idx_table_column ON table_name(column_name);

-- 복합 인덱스
CREATE INDEX idx_posts_status_published ON posts(status, published_at);

-- 부분 인덱스
CREATE INDEX idx_users_active ON users(id) WHERE status = 'active';

-- JSON 인덱스
CREATE INDEX idx_products_attributes ON products USING gin(attributes);

-- 전문 검색 인덱스
CREATE INDEX idx_posts_search ON posts USING gin(to_tsvector('korean', title || ' ' || content));

4. 제약 조건 활용

-- CHECK 제약 조건
status VARCHAR(20) DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'suspended'))

-- 외래키 제약 조건
user_id BIGINT NOT NULL REFERENCES users(id) ON DELETE CASCADE

-- 유니크 제약 조건
UNIQUE(user_id, product_id)

5. 공통 컬럼 패턴

-- 모든 테이블에 포함할 기본 컬럼
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,

-- 선택적 컬럼
deleted_at TIMESTAMP WITH TIME ZONE,  -- 소프트 삭제
version INT DEFAULT 1,                -- 버전 관리
created_by BIGINT REFERENCES users(id), -- 생성자
updated_by BIGINT REFERENCES users(id), -- 수정자

마무리

이 가이드에서 제시한 스키마 설계 방법들은 다음과 같은 실제 서비스들에서 검증된 패턴들입니다:

사용자 관리: Auth0, Firebase Auth
게시판 시스템: Reddit, Stack Overflow
전자상거래: Shopify, WooCommerce
CMS: WordPress, Drupal, Strapi
파일 관리: Dropbox, Google Drive
알림 시스템: Slack, Discord
대시보드: Grafana, Tableau

각 시스템의 요구사항에 맞게 스키마를 선택하고 커스터마이징하여 사용하시면 됩니다. 특히 PostgreSQL의 강력한 JSONB 지원과 고급 인덱싱 기능을 활용하면 유연하면서도 고성능의 시스템을 구축할 수 있습니다.

스키마 설계는 시스템의 기초가 되는 중요한 작업입니다. 초기 설계에 충분한 시간을 투자하여 확장 가능하고 유지보수가 용이한 구조를 만드는 것이 장기적으로 큰 가치를 가져다 줄 것입니다.

서론

공통 설정

1. 사용자 관리 시스템 (User Management System)

개요

스키마 관계도

스키마 구현

사용자 기본 정보

사용자 프로필

권한 관리

세션 관리

트리거 설정

2. 게시판 시스템 (Board System)

개요

스키마 관계도

스키마 구현

카테고리 관리

게시글 관리

댓글 시스템

태그 시스템

3. 전자상거래 시스템 (E-commerce System)

개요

스키마 관계도

스키마 구현

상품 관리

주문 관리

주문 상품 및 장바구니

4. 콘텐츠 관리 시스템 (CMS)

개요

스키마 관계도

스키마 구현

콘텐츠 타입 관리

콘텐츠 관리

버전 관리

분류 체계

5. 파일 관리 시스템 (File Management System)

개요

스키마 관계도

스키마 구현

6. 알림 시스템 (Notification System)

개요

스키마 관계도

스키마 구현

7. 사용자 맞춤 대시보드 시스템 (Dashboard System)

개요

스키마 관계도

스키마 구현

위젯 및 데이터 소스 관리

대시보드 관리

위젯 인스턴스

8. 활동 로그 및 감사 시스템 (Audit System)

개요

스키마 관계도

스키마 구현

필드 분리 vs JSON 사용 전략

필드 분리 방식을 선택해야 하는 경우

장점

사용 예시

JSON 방식을 선택해야 하는 경우

장점

사용 예시

권장 하이브리드 접근법

스키마 설계 베스트 프랙티스

1. 네이밍 규칙

2. 데이터 타입 선택

3. 인덱스 전략

4. 제약 조건 활용

5. 공통 컬럼 패턴

마무리

댓글 남기기 응답 취소