Core

Tables (49)

annual_leave

  • leave_id — INT
  • emp_id — INT
  • year — INT — NOT NULL
  • total_days — INT — NOT NULL
  • used_days — INT — NOT NULL
  • created_at — DATETIME

appr_log

  • log_id — INT
  • emp_id — INT
  • log_type — INT — NOT NULL
  • log_content — TEXT — NOT NULL
  • log_date — DATETIME — NOT NULL
  • created_at — DATETIME

attendance

  • attendance_id — INT
  • emp_id — INT
  • attendance_date — DATE — NOT NULL
  • check_in — DATETIME
  • check_out — DATETIME
  • attendance_status — INT — NOT NULL
  • created_at — DATETIME

category

  • category_id — INT
  • category_name — VARCHAR(30) — NOT NULL
  • category_status — INT — NOT NULL
  • created_at — DATETIME

category_sales_stats

  • stats_id — INT
  • category_id — INT
  • stats_date — DATE — NOT NULL
  • total_sales — INT — NOT NULL
  • created_at — DATETIME

chat_message

  • message_id — INT
  • room_id — INT
  • emp_id — INT
  • message_content — TEXT — NOT NULL
  • sent_at — DATETIME — NOT NULL
  • created_at — DATETIME

chat_room

  • room_id — INT
  • room_name — VARCHAR(50) — NOT NULL
  • room_type — INT — NOT NULL
  • created_at — DATETIME

dashboard_layout

  • layout_id — INT
  • emp_id — INT
  • layout_data — TEXT — NOT NULL
  • created_at — DATETIME

demand_prediction

  • prediction_id — INT
  • product_id — INT
  • prediction_date — DATE — NOT NULL
  • predicted_quantity — INT — NOT NULL
  • created_at — DATETIME

display_location

  • location_id — INT
  • store_id — INT
  • location_name — VARCHAR(50) — NOT NULL
  • location_type — INT — NOT NULL
  • created_at — DATETIME

disposal

  • disposal_id — INT
  • product_id — INT
  • disposal_date — DATETIME — NOT NULL
  • quantity — INT — NOT NULL
  • reason — TEXT
  • created_at — DATETIME

email_token

  • token_id — INT
  • emp_id — INT
  • token — VARCHAR(100) — NOT NULL
  • expiry_date — DATETIME — NOT NULL
  • created_at — DATETIME

email_verification

  • verification_id — INT
  • emp_id — INT
  • email — VARCHAR(100) — NOT NULL
  • verification_code — VARCHAR(6) — NOT NULL
  • created_at — DATETIME

employee

  • emp_id — INT
  • store_id — INT
  • depart_id — INT
  • emp_name — VARCHAR(30) — NOT NULL
  • emp_role — VARCHAR(30) — NOT NULL
  • emp_gender — INT — NOT NULL
  • emp_phone — VARCHAR(30) — NOT NULL
  • emp_addr — VARCHAR(30) — NOT NULL
  • emp_birth — VARCHAR(30) — NOT NULL
  • login_id — VARCHAR(30) — NOT NULL
  • login_pwd — VARCHAR(225) — NOT NULL
  • emp_img — VARCHAR(255)
  • emp_bank — INT — NOT NULL
  • emp_acount — VARCHAR(30) — NOT NULL
  • emp_status — VARCHAR(30) — NOT NULL
  • hire_date — DATETIME — NOT NULL
  • work_type — INT — NOT NULL
  • email_auth — INT
  • emp_ext — INT
  • created_at — DATETIME

hq_settlement

  • settlement_id — INT
  • store_id — INT
  • settlement_date — DATE — NOT NULL
  • total_amount — INT — NOT NULL
  • settlement_status — INT — NOT NULL
  • created_at — DATETIME

hq_stock

  • hq_stock_id — INT
  • product_id — INT
  • stock_quantity — INT — NOT NULL
  • stock_status — INT — NOT NULL
  • created_at — DATETIME

inventory_statistics

  • stats_id — INT
  • store_id — INT
  • stats_date — DATE — NOT NULL
  • total_products — INT — NOT NULL
  • total_value — INT — NOT NULL
  • created_at — DATETIME

leave_req

  • leave_id — INT
  • emp_id — INT
  • leave_start_date — DATE — NOT NULL
  • leave_end_date — DATE — NOT NULL
  • leave_type — INT — NOT NULL
  • leave_status — INT — NOT NULL
  • leave_reason — TEXT
  • created_at — DATETIME

notification

  • notification_id — INT
  • emp_id — INT
  • notification_type — INT — NOT NULL
  • notification_content — TEXT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • is_read — BOOLEAN

order_stats

  • stats_id — INT
  • store_id — INT
  • stats_date — DATE — NOT NULL
  • total_orders — INT — NOT NULL
  • created_at — DATETIME

part_timer

  • part_timer_id — INT
  • emp_id — INT
  • hourly_rate — INT — NOT NULL
  • max_hours_per_week — INT — NOT NULL
  • created_at — DATETIME

product

  • product_id — INT
  • category_id — INT
  • product_name — VARCHAR(225) — NOT NULL
  • product_code — VARCHAR(30) — NOT NULL
  • product_price — INT — NOT NULL
  • product_status — INT — NOT NULL
  • product_img — VARCHAR(255)
  • product_desc — TEXT
  • created_at — DATETIME

product_details

  • detail_id — INT
  • product_id — INT
  • detail_name — VARCHAR(225) — NOT NULL
  • detail_value — TEXT — NOT NULL
  • created_at — DATETIME

product_location_mapping

  • mapping_id — INT
  • product_id — INT
  • location_code — VARCHAR(30) — NOT NULL
  • created_at — DATETIME

