온라인 쇼핑몰 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)