소프트웨어 개발 전주기 상용 통합 서비스

Atlassian Jira + Confluence + Xray / Zephyr / Bitbucket

• 주요 기능:
  • Jira → 요구사항, 이슈, 태스크, 추적, 워크플로우 관리
  • Xray/Zephyr → 테스트 계획/케이스/결과 관리
  • Confluence → 분석/설계 문서 협업
  • Bitbucket → Git 형상관리 + CI/CD
• 특징: 애자일, 스크럼에 특화 / 마켓플레이스 확장성 뛰어남
• 도입 예: 삼성전자, 카카오, 현대차 등

CREATE DATABASE IF NOT EXISTS project_management;
USE project_management;

-- Tenants table: 테넌트(조직) 정보
CREATE TABLE tenants (
    tenant_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_name VARCHAR(100) NOT NULL UNIQUE,
    description TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Users table: 사용자 정보 (테넌트와 연계)
CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('ADMIN', 'DEVELOPER', 'TESTER', 'MANAGER', 'SYSTEM_ADMIN') DEFAULT 'DEVELOPER',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    CONSTRAINT unique_username_per_tenant UNIQUE (tenant_id, username)
);

-- Projects table: 프로젝트 정보 (테넌트와 연계)
CREATE TABLE projects (
    project_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_key VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    project_type ENUM('SCRUM', 'KANBAN', 'WATERFALL') NOT NULL,
    lead_user_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (lead_user_id) REFERENCES users(user_id) ON DELETE SET NULL,
    CONSTRAINT unique_project_key_per_tenant UNIQUE (tenant_id, project_key)
);

