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)