2-2 new

Tables (27)

Announcement

  • announcement_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • writer — VARCHAR(255) — NOT NULL
  • is_important — tinyint — NOT NULL

Expense

  • expense_id — BIGINT — Primary key — NOT NULL
  • warehouse_name — VARCHAR(100) — NOT NULL
  • expense_date — DATE — NOT NULL
  • category — VARCHAR(50) — "인건비"
  • amount — BIGINT — NOT NULL
  • description — VARCHAR(255) — NOT NULL
  • reg_date — TIMESTAMP — NOT NULL
  • mod_date — TIMESTAMP — NOT NULL

Inquiry

  • inquiry_id — BIGINT — Primary key — NOT NULL
  • title — VARCHAR(255) — NOT NULL
  • content — VARCHAR(1000) — NOT NULL
  • writer — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • password — VARCHAR(30) — NOT NULL

LOCATION

  • location_id — int — Primary key — NOT NULL — 창고위치
  • warehouse_id — int — Foreign key → WAREHOUSE.warehouse_id — NOT NULL — 창고 고유 ID
  • location_code — VARCHAR(100) — NOT NULL — 위치코드
  • floor_num — int — NOT NULL — 위치 층수
  • location_type_code — VARCHAR(50) — NOT NULL — 랙 , 팔레트
  • max_volume — DECIMAL(10, 3) — 해당위치 적재가능 부피

Member

  • member_id — BIGINT — Primary key — NOT NULL — auto_increment
  • client_id — BIGINT — Foreign key → Partner.partner_id — NOT NULL — 거래처 고유 ID
  • member_login_id — VARCHAR(255) — NOT NULL
  • member_pw — VARCHAR(255) — NOT NULL
  • member_name — VARCHAR(255) — NOT NULL
  • member_phone — VARCHAR(255) — NOT NULL
  • member_email — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • role — VARCHAR(255) — NOT NULL

PARTNER_CONTRACT

  • CONTRACT_ID — int — Primary key — NOT NULL
  • partner_id — BIGINT — Foreign key → Partner.partner_id — NOT NULL — 거래처 고유 ID
  • CONTRACT_START — DATE
  • CONTRACT_AREA — DECIMAL
  • CONTRACT_STATUS — VARCHAR(255) — NOT NULL

Partner

  • partner_id — BIGINT — Primary key — NOT NULL — 거래처 고유 ID
  • partner_name — VARCHAR(100) — NOT NULL — 예: 나이키, 아디다스 ...
  • business_number — VARCHAR(20) — NOT NULL
  • address — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL

Partner_Fee

  • FEE_ID — int — Primary key — NOT NULL
  • partner_id — BIGINT — NOT NULL — 거래처 고유 ID
  • FEE_TYPE — VARCHAR(255) — NOT NULL
  • PRICE — DECIMAL — NOT NULL
  • APPLY_DATE — DATE

Physical_Inventory

  • piID — int — Primary key — NOT NULL
  • PS_ID — int — Foreign key → Product_Stock.PS_ID — NOT NULL
  • pi_date — TIMESTAMP — NOT NULL
  • pi_state — VARCHAR(30) — NOT NULL — 시작,예정, 완료, 진행중
  • pid_q — int — NOT NULL
  • real_q — int
  • different_q — int
  • update_state — VARCHAR(30) — NOT NULL

Product

  • product_id — VARCHAR(20) — Primary key — NOT NULL
  • category_cd — int — Foreign key → category.category_cd — NOT NULL
  • client_id — BIGINT — Foreign key → Partner.partner_id — NOT NULL — 거래처 고유 ID
  • product_name — VARCHAR(200) — NOT NULL
  • product_price — BIGINT
  • product_oriogin — VARCHAR(200)

