PoC(ver.1)

Tables (10)

request

  • request_id — INT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • type — VARCHAR(100) — NOT NULL
  • description — TEXT
  • from_team_id — INT — Foreign key → team.team_id — NOT NULL
  • to_team_id — INT — Foreign key → team.team_id — NOT NULL
  • created_by_user_id — INT — Foreign key → user.user_id — NOT NULL
  • created_at — DATETIME — NOT NULL
  • due_date — DATE
  • status — VARCHAR(50) — NOT NULL

request_task

  • request_task_id — INT — Primary key — NOT NULL
  • request_id — INT — Foreign key → request.request_id — NOT NULL
  • task_meta_id — INT — Foreign key → task_meta.task_meta_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • from_team_id — INT — Foreign key → team.team_id — NOT NULL
  • to_team_id — INT — Foreign key → team.team_id — NOT NULL
  • assigned_to_user_id — INT — Foreign key → user.user_id
  • need_approval — BOOLEAN — NOT NULL
  • status — VARCHAR(50) — NOT NULL
  • start_date — DATE
  • due_date — DATE
  • completed_at — DATETIME
  • is_shared — BOOLEAN — NOT NULL
  • is_important — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

request_task_member

  • id — INT — Primary key — NOT NULL
  • request_task_id — INT — Foreign key → request_task.request_task_id — NOT NULL
  • user_id — INT — Foreign key → user.user_id — NOT NULL

routine_task

  • routine_task_id — INT — Primary key — NOT NULL
  • task_meta_id — INT — Foreign key → task_meta.task_meta_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • cycle — VARCHAR(50) — NOT NULL
  • from_team_id — INT — Foreign key → team.team_id — NOT NULL
  • to_team_id — INT — Foreign key → team.team_id — NOT NULL
  • assigned_to_user_id — INT — Foreign key → user.user_id
  • need_approval — BOOLEAN — NOT NULL
  • status — VARCHAR(50) — NOT NULL
  • start_date — DATE
  • due_date — DATE
  • completed_at — DATETIME
  • is_shared — BOOLEAN — NOT NULL
  • is_important — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

task_file

  • task_file_id — INT — Primary key — NOT NULL
  • request_task_id — INT — Foreign key → request_task.request_task_id — NOT NULL
  • user_id — INT — Foreign key → user.user_id — NOT NULL
  • filename — VARCHAR(255) — NOT NULL
  • filepath — VARCHAR(500) — NOT NULL
  • uploaded_at — DATETIME — NOT NULL

task_memo

  • task_memo_id — INT — Primary key — NOT NULL
  • request_task_id — INT — Foreign key → request_task.request_task_id — NOT NULL
  • user_id — INT — Foreign key → user.user_id — NOT NULL
  • content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL

task_meta

  • task_meta_id — INT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • category — VARCHAR(100) — NOT NULL
  • need_approval — BOOLEAN — NOT NULL
  • description — TEXT

team

  • team_id — INT — Primary key — NOT NULL
  • name — VARCHAR(100) — NOT NULL

unfinished_work

  • id — INTEGER
  • task_id — INT — NOT NULL
  • type — VARCHAR(20) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • due_date — DATE — NOT NULL
  • status — VARCHAR(50) — NOT NULL
  • is_shared — BOOLEAN — NOT NULL
  • is_important — BOOLEAN — NOT NULL
  • assigned_to_user_id — INT
  • created_at — DATETIME — NOT NULL

user

  • user_id — INT — Primary key — NOT NULL
  • team_id — INT — Foreign key → team.team_id — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • password — VARCHAR(100) — NOT NULL
  • position — VARCHAR(100) — NOT NULL
  • email — VARCHAR(100) — NOT NULL