hoi

Tables (28)

brands

  • id — BIGINT
  • name — VARCHAR(100) — NOT NULL
  • description — TEXT
  • logo_url — VARCHAR(500)
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_name
  • INDEX — idx_is_active

cart_items

  • id — BIGINT
  • cart_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • option_id — BIGINT
  • quantity — INT — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_cart_id

carts

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

categories

  • id — BIGINT
  • parent_id — BIGINT
  • name — VARCHAR(100) — NOT NULL
  • level — INT — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_parent_id
  • INDEX — idx_level
  • INDEX — idx_sort_order

coupons

  • id — BIGINT
  • event_id — BIGINT
  • name — VARCHAR(255) — NOT NULL
  • code — VARCHAR(50) — NOT NULL
  • discount_type — ENUM('FIXED', 'PERCENTAGE') — NOT NULL
  • discount_value — DECIMAL(10,2) — NOT NULL
  • min_order_amount — DECIMAL(10,2)
  • max_discount_amount — DECIMAL(10,2)
  • usage_limit — INT
  • used_count — INT
  • valid_from — DATETIME — NOT NULL
  • valid_to — DATETIME — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_code
  • INDEX — idx_valid_period

deliveries

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • status — ENUM('PREPARING', 'SHIPPING', 'DELIVERED', 'RETURNED') — NOT NULL
  • tracking_number — VARCHAR(100)
  • courier_company — VARCHAR(50)
  • shipped_at — TIMESTAMP
  • delivered_at — TIMESTAMP
  • memo — VARCHAR(500)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_order_id
  • INDEX — idx_tracking_number
  • INDEX — idx_status

events

  • id — BIGINT
  • name — VARCHAR(255) — NOT NULL
  • event_type — VARCHAR(50) — NOT NULL
  • banner_image — VARCHAR(500)
  • start_date — DATETIME — NOT NULL
  • end_date — DATETIME — NOT NULL
  • description — TEXT
  • status — ENUM('PLANNED', 'ACTIVE', 'ENDED', 'CANCELLED') — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_date_range
  • INDEX — idx_status

faqs

  • id — BIGINT
  • category — VARCHAR(100) — NOT NULL
  • question — VARCHAR(500) — NOT NULL
  • answer — TEXT — NOT NULL
  • sort_order — INT — NOT NULL
  • view_count — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_category
  • INDEX — idx_sort_order
  • INDEX — idx_is_active

inventories

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • stock_status — ENUM('IN_STOCK', 'LOW_STOCK', 'OUT_OF_STOCK') — NOT NULL
  • stock_quantity — INT — NOT NULL
  • reserved_quantity — INT — NOT NULL
  • updated_at — TIMESTAMP
  • INDEX — idx_stock_status

membership_grades

  • id — BIGINT
  • name — VARCHAR(50) — NOT NULL
  • display_name — VARCHAR(50) — NOT NULL
  • min_amount — DECIMAL(15,2) — NOT NULL
  • max_amount — DECIMAL(15,2)
  • sort_order — INT — NOT NULL
  • grade_type — ENUM('BABY', 'PINK', 'GREEN', 'BLACK', 'GOLD') — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP

notices

  • id — BIGINT
  • title — VARCHAR(500) — NOT NULL
  • content — TEXT — NOT NULL
  • is_important — BOOLEAN — NOT NULL
  • view_count — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_is_important
  • INDEX — idx_is_active
  • INDEX — idx_created_at

order_details

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • option_id — BIGINT
  • quantity — INT — NOT NULL
  • unit_price — DECIMAL(10,2) — NOT NULL
  • total_price — DECIMAL(10,2) — NOT NULL
  • discount_amount — DECIMAL(10,2)
  • product_name — VARCHAR(255) — NOT NULL
  • option_name — VARCHAR(100)
  • created_at — TIMESTAMP
  • INDEX — idx_order_id
  • INDEX — idx_product_id

