vms

Tables (7)

inventory_lots (입고 로트 재고)

  • id — bigint — Primary key — NOT NULL
  • ship_id — bigint — Foreign key → ships.id — NOT NULL
  • medicine_id — bigint — Foreign key → medicines.id — NOT NULL
  • lot_no — varchar(80) — NOT NULL
  • received_quantity — integer — NOT NULL
  • remaining_quantity — integer — NOT NULL
  • expires_on — date — NOT NULL
  • received_at — timestamptz — NOT NULL
  • recorded_by_user_id — varchar(100) — NOT NULL
  • created_at — timestamptz — NOT NULL

medicine_categories (약품 카테고리 마스터)

  • id — bigint — Primary key — NOT NULL
  • code — varchar(50) — NOT NULL
  • name — varchar(100) — NOT NULL
  • description — varchar(500)
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

medicine_usages (약품 사용 기록)

  • id — bigint — Primary key — NOT NULL
  • ship_id — bigint — Foreign key → ships.id — NOT NULL
  • medicine_id — bigint — Foreign key → medicines.id — NOT NULL
  • quantity — integer — NOT NULL
  • used_at — timestamptz — NOT NULL
  • used_by_user_id — varchar(100) — NOT NULL
  • status — varchar(20) — NOT NULL
  • cancel_reason — varchar(500)
  • canceled_at — timestamptz
  • canceled_by_user_id — varchar(100)
  • created_at — timestamptz — NOT NULL

medicines (약품 마스터)

  • id — bigint — Primary key — NOT NULL
  • name — varchar(100) — NOT NULL
  • category_id — bigint — Foreign key → medicine_categories.id — NOT NULL
  • unit — varchar(30) — NOT NULL
  • active — boolean — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

ship_medicine_standards (선박별 의약품 비치 기준)

  • id — bigint — Primary key — NOT NULL
  • ship_id — bigint — Foreign key → ships.id — NOT NULL
  • medicine_id — bigint — Foreign key → medicines.id — NOT NULL
  • minimum_quantity — integer — NOT NULL
  • created_at — timestamptz — NOT NULL
  • updated_at — timestamptz — NOT NULL

ships (선박 마스터)

  • id — bigint — Primary key — NOT NULL
  • name — varchar(100) — NOT NULL
  • created_at — timestamptz — NOT NULL

usage_allocations (사용 차감 배분)

  • id — bigint — Primary key — NOT NULL
  • usage_id — bigint — Foreign key → medicine_usages.id — NOT NULL
  • inventory_lot_id — bigint — Foreign key → inventory_lots.id — NOT NULL
  • quantity — integer — NOT NULL
  • created_at — timestamptz — NOT NULL