mr.bean

Tables (11)

bill_of_materials

  • bom_id — int — Primary key — NOT NULL — BOM 명
  • bom_code — varchar(50) — NOT NULL — 원자재 비율
  • material_code — varchar(50) — Foreign key → raw_materials.material_code — NOT NULL — 제품코드
  • bom_name — varchar(50) — NOT NULL — 제품명
  • material_ratio — decimal(10, 2) — NOT NULL — 제품설명

finished_product_quality_controls

  • control_id — int — Primary key — NOT NULL — 생산수율
  • lot_number — varchar(200) — NOT NULL — 검사자ID
  • product_code — varchar(50) — Foreign key → products.product_code — NOT NULL — 재고ID
  • inspection_date — datetime — NOT NULL — 원자재코드
  • expiry_date — date — NOT NULL — 창고코드
  • work_order_no — varchar(20) — Foreign key → work_orders.work_order_no — NOT NULL — LOT 번호
  • quantity — int — NOT NULL — 재고수량
  • quality_status — enum('WAITING', 'INSPECTING', 'APPROVED', 'REJECTED') — NOT NULL — 단위
  • production_yield — decimal(4, 2) — NOT NULL — 입고일시
  • inspector_id — varchar(50) — Foreign key → users.user_id — NOT NULL — 유통기한

material_stocks

  • stock_id — int — NOT NULL — 제품코드
  • material_code — varchar(50) — Foreign key → raw_materials.material_code — NOT NULL — 창고코드
  • warehouse_code — varchar(50) — Foreign key → warehouses.warehouse_code — NOT NULL — LOT 번호
  • lot_number — varchar(200) — NOT NULL — 재고수량
  • quantity — int — NOT NULL — 단위
  • unit — varchar(10) — NOT NULL — 생산일시
  • receipt_date — datetime — NOT NULL

product_stocks

  • stock_id — int — NOT NULL

production_plans

  • plan_id — int — Primary key — NOT NULL — 제품코드
  • plan_no — varchar(20) — NOT NULL — BOM 코드
  • plan_type — varchar(20) — NOT NULL — 계획수량
  • plan_start_date — date — NOT NULL — 우선순위
  • plan_end_date — date — NOT NULL — 계획상태
  • product_code — varchar(50) — Foreign key → products.product_code — NOT NULL — 비고
  • bom_code — varchar(50) — Foreign key → bill_of_materials.bom_code — NOT NULL — 등록자
  • plan_quantity — int — NOT NULL — 등록일시
  • priority — int — 수정일시
  • status — enum('PLANNED', 'CONFIRMED', 'IN_PROGRESS', 'COMPLETED') — NOT NULL — 작업 ID
  • remark — text — 작업지시번호
  • created_by — varchar(50) — Foreign key → users.user_id — NOT NULL — 생산계획번호
  • created_at — timestamp — NOT NULL — 작업예정일
  • updated_at — timestamp — NOT NULL — 작업수량

products

  • product_code — varchar(50) — Primary key — NOT NULL — 계획 ID
  • product_name — varchar(50) — NOT NULL — 생산계획번호
  • product_description — text — NOT NULL — 계획유형
  • bom_id — int — Foreign key → bill_of_materials.bom_id — NOT NULL — 계획시작일

raw_material_quality_controls

  • control_id — int — Primary key — NOT NULL — 품질검사상태
  • lot_number — varchar(200) — NOT NULL — 검사자ID
  • material_code — varchar(50) — Foreign key → raw_materials.material_code — NOT NULL — 관리번호
  • warehouse_code — varchar(50) — Foreign key → warehouses.warehouse_code — NOT NULL — LOT 번호
  • inspection_date — datetime — NOT NULL — 제품코드
  • work_order_no — varchar(20) — Foreign key → work_orders.work_order_no — NOT NULL — 검사일시
  • quantity — int — NOT NULL — 유통기한
  • quality_status — enum('WAITING', 'INSPECTING', 'APPROVED', 'REJECTED') — NOT NULL — 작업지시번호
  • inspector_id — varchar(50) — Foreign key → users.user_id — NOT NULL — 검사수량

raw_materials

  • material_code — varchar(50) — Primary key — NOT NULL — 원산지
  • material_name — varchar(50) — NOT NULL — 보관방법
  • origin — varchar(50) — NOT NULL — BOM ID
  • storage_method — text — NOT NULL — BOM 코드

users

  • user_id — varchar(50) — Primary key — NOT NULL — 사용자 ID
  • user_name — varchar(50) — NOT NULL — 사용자 이름
  • email — varchar(100) — NOT NULL — 이메일
  • phone_number — varchar(20) — NOT NULL — 전화번호
  • role — enum('ADMIN', 'MANAGER', 'MEMBER') — NOT NULL — 사용자 권한(관리자/매니저/일반)
  • created_at — timestamp — NOT NULL — 생성일시
  • updated_at — timestamp — NOT NULL — 수정일시
  • created_by — varchar(50) — Foreign key → users.user_id — 등록자 ID
  • is_deleted — tinyint — NOT NULL — 삭제여부
  • password_hash — varchar(255) — NOT NULL — 암호화된 비밀번호

warehouses

  • warehouse_code — varchar(50) — Primary key — NOT NULL — 창고명
  • warehouse_name — varchar(50) — NOT NULL — 창고주소
  • warehouse_address — varchar(200) — NOT NULL — 창고설명
  • warehouse_description — text — 원자재코드

work_orders

  • work_id — int — Primary key — NOT NULL — 비고
  • work_order_no — varchar(20) — NOT NULL — 등록자
  • plan_no — varchar(20) — Foreign key → production_plans.plan_no — NOT NULL — 등록일시
  • work_plan_date — date — NOT NULL — 수정일시
  • work_quantity — int — NOT NULL — 관리번호
  • status — enum('WAITING', 'IN_PROGRESS', 'COMPLETED', 'STOPPED') — NOT NULL — LOT 번호
  • remark — text — 원자재코드
  • created_by — varchar(50) — Foreign key → users.user_id — NOT NULL — 창고코드
  • created_at — timestamp — NOT NULL — 검사일시
  • updated_at — timestamp — NOT NULL — 작업지시번호