찍찍 ERD 초안

테이블 (19)

branch

  • branch_id — VARCHAR(10) — 기본 키 — NOT NULL
  • branch_name — VARCHAR(50) — NOT NULL
  • branch_type — VARCHAR(30) — NOT NULL
  • role — VARCHAR(50)
  • customer_profile — VARCHAR(100)
  • is_main_branch — CHAR(1) — NOT NULL
  • branch_characteristic_note — VARCHAR(100)

branch_transfer

  • transfer_id — BIGINT — 기본 키 — NOT NULL
  • transfer_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • from_branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • to_branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • item_id — INT — 외래 키 → item.item_id — NOT NULL
  • qty — INT — NOT NULL
  • status — VARCHAR(20)
  • is_manual_request — CHAR(1)

calendar

  • cal_date — DATE — 기본 키 — NOT NULL
  • day_of_week — VARCHAR(5) — NOT NULL
  • is_weekend — CHAR(1) — NOT NULL
  • is_holiday — CHAR(1) — NOT NULL
  • event_flag — CHAR(1) — NOT NULL
  • event_name — VARCHAR(30)
  • is_payday_period — CHAR(1) — NOT NULL
  • days_to_payday — INT

closing_sale_recommendation

  • recommendation_id — BIGINT — 기본 키 — NOT NULL
  • rec_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • risk_qty — INT
  • discount_rate — DECIMAL(4,2)
  • applied — CHAR(1)
  • applied_at — DATETIME

delivery_channel_mapping

  • mapping_id — VARCHAR(10) — 기본 키 — NOT NULL
  • delivery_channel_name — VARCHAR(20) — NOT NULL
  • delivery_channel_code — VARCHAR(20) — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • delivery_menu_id — VARCHAR(20)
  • sync_enabled — CHAR(1)

delivery_stock_sync_log

  • sync_event_id — VARCHAR(15) — 기본 키 — NOT NULL
  • event_datetime — DATETIME — NOT NULL
  • delivery_channel_code — VARCHAR(20) — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • delivery_status_after — VARCHAR(20)
  • sync_result — VARCHAR(20)
  • trigger_reason — VARCHAR(30)

disposal_log

  • disposal_id — BIGINT — 기본 키 — NOT NULL
  • disposal_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • state_code — VARCHAR(30) — NOT NULL
  • discarded_qty — INT — NOT NULL
  • discard_reason — VARCHAR(30)
  • disposal_time — TIME
  • lot_id — VARCHAR(40)

dough_state_inventory

  • dough_inv_id — BIGINT — 기본 키 — NOT NULL
  • snapshot_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • item_id — INT — 외래 키 → item.item_id — NOT NULL
  • state_code — VARCHAR(20) — NOT NULL
  • qty — INT — NOT NULL
  • unit — VARCHAR(10)
  • shelf_life_rule — VARCHAR(20)
  • prediction_note — VARCHAR(50)
  • lot_id — VARCHAR(40)
  • production_date — DATE
  • thaw_start_datetime — DATETIME
  • expiry_date — DATE

forecast_result

  • forecast_id — BIGINT — 기본 키 — NOT NULL
  • forecast_created_date — DATE — NOT NULL
  • target_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • predicted_quantity — DECIMAL(10,2) — NOT NULL
  • model_version — VARCHAR(20)

inventory_snapshot

  • snapshot_id — BIGINT — 기본 키 — NOT NULL
  • snapshot_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • item_id — INT — 외래 키 → item.item_id — NOT NULL
  • unit — VARCHAR(10)
  • current_qty — INT — NOT NULL
  • safety_stock — INT
  • target_stock — INT
  • lot_id — VARCHAR(40)
  • received_date — DATE
  • expiry_date — DATE

item

  • item_id — INT — 기본 키 — NOT NULL
  • item_name — VARCHAR(50) — NOT NULL
  • item_type — VARCHAR(20) — NOT NULL
  • default_unit — VARCHAR(10)

product

  • product_id — VARCHAR(10) — 기본 키 — NOT NULL
  • product_name — VARCHAR(50) — NOT NULL
  • product_type — VARCHAR(20) — NOT NULL
  • product_category — VARCHAR(30) — NOT NULL
  • unit_price_krw — INT — NOT NULL
  • base_daily_demand — INT
  • is_signature_product — CHAR(1)
  • delivery_available — CHAR(1)

purchase_order

  • order_id — BIGINT — 기본 키 — NOT NULL
  • order_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • target_stock_qty — INT
  • inventory_position_qty — INT
  • order_qty — INT
  • order_unit_qty — INT
  • status — VARCHAR(20)
  • approved_by — VARCHAR(30)
  • approved_at — DATETIME

purchase_order_line

  • line_id — BIGINT — 기본 키 — NOT NULL
  • order_id — BIGINT — 외래 키 → purchase_order.order_id — NOT NULL
  • item_id — INT — 외래 키 → item.item_id — NOT NULL
  • supplier_id — VARCHAR(10) — 외래 키 → supplier.supplier_id — NOT NULL
  • allocated_qty — INT — NOT NULL

recipe

  • recipe_id — INT — 기본 키 — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • item_id — INT — 외래 키 → item.item_id — NOT NULL
  • qty_per_product — DECIMAL(10,2) — NOT NULL
  • unit — VARCHAR(10) — NOT NULL
  • ingredient_usage_type — VARCHAR(20)

sales_history

  • sales_id — BIGINT — 기본 키 — NOT NULL
  • sales_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • sales_channel — VARCHAR(10) — NOT NULL
  • quantity_sold — INT — NOT NULL
  • unit_price_krw — INT — NOT NULL
  • revenue_krw — INT — NOT NULL
  • sold_out_flag — CHAR(1)
  • sold_out_time — TIME
  • lost_sales_estimate — INT

stockout_log

  • stockout_id — BIGINT — 기본 키 — NOT NULL
  • stockout_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • branch_id — VARCHAR(10) — 외래 키 → branch.branch_id — NOT NULL
  • product_id — VARCHAR(10) — 외래 키 → product.product_id — NOT NULL
  • sales_channel — VARCHAR(10) — NOT NULL
  • stockout_time — TIME
  • stockout_reason — VARCHAR(30)
  • lost_sales_estimate — INT

supplier

  • supplier_id — VARCHAR(10) — 기본 키 — NOT NULL
  • supplier_name — VARCHAR(30) — NOT NULL
  • supplier_type — VARCHAR(10)
  • default_allocation_ratio — DECIMAL(4,2)

weather

  • weather_date — DATE — 외래 키 → calendar.cal_date — NOT NULL
  • weather_type — VARCHAR(10) — NOT NULL
  • avg_temperature_c — DECIMAL(4,1)
  • rainfall_mm — DECIMAL(5,1)
  • humidity_pct — INT
  • is_rainy — CHAR(1) — NOT NULL