ㅁㄴㅇㄻ

테이블 (6)

거래 (transactions)

  • txn_id — CHAR(36)
  • txn_type — ENUM('DEPOSIT','WITHDRAWAL','TRANSFER','LOAN_REPAYMENT') — NOT NULL
  • occurred_at — DATETIME(3) — NOT NULL
  • amount — DECIMAL(18,2) — NOT NULL
  • fee_amount — DECIMAL(18,2) — NOT NULL
  • deposit_channel — ENUM('ATM','ONLINE','AUTO')
  • memo — VARCHAR(255)
  • from_account_id — CHAR(36)
  • to_account_id — CHAR(36)
  • created_by — CHAR(36)
  • CHECK — (amount > 0)
  • CHECK — ( (txn_type='DEPOSIT' AND to_account_id IS NOT NULL AND from_account_id IS NULL) — NOT NULL

계좌 (accounts)

  • account_id — CHAR(36)
  • user_id — CHAR(36) — NOT NULL
  • account_number — VARCHAR(32) — NOT NULL
  • account_type — ENUM('DEPOSIT','SAVINGS','LOAN') — NOT NULL
  • initial_deposit — DECIMAL(18,2)
  • current_balance — DECIMAL(18,2) — NOT NULL
  • min_balance — DECIMAL(18,2) — NOT NULL
  • status — ENUM('ACTIVE','CLOSED','FROZEN','LOCKED') — NOT NULL
  • opened_at — DATETIME(3) — NOT NULL
  • closed_at — DATETIME(3)

대출 (loans)

  • loan_id — CHAR(36)
  • user_id — CHAR(36) — NOT NULL
  • loan_account_id — CHAR(36) — NOT NULL
  • collateral_info — VARCHAR(255) — NOT NULL
  • collateral_account_id — CHAR(36)
  • amount — DECIMAL(18,2) — NOT NULL
  • ltv_ratio — DECIMAL(5,4) — NOT NULL
  • status — ENUM('ONGOING','DELINQUENT','PAID','PREPAID') — NOT NULL
  • interest_rate_apr — DECIMAL(7,4) — NOT NULL
  • disbursed_at — DATETIME(3) — NOT NULL
  • repaid_at — DATETIME(3)
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL
  • CHECK — (amount > 0)
  • CHECK — (ltv_ratio > 0 AND ltv_ratio <= 1)
  • CHECK — (interest_rate_apr >= 0)

등급 (membership_level)

  • level_code — VARCHAR(20)
  • display_name — VARCHAR(50) — NOT NULL
  • desc_text — VARCHAR(200)
  • min_avg_balance — DECIMAL(18,2)
  • min_monthly_txn — DECIMAL(18,2)
  • default_discount_rate — DECIMAL(5,2)
  • created_at — DATETIME(3) — NOT NULL

수수료 정책 (fee_policy)

  • policy_id — CHAR(36)
  • policy_name — VARCHAR(100) — NOT NULL
  • discount_rate — DECIMAL(5,2)
  • limit_value — DECIMAL(18,2) — NOT NULL
  • apply_user_level — ENUM('BRONZE','SILVER','GOLD','PLATINUM','VIP')
  • apply_txn_type — ENUM('DEPOSIT','WITHDRAWAL','TRANSFER','LOAN_REPAYMENT')
  • min_txn_amount — DECIMAL(18,2)
  • max_txn_amount — DECIMAL(18,2)
  • created_at — DATETIME(3) — NOT NULL
  • CHECK — (min_txn_amount IS NULL OR max_txn_amount IS NULL OR min_txn_amount <= max_txn_amount)

회원 (users)

  • user_id — CHAR(36)
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(30)
  • email — VARCHAR(255) — NOT NULL
  • login_id — VARCHAR(100) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • birth_date — DATE — NOT NULL
  • user_level — ENUM('BRONZE','SILVER','GOLD','PLATINUM','VIP') — NOT NULL
  • created_at — DATETIME(3) — NOT NULL
  • updated_at — DATETIME(3) — NOT NULL