찍찍 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