FreshCart_v3

Tables (13)

Category (카테고리)

  • id (category_id) — INT UNSIGNED — Primary key — NOT NULL
  • name (카테고리명) — VARCHAR(30) — NOT NULL
  • parent_id (부모 카테고리 id) — INT UNSIGNED — Foreign key → Category.id

Discount (할인)

  • id (discount_id) — BIGINT UNSIGNED — Primary key — NOT NULL
  • value (할인율, 금액 모두 들어간다.) — INT UNSIGNED — NOT NULL
  • type (할인 종류 (정액, 정률)) — ENUM — NOT NULL
  • product_id — BIGINT UNSIGNED — Foreign key → Product.id — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL

Option (옵션)

  • id (OptionId) — BIG_INT_UNSIGNED — Primary key — NOT NULL
  • name (이름) — VARCHAR(100) — NOT NULL
  • price (가격) — INT SIGNED — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL
  • stock (재고) — SMALLINT UNSIGNED
  • id (OptionGroupId) — BIG INT UNSIGNED — Foreign key → OptionGroup.id — NOT NULL
  • count (최소 주문 개수) — SMALLINT UNSIGNED — NOT NULL
  • count (최대 주문 개수) — SMALLINT UNSIGNED

OptionGroup (옵션 그룹)

  • id (OptionGroupId) — BIG INT UNSIGNED — Primary key — NOT NULL
  • name (옵션 이름) — VARCHAR(100) — NOT NULL
  • default_option (기본 옵션 여부) — Boolean — NOT NULL
  • required_option (필수 옵션 여부) — Boolean — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL
  • id (product_id) — BIGINT UNSIGNED — Foreign key → Product.id — NOT NULL
  • count_option (갯수 제한 여부) — Boolean — NOT NULL

Order (주문)

  • id (8자리랜덤숫자) — BIGINT UNSIGNED — Primary key — NOT NULL
  • receiver_name (받는 사람) — VARCHAR(30) — NOT NULL
  • receiver_phone (받는 사람 연락처) — VARCHAR(15) — NOT NULL
  • recipient_address (받는 사람 주소) — VARCHAR(100) — NOT NULL
  • order_status (주문 상태) — ENUM — NOT NULL
  • created_at (주문 시점) — DATETIME — NOT NULL
  • delivered_at (배송 완료 시점) — DATETIME
  • user_id — INT UNSIGNED — Foreign key → User.id — NOT NULL

OrderLineItem (주문 상품)

  • id (item_Id) — BIGINT UNSIGNED — Primary key — NOT NULL
  • name (이름) — VARCHAR(256) — NOT NULL
  • price (가격) — INT UNSIGNED — NOT NULL
  • status (상품 상태) — ENUM — NOT NULL
  • id (8자리랜덤숫자) — BIGINT UNSIGNED — Foreign key → Order.id — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL

OrderOption (주문 - 옵션)

  • id (OptionId) — BIG_INT_UNSIGNED — Primary key — NOT NULL
  • name (이름) — VARCHAR(100) — NOT NULL
  • price (가격) — INT UNSIGNED — NOT NULL
  • id (OptionGroupId) — BIG INT UNSIGNED — Foreign key → OrderOptionGroup.id — NOT NULL
  • count (주문 갯수) — SMALLINT UNSIGNED — NOT NULL

OrderOptionGroup (주문 - 옵션그룹)

  • id (OptionGroupId) — BIG INT UNSIGNED — Primary key — NOT NULL
  • name (ex.기본) — VARCHAR(100) — NOT NULL
  • default_option (기본 옵션 여부) — Boolean — NOT NULL
  • required_option (필수 옵션 여부) — Boolean — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL
  • id (item_Id) — BIGINT UNSIGNED — Foreign key → OrderLineItem.id — NOT NULL

Payment (결제 기록)

  • id (payment_id) — BIGINT UNSIGNED — Primary key — NOT NULL
  • total_price (전체금액) — INT UNSIGNED — NOT NULL
  • discount_price (할인금액) — INT UNSIGNED — NOT NULL
  • delivery_fee (배달금액) — INT UNSIGNED — NOT NULL
  • final_price (최종금액) — INT UNSIGNED — NOT NULL
  • created_at (생성일자) — DATETIME — NOT NULL
  • payment_type_id — TINYINT UNSIGNED — Foreign key → Payment_Type.id — NOT NULL
  • order_id — BIGINT UNSIGNED — Foreign key → Order.id — NOT NULL

Payment_Type (결제 타입)

  • id (payment_type_id) — TINYINT UNSIGNED — Primary key — NOT NULL
  • name (타입 이름) — VARCHAR(30) — NOT NULL

Product (실제 상품)

  • id (product_id) — BIGINT UNSIGNED — Primary key — NOT NULL
  • name (이름) — VARCHAR(256) — NOT NULL
  • price (가격) — INT UNSIGNED — NOT NULL
  • status (상품 상태) — ENUM — NOT NULL
  • id (category_id) — INT UNSIGNED — Foreign key → Category.id — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL
  • description (제품 설명) — MEDIUMTEXT — NOT NULL

Shipping_Address (배송지 주소)

  • id (Shipping_Address_id) — BIGINT UNSIGNED — Primary key — NOT NULL
  • user_id (유저 id) — INT UNSIGNED — Foreign key → User.id — NOT NULL
  • address (주소) — VARCHAR(100) — NOT NULL — 규칙 : 주소는 최대 50자 제약 : varchar 길이 제한
  • is_default (기본배송지 여부) — BOOLEAN — NOT NULL

User (회원)

  • id (user_id) — INT UNSIGNED — Primary key — NOT NULL — Unique
  • password (비밀번호) — VARCHAR(256) — NOT NULL
  • name (이름) — VARCHAR(30) — NOT NULL
  • phone_number (핸드폰 번호) — VARCHAR(15) — NOT NULL
  • created_at (생성일시) — DATETIME — NOT NULL
  • updated_at (수정일시) — DATETIME — NOT NULL
  • email (이메일(Unique)) — VARCHAR(30) — NOT NULL
  • role (SELLER, ADMIN, USER) — ENUM — NOT NULL