brewnet V2

Tables (45)

tbl_approval

  • approval_code — INT — Primary key — NOT NULL
  • kind — ENUM('PURCHASE', 'ORDER', 'EXCHANGE', 'RETURN') — NOT NULL
  • sequence — INT — NOT NULL — 현재는 기안자 1명, 결재자 1명으로 순번은 1고정
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • position_code — INT — Foreign key → tbl_position.position_code — NOT NULL

tbl_company

  • company_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • business_number — VARCHAR(255) — NOT NULL
  • corporate_number — VARCHAR(255)
  • ceo_name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • type_of_business — VARCHAR(255) — NOT NULL
  • contact — VARCHAR(255) — NOT NULL
  • date_of_establishment — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_correspondent

  • correspondent_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • detail_address — VARCHAR(255)
  • email — VARCHAR(255)
  • contact — VARCHAR(255)
  • manager_name — VARCHAR(255)
  • active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

tbl_correspondent_item

  • correspondent_code — INT — Foreign key → tbl_correspondent.correspondent_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_exchange

  • exchange_code — INT — Primary key — NOT NULL
  • comment — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • reason — ENUM('DAMAGED', 'DEFECTIVE', 'OTHER') — NOT NULL — 파손,품질불량,기타
  • explanation — VARCHAR(255) — NOT NULL
  • approval_status — ENUM('UNCONFIRMED', 'CANCELED', 'APPROVED', 'REJECTED') — NOT NULL — 결재 확인되지 않음, 결재 취소, 결재 승인, 결재 반려
  • order_code — INT — Foreign key → tbl_order.order_code — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code
  • delivery_code — INT — Foreign key → tbl_member.member_code
  • drafter_approved — ENUM('APPROVE', 'REJECT', 'NONE') — NOT NULL — 승인, 반려, 미정
  • sum_price — INT — NOT NULL

tbl_exchange_approver

  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • exchange_code — INT — Foreign key → tbl_exchange.exchange_code — NOT NULL
  • approved — ENUM('APPROVED', 'UNCONFIRMED', 'REJECTED') — NOT NULL — 승인, 미확인, 반려
  • created_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • comment — VARCHAR(255)
  • active — BOOLEAN — NOT NULL

tbl_exchange_img

  • exchange_img_code — INT — Primary key — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • exchange_code — INT — Foreign key → tbl_exchange.exchange_code — NOT NULL

tbl_exchange_item

  • exchange_code — INT — Foreign key → tbl_exchange.exchange_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • quantity — INT — NOT NULL

tbl_exchange_item_status

  • exchange_stock_history_code — INT — Foreign key → tbl_exchange_stock_history.exchange_stock_history_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • quantity — INT — NOT NULL
  • restock_quantity — INT — NOT NULL

tbl_exchange_status_history

  • exchange_status_history_code — INT — Primary key — NOT NULL
  • status — ENUM('REQUESTED', 'PENDING', 'CANCELED', 'APPROVED', 'REJECTED', 'PICKING', 'PICKED', 'SHIPPING', 'SHIPPED', 'COMPLETED') — NOT NULL — 교환요청, 진행중, 교환취소, 교환승인, 교환 반려, 수거중, 수거완료, 배송중, 배송완료,교환완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • exchange_code — INT — Foreign key → tbl_exchange.exchange_code — NOT NULL

tbl_exchange_stock_history

  • exchange_stock_history_code — INT — Primary key — NOT NULL
  • status — ENUM('TOTAL_INBOUND', 'TOTAL_DISPOSAL', 'PARTIAL_INBOUND') — NOT NULL — 전체입고,전체폐기, 부분입고
  • manager — VARCHAR(255) — NOT NULL
  • comment — VARCHAR(255)
  • confirmed — ENUM('CONFIRMED', 'UNCONFIRMED') — NOT NULL — 미처리, 처리완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • exchange_code — INT — Foreign key → tbl_exchange.exchange_code — NOT NULL

tbl_franchise

  • franchise_code — INT — Primary key — NOT NULL
  • franchise_name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • detail_address — VARCHAR(255) — NOT NULL
  • city — VARCHAR(255) — NOT NULL
  • contact — VARCHAR(255) — NOT NULL
  • business_number — VARCHAR(255) — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_franchise_mandatory_purchase

  • franchise_mandatory_purchase_code — INT — Primary key — NOT NULL
  • quantity — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • franchise_code — INT — Foreign key → tbl_franchise.franchise_code — NOT NULL
  • mandatory_purchase_code — INT — Foreign key → tbl_mandatory_purchase.mandatory_purchase_code — NOT NULL

tbl_franchise_member

  • franchise_member_code — INT — Primary key — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • franchise_code — INT — Foreign key → tbl_franchise.franchise_code — NOT NULL

tbl_item

  • item_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • purchase_price — INT — NOT NULL
  • selling_price — INT — NOT NULL
  • image_url — VARCHAR(500)
  • safety_stock — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • item_unique_code — VARCHAR(255) — NOT NULL
  • category_code — INT — Foreign key → tbl_sub_category.sub_category_code — NOT NULL