purchase_order

  • order_id — INT
  • store_id — INT
  • order_date — DATETIME — NOT NULL
  • order_status — INT — NOT NULL
  • created_at — DATETIME

purchase_order_item

  • item_id — INT
  • order_id — INT
  • product_id — INT
  • quantity — INT — NOT NULL
  • unit_price — INT — NOT NULL
  • created_at — DATETIME

pw_reset_token

  • token_id — INT
  • emp_id — INT
  • token — VARCHAR(100) — NOT NULL
  • expiry_date — DATETIME — NOT NULL
  • created_at — DATETIME

salary

  • salary_id — INT
  • emp_id — INT
  • salary_date — DATE — NOT NULL
  • base_salary — INT — NOT NULL
  • overtime_pay — INT — NOT NULL
  • bonus — INT — NOT NULL
  • deductions — INT — NOT NULL
  • net_salary — INT — NOT NULL
  • created_at — DATETIME

sales_detail

  • detail_id — INT
  • transaction_id — INT
  • product_id — INT
  • quantity — INT — NOT NULL
  • unit_price — INT — NOT NULL
  • created_at — DATETIME

sales_hourly

  • hourly_id — INT
  • store_id — INT
  • sales_date — DATE — NOT NULL
  • hour — INT — NOT NULL
  • sales_amount — INT — NOT NULL
  • created_at — DATETIME

sales_settle

  • settle_id — INT
  • store_id — INT
  • settle_date — DATE — NOT NULL
  • total_sales — INT — NOT NULL
  • cash_amount — INT — NOT NULL
  • card_amount — INT — NOT NULL
  • settle_status — INT — NOT NULL
  • created_at — DATETIME

sales_statistics

  • stats_id — INT
  • store_id — INT
  • stats_date — DATE — NOT NULL
  • total_sales — INT — NOT NULL
  • total_transactions — INT — NOT NULL
  • avg_transaction_amount — INT — NOT NULL
  • created_at — DATETIME

sales_stats

  • stats_id — INT
  • store_id — INT
  • stats_date — DATE — NOT NULL
  • total_sales — INT — NOT NULL
  • created_at — DATETIME

sales_transaction

  • transaction_id — INT
  • store_id — INT
  • emp_id — INT
  • transaction_date — DATETIME — NOT NULL
  • total_amount — INT — NOT NULL
  • payment_method — INT — NOT NULL
  • transaction_status — INT — NOT NULL
  • created_at — DATETIME

shift_schedule

  • schedule_id — INT
  • emp_id — INT
  • schedule_date — DATE — NOT NULL
  • start_time — TIME — NOT NULL
  • end_time — TIME — NOT NULL
  • created_at — DATETIME

stock_adjust_log

  • adjust_id — INT
  • product_id — INT
  • adjust_quantity — INT — NOT NULL
  • adjust_reason — TEXT
  • adjust_date — DATETIME — NOT NULL
  • created_at — DATETIME

stock_flow

  • flow_id — INT
  • product_id — INT
  • flow_type — INT — NOT NULL
  • quantity — INT — NOT NULL
  • flow_date — DATETIME — NOT NULL
  • reference_id — INT
  • created_at — DATETIME

stock_in_history

  • history_id — INT
  • product_id — INT
  • quantity — INT — NOT NULL
  • in_date — DATETIME — NOT NULL
  • in_type — INT — NOT NULL
  • created_at — DATETIME

stock_inventory_check

  • check_id — INT
  • store_id — INT
  • check_date — DATETIME — NOT NULL
  • check_status — INT — NOT NULL
  • created_at — DATETIME

stock_inventory_check_item

  • check_item_id — INT
  • check_id — INT
  • product_id — INT
  • system_quantity — INT — NOT NULL
  • actual_quantity — INT — NOT NULL
  • created_at — DATETIME

stock_transfer

  • transfer_id — INT
  • from_store_id — INT
  • to_store_id — INT
  • transfer_date — DATETIME — NOT NULL
  • transfer_status — INT — NOT NULL
  • created_at — DATETIME

store

  • store_id — INT
  • store_name — VARCHAR(225) — NOT NULL
  • store_addr — VARCHAR(225) — NOT NULL
  • store_tel — VARCHAR(30) — NOT NULL
  • store_created_at — DATETIME — NOT NULL
  • store_status — INT — NOT NULL — 1: 영업중, 2: 휴업, 3: 폐업
  • created_at — DATETIME

store_inquiry

  • inquiry_id — INT
  • store_id — INT
  • inquiry_type — INT — NOT NULL
  • inquiry_content — TEXT — NOT NULL
  • inquiry_status — INT — NOT NULL
  • created_at — DATETIME

store_stock

  • stock_id — INT
  • store_id — INT
  • product_id — INT
  • stock_quantity — INT — NOT NULL
  • stock_status — INT — NOT NULL
  • created_at — DATETIME

tbl_board_comments

  • comment_id — INT
  • post_id — INT
  • emp_id — INT
  • comment_content — TEXT — NOT NULL
  • comment_date — DATETIME — NOT NULL
  • created_at — DATETIME

tbl_board_posts

  • post_id — INT
  • emp_id — INT
  • post_title — VARCHAR(100) — NOT NULL
  • post_content — TEXT — NOT NULL
  • post_date — DATETIME — NOT NULL
  • created_at — DATETIME

verified_device

  • device_id — INT
  • emp_id — INT
  • device_info — VARCHAR(255) — NOT NULL
  • last_login — DATETIME — NOT NULL
  • created_at — DATETIME

warehouse_stock

  • warehouse_stock_id — INT
  • product_id — INT
  • stock_quantity — INT — NOT NULL
  • stock_status — INT — NOT NULL
  • created_at — DATETIME

부서 (department)

  • depart_id — INT
  • depart_name — VARCHAR(30) — NOT NULL
  • created_at — DATETIME