orders

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • order_number — VARCHAR(50) — NOT NULL
  • delivery_type — ENUM('DELIVERY', 'PICKUP') — NOT NULL
  • order_date — TIMESTAMP
  • payment_status — ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED') — NOT NULL
  • order_status — ENUM('PENDING', 'PAID', 'PREPARING', 'SHIPPING', 'DELIVERED', 'PICKUP_READY', 'PICKUP_COMPLETED', 'CANCELLED', 'RETURNED', 'REFUNDED') — NOT NULL
  • total_amount — DECIMAL(15,2) — NOT NULL
  • shipping_fee — DECIMAL(10,2)
  • discount_amount — DECIMAL(10,2)
  • point_amount — DECIMAL(10,2)
  • total_quantity — INT — NOT NULL
  • recipient_name — VARCHAR(100)
  • recipient_phone — VARCHAR(20)
  • postal_code — VARCHAR(10)
  • address1 — VARCHAR(255)
  • address2 — VARCHAR(255)
  • delivery_memo — VARCHAR(500)
  • pickup_store_id — BIGINT
  • pickup_phone — VARCHAR(20)
  • pickup_available_at — TIMESTAMP
  • pickup_expired_at — TIMESTAMP
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_user_id
  • INDEX — idx_order_number
  • INDEX — idx_order_date
  • INDEX — idx_status
  • INDEX — idx_payment_status

payments

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • payment_method — ENUM('CARD', 'BANK_TRANSFER', 'VIRTUAL_ACCOUNT', 'SIMPLE_PAY', 'POINT') — NOT NULL
  • payment_provider — VARCHAR(50)
  • payment_key — VARCHAR(255)
  • amount — DECIMAL(15,2) — NOT NULL
  • status — ENUM('PENDING', 'COMPLETED', 'FAILED', 'CANCELLED', 'REFUNDED') — NOT NULL
  • completed_at — TIMESTAMP
  • cancelled_at — TIMESTAMP
  • fail_reason — VARCHAR(500)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_status
  • INDEX — idx_payment_key

pickups

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • store_id — BIGINT — NOT NULL
  • status — ENUM('PREPARING', 'READY', 'COMPLETED', 'EXPIRED', 'CANCELLED') — NOT NULL
  • available_at — TIMESTAMP — NOT NULL
  • expired_at — TIMESTAMP — NOT NULL
  • completed_at — TIMESTAMP
  • memo — VARCHAR(500)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_order_id
  • INDEX — idx_store_id
  • INDEX — idx_status

points

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • order_id — BIGINT
  • amount_change — DECIMAL(10,2) — NOT NULL
  • point_type — ENUM('EARN', 'USE', 'EXPIRE', 'ADJUST') — NOT NULL
  • earned_at — TIMESTAMP — NOT NULL
  • expired_at — TIMESTAMP
  • balance — DECIMAL(10,2) — NOT NULL
  • description — VARCHAR(255)
  • created_at — TIMESTAMP
  • INDEX — idx_user_id
  • INDEX — idx_expired_at

product_options

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • option_name — VARCHAR(100) — NOT NULL
  • additional_price — DECIMAL(10,2) — NOT NULL
  • sort_order — INT — NOT NULL
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_product_id

product_prices

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • price_type — ENUM('COST', 'ORIGINAL', 'SALE', 'PROMOTION') — NOT NULL
  • price — DECIMAL(10,2) — NOT NULL
  • start_date — DATE — NOT NULL
  • end_date — DATE
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_product_id
  • INDEX — idx_price_type
  • INDEX — idx_date_range

products

  • id — BIGINT
  • name — VARCHAR(255) — NOT NULL
  • description — TEXT
  • image_url — VARCHAR(500)
  • status — ENUM('ON_SALE', 'OUT_OF_STOCK', 'TEMP_OUT_OF_STOCK', 'COMING_SOON') — NOT NULL
  • brand_id — BIGINT — NOT NULL
  • category_id — BIGINT — NOT NULL
  • avg_rating — DECIMAL(3,2)
  • review_count — INT
  • sales_count — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_brand_id
  • INDEX — idx_category_id
  • INDEX — idx_status
  • INDEX — idx_avg_rating
  • INDEX — idx_created_at

promotions

  • id — BIGINT
  • event_id — BIGINT
  • name — VARCHAR(255) — NOT NULL
  • discount_type — ENUM('FIXED', 'PERCENTAGE') — NOT NULL
  • discount_value — DECIMAL(10,2) — NOT NULL
  • scope_type — ENUM('ALL', 'PRODUCT', 'CATEGORY', 'BRAND') — NOT NULL
  • target_id — BIGINT
  • min_order_amount — DECIMAL(10,2)
  • max_discount_amount — DECIMAL(10,2)
  • start_date — DATETIME — NOT NULL
  • end_date — DATETIME — NOT NULL
  • status — ENUM('ACTIVE', 'INACTIVE') — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_date_range
  • INDEX — idx_scope_target