tbl_letter_of_purchase

  • letter_of_purchase_code — INT — Primary key — NOT NULL — 구매품의서는 발주서의 모든 정보를 포함함
  • comment — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • approved — ENUM('APPROVED', 'UNCONFIRMED', 'REJECTED') — NOT NULL
  • sum_price — INT — NOT NULL
  • correspondent_code — INT — Foreign key → tbl_correspondent.correspondent_code — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • storage_code — INT — Foreign key → tbl_storage.storage_code — NOT NULL
  • seal_code — INT — Foreign key → tbl_seal.seal_code

tbl_letter_of_purchase_approver

  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • letter_of_purchase_code — INT — Foreign key → tbl_letter_of_purchase.letter_of_purchase_code — NOT NULL
  • approved — ENUM('APPROVED', 'UNCONFIRMED', 'REJECTED') — NOT NULL — 승인, 미확인, 반려
  • approved_at — DATETIME
  • active — BOOLEAN — NOT NULL
  • comment — VARCHAR(255)

tbl_letter_of_purchase_item

  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • letter_of_purchase_code — INT — Foreign key → tbl_letter_of_purchase.letter_of_purchase_code — NOT NULL — 구매품의서는 발주서의 모든 정보를 포함함
  • quantity — INT — NOT NULL
  • storage_confirmed — BOOLEAN — NOT NULL — default FALSE

tbl_mandatory_purchase

  • mandatory_purchase_code — INT — Primary key — NOT NULL
  • min_quantity — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • due_date — DATETIME — NOT NULL — 해당 날짜까지 주문해야 함

tbl_member

  • member_code — INT — Primary key — NOT NULL
  • id — VARCHAR(255) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • email — VARCHAR(255) — NOT NULL
  • contact — VARCHAR(255) — NOT NULL
  • signature_url — VARCHAR(500)
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • position_code — INT — Foreign key → tbl_position.position_code

tbl_member_role

  • role_code — INT — Foreign key → tbl_role.role_code — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_notice

  • notice_code — INT — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — VARCHAR(1024) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL

tbl_notice_img

  • notice_img_code — INT — Primary key — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • notice_code — INT — Foreign key → tbl_notice.notice_code — NOT NULL

tbl_order

  • order_code — INT — Primary key — NOT NULL
  • comment — VARCHAR(255) — 누가 작성하는가? -> 주문담당자
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • approval_status — ENUM('UNCONFIRMED', 'CANCELED', 'APPROVED', 'REJECTED') — NOT NULL — 결재 확인되지 않음, 결재 취소, 결재 승인, 결재 반려
  • drafter_approved — ENUM('APPROVE', 'REJECT', 'NONE') — NOT NULL — 승인, 반려, 미정
  • sum_price — INT — NOT NULL
  • franchise_code — INT — Foreign key → tbl_franchise.franchise_code — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code
  • delivery_code — INT — Foreign key → tbl_member.member_code

tbl_order_approver

  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • order_code — INT — Foreign key → tbl_order.order_code — NOT NULL
  • approved — ENUM('APPROVED', 'UNCONFIRMED', 'REJECTED') — NOT NULL — 승인, 미확인, 반려
  • created_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • comment — VARCHAR(255)
  • active — BOOLEAN — NOT NULL

tbl_order_item

  • order_code — INT — Foreign key → tbl_order.order_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • quantity — INT — NOT NULL
  • available — ENUM('AVAILABLE', 'UNAVAILABLE') — NOT NULL
  • part_sum_price — INT — NOT NULL — 주문 당시 품목에 대한 결제 금액(단일 품목 가격 * 주문수량)

tbl_order_print

  • order_print_code — INT — Primary key — NOT NULL
  • reason — VARCHAR(500) — NOT NULL
  • printed_at — DATETIME — NOT NULL — 생성일시와 같은 말
  • active — BOOLEAN — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • letter_of_purchase_code — INT — Foreign key → tbl_letter_of_purchase.letter_of_purchase_code — NOT NULL — 구매품의서는 발주서의 모든 정보를 포함함

tbl_order_status_history

  • order_status_history_code — INT — Primary key — NOT NULL
  • status — ENUM('REQUESTED', 'PENDING', 'CANCELED', 'APPROVED', 'REJECTED', 'SHIPPING', 'SHIPPED') — NOT NULL — 주문요청, 주문취소, 주문승인, 주문 반려, 배송중, 배송완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • order_code — INT — Foreign key → tbl_order.order_code — NOT NULL

tbl_position

  • position_code — INT — Primary key — NOT NULL
  • name — ENUM('STAFF', 'ASSISTANT_MANAGER', 'MANAGER', 'CEO') — NOT NULL — 사원, 대리, 과장, 대표이사
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_purchase_status_history

  • purchase_status_history_code — INT — Primary key — NOT NULL
  • status — ENUM('REQUESTED', 'CANCELED', 'APPROVED', 'REJECTED') — NOT NULL — 결재요청, 결재요청취소, 발주승인, 발주반려
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • letter_of_purchase_code — INT — Foreign key → tbl_letter_of_purchase.letter_of_purchase_code — NOT NULL — 구매품의서는 발주서의 모든 정보를 포함함

