온라인 쇼핑몰 DB

테이블 (9)

categories

  • category_id — BIGINT — 기본 키 — NOT NULL — PK
  • category_name — VARCHAR(50) — NOT NULL
  • parent_category_id — BIGINT — 외래 키 → categories.category_id — FK (self)
  • description — VARCHAR(200)

customers

  • customer_id — BIGINT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(100) — NOT NULL — UNIQUE
  • phone — VARCHAR(20)
  • address — VARCHAR(200)
  • join_date — DATETIME
  • membership_level — VARCHAR(20)

departments

  • department_id — BIGINT — 기본 키 — NOT NULL — PK
  • department_name — VARCHAR(50) — NOT NULL
  • location — VARCHAR(100)
  • manager_id — BIGINT — 외래 키 → employees.employee_id — FK

employees

  • employee_id — BIGINT — 기본 키 — NOT NULL — PK
  • name — VARCHAR(100) — NOT NULL
  • email — VARCHAR(100) — NOT NULL — UNIQUE
  • phone — VARCHAR(20)
  • hire_date — DATETIME — NOT NULL
  • position — VARCHAR(50)
  • salary — FLOAT
  • manager_id — BIGINT — 외래 키 → employees.employee_id — FK (self)
  • department_id — BIGINT — FK

order_items

  • order_item_id — BIGINT — 기본 키 — NOT NULL — PK
  • order_id — BIGINT — 외래 키 → orders.order_id — NOT NULL — FK
  • product_id — BIGINT — 외래 키 → products.product_id — NOT NULL — FK
  • quantity — INT
  • unit_price — FLOAT — NOT NULL
  • discount — FLOAT

orders

  • order_id — BIGINT — 기본 키 — NOT NULL — PK
  • customer_id — BIGINT — 외래 키 → customers.customer_id — NOT NULL — FK
  • order_date — DATETIME
  • total_amount — FLOAT
  • status — VARCHAR(20)
  • shipping_address — VARCHAR(200)
  • employee_id — BIGINT — 외래 키 → employees.employee_id — FK

product_reviews

  • review_id — BIGINT — 기본 키 — NOT NULL — PK
  • product_id — BIGINT — 외래 키 → products.product_id — NOT NULL — FK
  • customer_id — BIGINT — 외래 키 → customers.customer_id — NOT NULL — FK
  • rating — FLOAT
  • review_text — VARCHAR(1000)
  • review_date — DATETIME

products

  • product_id — BIGINT — 기본 키 — NOT NULL — PK
  • product_name — VARCHAR(100) — NOT NULL
  • category_id — BIGINT — 외래 키 → categories.category_id — FK
  • price — FLOAT — NOT NULL
  • stock_quantity — INT
  • description — VARCHAR(500)
  • supplier_id — BIGINT — 외래 키 → suppliers.supplier_id — FK

suppliers

  • supplier_id — BIGINT — 기본 키 — NOT NULL — PK
  • supplier_name — VARCHAR(100) — NOT NULL
  • contact_name — VARCHAR(100)
  • email — VARCHAR(100)
  • phone — VARCHAR(20)
  • address — VARCHAR(200)