refunds

  • id — BIGINT
  • order_id — BIGINT — NOT NULL
  • delivery_id — BIGINT
  • refund_type — ENUM('REFUND', 'RETURN', 'EXCHANGE') — NOT NULL
  • reason — VARCHAR(500) — NOT NULL
  • refund_amount — DECIMAL(15,2) — NOT NULL
  • refund_method — VARCHAR(50)
  • status — ENUM('REQUESTED', 'APPROVED', 'SHIPPING', 'INSPECTING', 'PROCESSING', 'COMPLETED', 'REJECTED') — NOT NULL
  • request_date — TIMESTAMP
  • completed_date — TIMESTAMP
  • admin_memo — VARCHAR(1000)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_order_id
  • INDEX — idx_status
  • INDEX — idx_request_date

reviews

  • id — BIGINT
  • product_id — BIGINT — NOT NULL
  • user_id — BIGINT — NOT NULL
  • order_detail_id — BIGINT
  • rating — INT — NOT NULL
  • title — VARCHAR(200)
  • content — TEXT
  • media_urls — JSON
  • is_recommend — BOOLEAN
  • helpful_count — INT
  • is_active — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_product_id
  • INDEX — idx_user_id
  • INDEX — idx_rating
  • INDEX — idx_created_at

store_inventories

  • id — BIGINT
  • store_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • stock_quantity — INT — NOT NULL
  • display_status — ENUM('DISPLAYED', 'STORED', 'OUT_OF_STOCK') — NOT NULL
  • last_received_at — TIMESTAMP
  • last_sold_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_store_id
  • INDEX — idx_product_id

stores

  • id — BIGINT
  • name — VARCHAR(255) — NOT NULL
  • address — VARCHAR(500) — NOT NULL
  • phone — VARCHAR(20)
  • operating_hours — VARCHAR(500)
  • status — ENUM('OPEN', 'TEMP_CLOSED', 'CLOSED') — NOT NULL
  • latitude — DECIMAL(10, 8)
  • longitude — DECIMAL(11, 8)
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_status
  • INDEX — idx_location

user_addresses

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • recipient_name — VARCHAR(100) — NOT NULL
  • address_name — VARCHAR(50)
  • postal_code — VARCHAR(10) — NOT NULL
  • road_address — VARCHAR(255) — NOT NULL
  • jibun_address — VARCHAR(255)
  • detail_address — VARCHAR(255)
  • is_default — BOOLEAN — NOT NULL
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_user_id
  • INDEX — idx_is_default

user_coupons

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • coupon_id — BIGINT — NOT NULL
  • status — ENUM('ISSUED', 'USED', 'EXPIRED', 'CANCELLED') — NOT NULL
  • issued_at — TIMESTAMP
  • used_at — TIMESTAMP
  • expired_at — TIMESTAMP — NOT NULL
  • order_id — BIGINT
  • INDEX — idx_user_id
  • INDEX — idx_status
  • INDEX — idx_expired_at

users

  • id — BIGINT
  • login_id — VARCHAR(50) — NOT NULL
  • password — VARCHAR(255) — NOT NULL
  • name — VARCHAR(100) — NOT NULL
  • phone — VARCHAR(20) — NOT NULL
  • birth_date — DATE
  • email — VARCHAR(100) — NOT NULL
  • gender — ENUM('M', 'F', 'OTHER')
  • status — ENUM('DORMANT', 'ACTIVE', 'WITHDRAWN') — NOT NULL
  • is_admin — BOOLEAN — NOT NULL
  • last_login_at — TIMESTAMP
  • marketing_consent — BOOLEAN — NOT NULL
  • grade_id — BIGINT
  • created_at — TIMESTAMP
  • updated_at — TIMESTAMP
  • INDEX — idx_login_id
  • INDEX — idx_status
  • INDEX — idx_email

wishlists

  • id — BIGINT
  • user_id — BIGINT — NOT NULL
  • product_id — BIGINT — NOT NULL
  • created_at — TIMESTAMP
  • INDEX — idx_user_id
  • INDEX — idx_product_id