tbl_refund_item_status

  • return_refund_history_code — INT — Foreign key → tbl_return_refund_history.return_refund_history_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • completed — BOOLEAN — NOT NULL

tbl_return

  • return_code — INT — Primary key — NOT NULL
  • comment — VARCHAR(255)
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • reason — ENUM('DAMAGED', 'DEFECTIVE', 'MIND_CHANGE', 'OTHER') — NOT NULL — 파손, 품질불량, 단순변심, 기타
  • explanation — VARCHAR(255) — NOT NULL
  • approval_status — ENUM('UNCONFIRMED', 'CANCELED', 'APPROVED', 'REJECTED') — NOT NULL — 결재 확인되지 않음, 결재 취소, 결재 승인, 결재 반려
  • order_code — INT — Foreign key → tbl_order.order_code — NOT NULL
  • member_code — INT — Foreign key → tbl_member.member_code
  • delivery_code — INT — Foreign key → tbl_member.member_code
  • drafter_approved — ENUM('APPROVE', 'REJECT', 'NONE') — NOT NULL — 승인, 반려, 미정
  • sum_price — INT — NOT NULL

tbl_return_approver

  • member_code — INT — Foreign key → tbl_member.member_code — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL
  • approved — ENUM('APPROVED', 'UNCONFIRMED', 'REJECTED') — NOT NULL — 승인, 미확인, 반려
  • created_at — DATETIME
  • updated_at — DATETIME — NOT NULL
  • comment — VARCHAR(255)
  • active — BOOLEAN — NOT NULL

tbl_return_img

  • return_img_code — INT — Primary key — NOT NULL
  • imag_url — VARCHAR(500) — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL

tbl_return_item

  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL
  • quantity — INT — NOT NULL

tbl_return_item_status

  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • return_stock_history_code — INT — Foreign key → tbl_return_stock_history.return_stock_history_code — NOT NULL
  • quantity — INT — NOT NULL
  • restock_quantity — INT — NOT NULL

tbl_return_refund_history

  • return_refund_history_code — INT — Primary key — NOT NULL
  • status — ENUM('TOTAL_REFUND', 'PARTIAL_REFUND', 'NON_REFUNDABLE') — NOT NULL — 전체환불, 부분환불, 환불불가
  • manager — VARCHAR(255) — NOT NULL
  • comment — VARCHAR(255)
  • confirmed — ENUM('CONFIRMED', 'UNCONFIRMED') — NOT NULL — 미처리, 처리완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL

tbl_return_status_history

  • return_status_history_code — INT — Primary key — NOT NULL
  • status — ENUM('REQUESTED', 'PENDING', 'CANCELED', 'APPROVED', 'REJECTED', 'PICKING', 'PICKED', 'COMPLETED') — NOT NULL — 반품요청, 반품취소, 반품승인, 반품반려, 수거중, 수거완료, 반품완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL

tbl_return_stock_history

  • return_stock_history_code — INT — Primary key — NOT NULL
  • status — ENUM('TOTAL_INBOUND', 'PARTIAL_INBOUND', 'TOTAL_DISPOSAL') — NOT NULL — 전체입고, 부분입고, 전체폐기
  • manager — VARCHAR(255) — NOT NULL
  • comment — VARCHAR(255)
  • confirmed — ENUM('CONFIRMED', 'UNCONFIRMED') — NOT NULL — 미처리, 처리완료
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • return_code — INT — Foreign key → tbl_return.return_code — NOT NULL

tbl_role

  • role_code — INT — Primary key — NOT NULL
  • role — ENUM('ROLE_MASTER', 'ROLE_GENERAL_ADMIN', 'ROLE_RESPONSIBLE_ADMIN', 'ROLE_FRANCHISE', 'ROLE_DELIVERY') — NOT NULL — 마스터, 일반 관리자, 책임 관리자, 가맹점, 배송기사)
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_seal

  • seal_code — INT — Primary key — NOT NULL
  • image_url — VARCHAR(500) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • company_code — INT — Foreign key → tbl_company.company_code — NOT NULL

tbl_stock

  • storage_code — INT — Foreign key → tbl_storage.storage_code — NOT NULL
  • item_code — INT — Foreign key → tbl_item.item_code — NOT NULL
  • available_stock — INT — NOT NULL
  • out_stock — INT — NOT NULL
  • in_stock — INT — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL

tbl_storage

  • storage_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • contact — VARCHAR(255) — NOT NULL
  • active — BOOLEAN — NOT NULL
  • created_at — DATETIME — NOT NULL

tbl_sub_category

  • sub_category_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL
  • super_category_code — INT — Foreign key → tbl_super_category.super_category_code — NOT NULL

tbl_super_category

  • super_category_code — INT — Primary key — NOT NULL
  • name — VARCHAR(255) — NOT NULL
  • created_at — DATETIME — NOT NULL
  • active — BOOLEAN — NOT NULL