Product_Stock

  • PS_ID — int — Primary key — NOT NULL
  • product_id — VARCHAR(20) — Foreign key → Product.product_id — NOT NULL
  • client_id — BIGINT — Foreign key → Partner.partner_id — NOT NULL — 거래처 고유 ID
  • inbound_id — int — Foreign key → inbound.inbound_id — NOT NULL
  • outboundRequst_ID — int — Foreign key → shipmentRequest.outboundRequst_ID — NOT NULL
  • warehouse_id — int — Foreign key → Section.warehouse_id — NOT NULL — 창고 고유 식별
  • section_id — int — Foreign key → Section.section_id — NOT NULL — 창고구역 식별 ID
  • quantity — int — NOT NULL
  • product_status — VARCHAR(50) — NOT NULL
  • last_updatedate — TIMESTAMP

QR

  • Key — VARCHAR(255) — Primary key — NOT NULL
  • waybill_id — int — Foreign key → waybill.waybill_id — NOT NULL
  • Field — VARCHAR(255)

Reply

  • reply_id — BIGINT — Primary key — NOT NULL
  • inquiry_id — BIGINT — Foreign key → Inquiry.inquiry_id — NOT NULL
  • content — VARCHAR(500) — NOT NULL
  • writer — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • password — VARCHAR(30) — NOT NULL
  • title — VARCHAR(255) — NOT NULL

Sales

  • sales_id — BIGINT — Primary key — NOT NULL
  • warehouse_name — VARCHAR(100) — NOT NULL
  • sales_date — DATE — NOT NULL
  • category — VARCHAR(50) — "월 기본료"
  • client_name — VARCHAR(100) — NOT NULL
  • amount — BIGINT — NOT NULL
  • description — VARCHAR(255) — NOT NULL
  • reg_date — TIMESTAMP — NOT NULL
  • mod_date — TIMESTAMP — NOT NULL

Section

  • section_id — int — Primary key — NOT NULL — 창고구역 식별 ID
  • warehouse_id — int — Foreign key → WAREHOUSE.warehouse_id — NOT NULL — 창고 고유 식별
  • section_name — VARCHAR(50) — NOT NULL — 구역 이름(A/B/C/D 구역)
  • section_type — VARCHAR(50) — NOT NULL — 구역의 종류
  • section_purpose — TEXT — 상세 설명
  • allocated_area — int — 구역에 할당된 면적

Staff

  • staff_id — BIGINT — Primary key — NOT NULL
  • staff_login_id — VARCHAR(255) — NOT NULL
  • staff_pw — VARCHAR(255) — NOT NULL
  • staff_name — VARCHAR(255) — NOT NULL
  • staff_phone — VARCHAR(255) — NOT NULL
  • staff_email — VARCHAR(255) — NOT NULL
  • created_at — TIMESTAMP — NOT NULL
  • updated_at — TIMESTAMP — NOT NULL
  • status — VARCHAR(20) — NOT NULL
  • role — VARCHAR(255) — NOT NULL

WAREHOUSE

  • warehouse_id — int — Primary key — NOT NULL — 창고 고유 식별
  • admin_id — BIGINT — Foreign key → Staff.staff_id — NOT NULL — 창고관리담당자ID (Staff PK와 일치시키기 위해 BIGINT로 변경)
  • name — VARCHAR(100) — NOT NULL — 창고의 이름
  • warehouse_type — VARCHAR(100) — NOT NULL — 창고의종류/유형
  • warehouse_capacity — int — 창고의 수용 용량
  • warehouse_status — tinyint — NOT NULL — 창고의 운용현황
  • registration_date — DATE — NOT NULL — 창고 정보 최초 등록일자
  • latest_update_date — TIMESTAMP — NOT NULL — 창고 정보 최종 수정일자
  • address — VARCHAR(255) — NOT NULL — 창고의 주소
  • latitude — DECIMAL(10, 7) — NOT NULL — 카카오API연동 위도
  • longitude — DECIMAL(10, 7) — NOT NULL — 카카오API연동 경도

category

  • category_cd — int — Primary key — NOT NULL
  • category_name — VARCHAR(200) — NOT NULL

dispatch

  • dispatch_ID — int — Primary key — NOT NULL
  • approvedOrder_ID — int — Foreign key → shipmentOrder.approvedOrder_ID — NOT NULL
  • carID — int
  • Cartype — VARCHAR(20)
  • driverName — VARCHAR(10)
  • assignedDate — TIMESTAMP
  • dispatchStatus — VARCHAR(10)
  • loadedBOX — int
  • maximumBOX — int