-- Requirements table: 요구사항 정의 (테넌트와 연계)
CREATE TABLE requirements (
    requirement_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
    status ENUM('DRAFT', 'APPROVED', 'REJECTED', 'IN_PROGRESS', 'DONE') DEFAULT 'DRAFT',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Workflows table: 워크플로우 정의 (테넌트와 연계)
CREATE TABLE workflows (
    workflow_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

-- Workflow States table: 워크플로우 상태
CREATE TABLE workflow_states (
    state_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    workflow_id BIGINT NOT NULL,
    state_name VARCHAR(50) NOT NULL,
    is_initial BOOLEAN DEFAULT FALSE,
    is_final BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (workflow_id) REFERENCES workflows(workflow_id) ON DELETE CASCADE
);

-- Workflow Transitions table: 상태 전이 규칙
CREATE TABLE workflow_transitions (
    transition_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    workflow_id BIGINT NOT NULL,
    from_state_id BIGINT,
    to_state_id BIGINT,
    transition_name VARCHAR(50) NOT NULL,
    FOREIGN KEY (workflow_id) REFERENCES workflows(workflow_id) ON DELETE CASCADE,
    FOREIGN KEY (from_state_id) REFERENCES workflow_states(state_id) ON DELETE CASCADE,
    FOREIGN KEY (to_state_id) REFERENCES workflow_states(state_id) ON DELETE CASCADE
);

-- Issues table: 작업 (Story, Task, Bug 등, 테넌트와 연계)
CREATE TABLE issues (
    issue_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    requirement_id BIGINT,
    issue_type ENUM('STORY', 'TASK', 'BUG', 'EPIC') NOT NULL,
    summary VARCHAR(255) NOT NULL,
    description TEXT,
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
    status_id BIGINT NOT NULL,
    assignee_id BIGINT,
    reporter_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE SET NULL,
    FOREIGN KEY (status_id) REFERENCES workflow_states(state_id) ON DELETE RESTRICT,
    FOREIGN KEY (assignee_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (reporter_id) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Test Cases table: 테스트 케이스 관리 (테넌트와 연계)
CREATE TABLE test_cases (
    test_case_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    requirement_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    test_type ENUM('MANUAL', 'AUTOMATED') DEFAULT 'MANUAL',
    status ENUM('DRAFT', 'ACTIVE', 'OBSOLETE') DEFAULT 'DRAFT',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Test Results table: 테스트 실행 결과
CREATE TABLE test_results (
    test_result_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    test_case_id BIGINT NOT NULL,
    issue_id BIGINT,
    result ENUM('PASS', 'FAIL', 'BLOCKED') NOT NULL,
    executed_by BIGINT,
    execution_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comments TEXT,
    FOREIGN KEY (test_case_id) REFERENCES test_cases(test_case_id) ON DELETE CASCADE,
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE SET NULL,
    FOREIGN KEY (executed_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Releases table: 배포 정보 (테넌트와 연계)
CREATE TABLE releases (
    release_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    project_id BIGINT NOT NULL,
    version VARCHAR(50) NOT NULL,
    description TEXT,
    release_date DATE,
    status ENUM('PLANNING', 'RELEASED', 'ARCHIVED') DEFAULT 'PLANNING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

-- Issue-Release Mapping: 작업과 배포 연결
CREATE TABLE issue_release (
    issue_id BIGINT NOT NULL,
    release_id BIGINT NOT NULL,
    PRIMARY KEY (issue_id, release_id),
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE CASCADE,
    FOREIGN KEY (release_id) REFERENCES releases(release_id) ON DELETE CASCADE
);

-- Comments table: 작업 및 요구사항에 대한 댓글 (테넌트와 연계)
CREATE TABLE comments (
    comment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    tenant_id BIGINT NOT NULL,
    issue_id BIGINT,
    requirement_id BIGINT,
    user_id BIGINT NOT NULL,
    comment_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (tenant_id) REFERENCES tenants(tenant_id) ON DELETE CASCADE,
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);

 

Jira와 같은 프로젝트 관리(Scrum, Kanban), 요구사항 관리, 테스트, 배포 등 SDLC(소프트웨어 개발 라이프사이클) 전 과정을 지원하는 서비스를 위한 데이터베이스 설계를 MariaDB로 구현하겠습니다. 이 데이터베이스는 프로젝트, 요구사항, 작업(이슈), 테스트 케이스, 배포, 사용자, 워크플로우 상태를 관리할 수 있도록 테이블을 구성합니다. 아래는 주요 기능과 요구사항을 반영한 MariaDB 생성문입니다.

### 설계 개요
- **주요 엔터티**:
  - **Users**: 시스템 사용자(개발자, 관리자 등).
  - **Projects**: 프로젝트 정보(Scrum, Kanban 등).
  - **Requirements**: 요구사항 정의 및 관리.
  - **Issues**: 작업(Story, Task, Bug 등) 및 워크플로우 상태.
  - **TestCases**: 테스트 케이스 및 결과.
  - **Releases**: 배포 정보.
  - **Workflows**: 워크플로우 상태 전이.
- **관계**:
  - 프로젝트는 여러 요구사항, 작업, 테스트 케이스, 배포를 가짐.
  - 요구사항은 작업 및 테스트 케이스와 연결(추적성 보장).
  - 작업은 워크플로우 상태를 따르며, 사용자에게 할당됨.
- **고려사항**:
  - 유연한 워크플로우를 위해 상태와 전이를 별도 테이블로 관리.
  - 요구사항과 테스트 케이스 간의 추적 매트릭스 지원.
  - Agile(Scrum, Kanban) 보드와 배포 관리 지원.

### MariaDB 생성문

```sql
CREATE DATABASE IF NOT EXISTS project_management;
USE project_management;

-- Users table: 시스템 사용자 정보
CREATE TABLE users (
    user_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    username VARCHAR(50) NOT NULL UNIQUE,
    email VARCHAR(100) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    full_name VARCHAR(100),
    role ENUM('ADMIN', 'DEVELOPER', 'TESTER', 'MANAGER') DEFAULT 'DEVELOPER',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

-- Projects table: 프로젝트 정보 (Scrum, Kanban 등)
CREATE TABLE projects (
    project_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_key VARCHAR(10) NOT NULL UNIQUE,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    project_type ENUM('SCRUM', 'KANBAN', 'WATERFALL') NOT NULL,
    lead_user_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (lead_user_id) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Requirements table: 요구사항 정의
CREATE TABLE requirements (
    requirement_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
    status ENUM('DRAFT', 'APPROVED', 'REJECTED', 'IN_PROGRESS', 'DONE') DEFAULT 'DRAFT',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Workflows table: 워크플로우 상태 정의
CREATE TABLE workflows (
    workflow_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    name VARCHAR(100) NOT NULL,
    description TEXT,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

-- Workflow States table: 워크플로우 상태
CREATE TABLE workflow_states (
    state_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    workflow_id BIGINT NOT NULL,
    state_name VARCHAR(50) NOT NULL,
    is_initial BOOLEAN DEFAULT FALSE,
    is_final BOOLEAN DEFAULT FALSE,
    FOREIGN KEY (workflow_id) REFERENCES workflows(workflow_id) ON DELETE CASCADE
);

-- Workflow Transitions table: 상태 전이 규칙
CREATE TABLE workflow_transitions (
    transition_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    workflow_id BIGINT NOT NULL,
    from_state_id BIGINT,
    to_state_id BIGINT,
    transition_name VARCHAR(50) NOT NULL,
    FOREIGN KEY (workflow_id) REFERENCES workflows(workflow_id) ON DELETE CASCADE,
    FOREIGN KEY (from_state_id) REFERENCES workflow_states(state_id) ON DELETE CASCADE,
    FOREIGN KEY (to_state_id) REFERENCES workflow_states(state_id) ON DELETE CASCADE
);

-- Issues table: 작업 (Story, Task, Bug 등)
CREATE TABLE issues (
    issue_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    requirement_id BIGINT,
    issue_type ENUM('STORY', 'TASK', 'BUG', 'EPIC') NOT NULL,
    summary VARCHAR(255) NOT NULL,
    description TEXT,
    priority ENUM('LOW', 'MEDIUM', 'HIGH', 'CRITICAL') DEFAULT 'MEDIUM',
    status_id BIGINT NOT NULL,
    assignee_id BIGINT,
    reporter_id BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE SET NULL,
    FOREIGN KEY (status_id) REFERENCES workflow_states(state_id) ON DELETE RESTRICT,
    FOREIGN KEY (assignee_id) REFERENCES users(user_id) ON DELETE SET NULL,
    FOREIGN KEY (reporter_id) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Test Cases table: 테스트 케이스 관리
CREATE TABLE test_cases (
    test_case_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    requirement_id BIGINT,
    title VARCHAR(255) NOT NULL,
    description TEXT,
    test_type ENUM('MANUAL', 'AUTOMATED') DEFAULT 'MANUAL',
    status ENUM('DRAFT', 'ACTIVE', 'OBSOLETE') DEFAULT 'DRAFT',
    created_by BIGINT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE SET NULL,
    FOREIGN KEY (created_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Test Results table: 테스트 실행 결과
CREATE TABLE test_results (
    test_result_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    test_case_id BIGINT NOT NULL,
    issue_id BIGINT,
    result ENUM('PASS', 'FAIL', 'BLOCKED') NOT NULL,
    executed_by BIGINT,
    execution_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    comments TEXT,
    FOREIGN KEY (test_case_id) REFERENCES test_cases(test_case_id) ON DELETE CASCADE,
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE SET NULL,
    FOREIGN KEY (executed_by) REFERENCES users(user_id) ON DELETE SET NULL
);

-- Releases table: 배포 정보
CREATE TABLE releases (
    release_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    project_id BIGINT NOT NULL,
    version VARCHAR(50) NOT NULL,
    description TEXT,
    release_date DATE,
    status ENUM('PLANNING', 'RELEASED', 'ARCHIVED') DEFAULT 'PLANNING',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (project_id) REFERENCES projects(project_id) ON DELETE CASCADE
);

-- Issue-Release Mapping: 작업과 배포 연결
CREATE TABLE issue_release (
    issue_id BIGINT NOT NULL,
    release_id BIGINT NOT NULL,
    PRIMARY KEY (issue_id, release_id),
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE CASCADE,
    FOREIGN KEY (release_id) REFERENCES releases(release_id) ON DELETE CASCADE
);

-- Comments table: 작업 및 요구사항에 대한 댓글
CREATE TABLE comments (
    comment_id BIGINT AUTO_INCREMENT PRIMARY KEY,
    issue_id BIGINT,
    requirement_id BIGINT,
    user_id BIGINT NOT NULL,
    comment_text TEXT NOT NULL,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (issue_id) REFERENCES issues(issue_id) ON DELETE CASCADE,
    FOREIGN KEY (requirement_id) REFERENCES requirements(requirement_id) ON DELETE CASCADE,
    FOREIGN KEY (user_id) REFERENCES users(user_id) ON DELETE CASCADE
);
```

---

### 설명
- **데이터베이스 구조**:
  - `users`: 사용자 정보 관리(권한, 역할 포함).
  - `projects`: 프로젝트 정보(Scrum, Kanban 등) 저장.
  - `requirements`: 요구사항 정의 및 상태 관리.
  - `workflows`, `workflow_states`, `workflow_transitions`: 유연한 워크플로우 관리.
  - `issues`: 작업(Story, Task, Bug 등) 관리, 요구사항과 연결.
  - `test_cases`, `test_results`: 테스트 케이스와 결과 관리, 요구사항 추적.
  - `releases`, `issue_release`: 배포 정보와 작업 연결.
  - `comments`: 요구사항 및 작업에 대한 협업 기록.
- **특징**:
  - 요구사항과 테스트 케이스 간 추적성 보장(`requirement_id` 참조).
  - 워크플로우 상태 전이를 별도 테이블로 관리하여 유연성 확보.
  - 외래 키 제약으로 데이터 무결성 유지.
  - Agile 보드(Scrum, Kanban)를 지원하기 위해 `issue_type`, `status_id` 사용.
- **확장성**:
  - 새로운 `issue_type`이나 `status` 추가 가능.
  - 플러그인처럼 동작할 추가 테이블(예: SLA, 보고서) 확장 가능.