estimate

  • estimate_id — BIGINT — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • staff_id — BIGINT — Foreign key → Staff.staff_id — NOT NULL
  • is_guest — tinyint — NOT NULL — 미사용
  • guest_name — VARCHAR(20) — 미사용
  • guest_contact — VARCHAR(20) — 미사용
  • guest_email — VARCHAR(40) — 미사용
  • estimate_title — VARCHAR(200) — NOT NULL
  • estimate_content — TEXT — NOT NULL
  • estimate_status — VARCHAR(20) — NOT NULL — (대기, 검토중, 완료 등)
  • estimate_password — VARCHAR(100) — 미사용
  • estimate_response — TEXT
  • estimate_request_at — TIMESTAMP — NOT NULL
  • estimate_response_at — TIMESTAMP

inbound

  • inbound_id — int — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • warehouse_id — int — Foreign key → WAREHOUSE.warehouse_id — NOT NULL — 창고 고유 식별
  • staff_id — BIGINT — Foreign key → Staff.staff_id — NOT NULL
  • inbound_status — VARCHAR(100) — NOT NULL — (요청, 승인/취소/반려) 데이터상에는 영어로
  • inbound_reject_reason — VARCHAR(200) — 반려시 입력
  • inbound_requested_at — TIMESTAMP — NOT NULL
  • inbound_updated_at — TIMESTAMP — NOT NULL
  • inbound_at — TIMESTAMP

inbound_item

  • inbound_item_id — int — Primary key — NOT NULL
  • inbound_id — int — Foreign key → inbound.inbound_id — NOT NULL
  • product_id — VARCHAR(20) — Foreign key → Product.product_id — NOT NULL
  • amount — int — NOT NULL

product_stock_log

  • log_ID — int — Primary key — NOT NULL
  • PS_ID — int — Foreign key → Product_Stock.PS_ID — NOT NULL
  • event_time — TIMESTAMP — NOT NULL — 입고, 출고가 이루어지는 시간
  • move_quantity — int — NOT NULL — 입고면 + 출고면 -
  • event_type — VARCHAR(20) — NOT NULL — 입고, 출고 둘중에 하나
  • product_status — VARCHAR(50) — NOT NULL
  • destination — VARCHAR(30) — NOT NULL — 이벤트 타입이 입고면 출발지 이고 출고면 도착지 이다

shipmentItem

  • outboundshipment_item_id — int — Primary key — NOT NULL
  • outboundRequst_ID — int — Foreign key → shipmentRequest.outboundRequst_ID — NOT NULL
  • Product_ID — VARCHAR(20) — Foreign key → Product.product_id — NOT NULL
  • outboundQuantity — int — NOT NULL

shipmentOrder

  • approvedOrder_ID — int — Primary key — NOT NULL
  • outboundRequst_ID — int — Foreign key → shipmentRequest.outboundRequst_ID — NOT NULL
  • approvedDate — TIMESTAMP
  • instructionNo — VARCHAR(10)
  • orderStatus — VARCHAR(20)

shipmentRequest

  • outboundRequst_ID — int — Primary key — NOT NULL
  • member_id — BIGINT — Foreign key → Member.member_id — NOT NULL
  • warehouse_id — int — Foreign key → WAREHOUSE.warehouse_id — NOT NULL — 창고 고유 식별
  • staff_id — BIGINT — Foreign key → Staff.staff_id — NOT NULL
  • outboundDate — TIMESTAMP
  • approvedStatus — VARCHAR(100)
  • outboundAddress — VARCHAR(100)
  • requestedDeliveryDate — TIMESTAMP

waybill

  • waybill_id — int — Primary key — NOT NULL
  • dispatch_ID — int — Foreign key → dispatch.dispatch_ID — NOT NULL
  • waybillNumber — int
  • watbiiDate — TIMESTAMP
  • waybillStatus — VARCHAR(20)
  • departureAddress — VARCHAR(100)
  • arrivalAddress — VARCHAR(100)
  • senderName — VARCHAR(10)
  • receiverName — VARCHAR(10)