온라인 쇼핑몰 주문 관리 시스템

문제풀이/데이터베이스 설계 2024. 1. 29. 17:27

테이블 설계 요구사항: 온라인 쇼핑몰 주문 관리 시스템

온라인 쇼핑몰에서 사용될 주문 관리 시스템을 위한 데이터베이스를 설계해야 합니다. 다음 요구사항을 충족해야 합니다:

고객(Customers): 고객은 이름, 이메일, 주소, 가입일을 가지고 있어야 합니다.
상품(Products): 상품은 이름, 가격, 재고 수량, 제공 업체 정보를 가지고 있어야 합니다.
주문(Orders): 주문은 주문한 고객, 주문 날짜, 배송 주소, 주문 상태(처리 중, 배송 중, 배송 완료, 취소됨 등)를 포함해야 합니다.
주문 상세(Order Details): 각 주문에는 하나 이상의 상품이 포함될 수 있습니다. 주문 상세는 주문된 상품, 수량, 그리고 각 상품에 대한 가격 정보를 포함해야 합니다.

Step1) 최초 작성

주문에 여러 개의 상품이 들어갈 수 있다고 가정하고 erd를 그려보았습니다.

CREATE TABLE IF NOT EXISTS order_status (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS customers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL, 
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    stock INT NOT NULL,
    company VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status_id BIGINT NOT NULL,
    address VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES customers(id),
    FOREIGN KEY (status_id) REFERENCES order_status(id)
);

CREATE TABLE IF NOT EXISTS order_details (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

Step2) 피드백

1. updated_at 컬럼에는 업데이시에 자동으로 현재 시간을 입력하는 옵션이 있습니다.

updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP);

2.주문 상세에 개수와 가격이 누락됨

ALTER TABLE order_details
ADD COLUMN quantity INT NOT NULL,
ADD COLUMN price INT NOT NULL;

=> 피드백 반영 sql

CREATE TABLE IF NOT EXISTS order_status (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(255) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS customers (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    email VARCHAR(255) NOT NULL UNIQUE,
    address VARCHAR(255) NOT NULL, 
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS products (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    stock INT NOT NULL,
    company VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    status_id BIGINT NOT NULL,
    address VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES customers(id),
    FOREIGN KEY (status_id) REFERENCES order_status(id)
);

CREATE TABLE IF NOT EXISTS order_details (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    product_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    price INT NOT NULL,
    quantity INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (product_id) REFERENCES products(id),
    FOREIGN KEY (order_id) REFERENCES orders(id)
);

Step3. 더미 데이터 추가

-- customers 테이블에 더미 데이터 삽입
INSERT INTO customers (email, address) VALUES 
('alice@example.com', '123 Apple St, New York'),
('bob@example.com', '456 Orange Ave, Los Angeles'),
('carol@example.com', '789 Banana Blvd, Chicago'),
('dave@example.com', '101 Cherry Lane, San Francisco'),
('eva@example.com', '202 Pine Street, Boston'),
('frank@example.com', '303 Maple Road, Austin'),
('grace@example.com', '404 Oak Lane, Seattle'),
('hank@example.com', '505 Palm Highway, Miami'),
('irene@example.com', '606 Peach Road, Atlanta'),
('jack@example.com', '707 Grape Street, Denver');

-- products 테이블에 더미 데이터 삽입
INSERT INTO products (name, price, stock, company, created_at, updated_at) VALUES 
('Laptop', 1500, 100, 'TechCorp', NOW(), NOW()),
('Smartphone', 800, 200, 'MobileInc', NOW(), NOW()),
('Headphones', 150, 300, 'SoundCo', NOW(), NOW()),
('Tablet', 600, 150, 'TechCorp', NOW(), NOW()),
('Camera', 1200, 50, 'CameraCo', NOW(), NOW()),
('Smartwatch', 250, 180, 'MobileInc', NOW(), NOW()),
('Mouse', 50, 500, 'TechCorp', NOW(), NOW()),
('Keyboard', 100, 300, 'TechCorp', NOW(), NOW()),
('Monitor', 300, 100, 'DisplayTech', NOW(), NOW()),
('Printer', 200, 80, 'PrintSolutions', NOW(), NOW());

-- order_status 테이블에 더미 데이터 삽입 (이미 존재하는 경우 생략)
INSERT INTO order_status (status) VALUES 
('Processing'),
('Shipped'),
('Delivered'),
('Cancelled');

-- orders 테이블에 더미 데이터 삽입
INSERT INTO orders (user_id, status_id, address) VALUES 
(1, 1, '123 Apple St, New York'),
(1, 2, '123 Apple St, New York'),
(2, 3, '456 Orange Ave, Los Angeles'),
(2, 2, '456 Orange Ave, Los Angeles'),
(3, 1, '789 Banana Blvd, Chicago'),
(3, 4, '789 Banana Blvd, Chicago'),
(4, 1, '101 Cherry Lane, San Francisco'),
(4, 3, '101 Cherry Lane, San Francisco'),
(5, 2, '202 Pine Street, Boston'),
(5, 3, '202 Pine Street, Boston'),
(6, 1, '303 Maple Road, Austin'),
(6, 4, '303 Maple Road, Austin'),
(7, 1, '404 Oak Lane, Seattle'),
(7, 2, '404 Oak Lane, Seattle'),
(8, 3, '505 Palm Highway, Miami'),
(8, 1, '505 Palm Highway, Miami'),
(9, 2, '606 Peach Road, Atlanta'),
(9, 3, '606 Peach Road, Atlanta'),
(10, 4, '707 Grape Street, Denver'),
(10, 1, '707 Grape Street, Denver');

-- order_details 테이블에 더미 데이터 삽입
-- 주문 상세에서 각 주문 번호(order_id)에 여러 상품을 연결합니다.
-- quantity와 price는 임의로 설정합니다 (실제 상품의 가격과는 다를 수 있음).
INSERT INTO order_details (product_id, order_id, quantity, price) VALUES 
(1, 1, 1, 1500),
(2, 1, 2, 800),
(3, 2, 3, 150),
(4, 2, 1, 600),
(5, 3, 2, 1200),
(6, 3, 1, 250),
(7, 4, 1, 50),
(8, 4, 1, 100),
(9, 5, 1, 300),
(10, 5, 1, 200),
(1, 6, 1, 1500),
(2, 6, 1, 800),
(3, 7, 1, 150),
(4, 7, 1, 600),
(5, 8, 1, 1200),
(6, 8, 1, 250),
(7, 9, 2, 50),
(8, 9, 2, 100),
(9, 10, 2, 300),
(10, 10, 2, 200),
(1, 11, 1, 1500),
(2, 11, 1, 800),
(3, 12, 1, 150),
(4, 12, 1, 600),
(5, 13, 1, 1200),
(6, 13, 1, 250),
(7, 14, 1, 50),
(8, 14, 1, 100),
(9, 15, 1, 300),
(10, 15, 1, 200),
(1, 16, 1, 1500),
(2, 16, 1, 800),
(3, 17, 1, 150),
(4, 17, 1, 600),
(5, 18, 1, 1200),
(6, 18, 1, 250),
(7, 19, 1, 50),
(8, 19, 1, 100),
(9, 20, 1, 300),
(10, 20, 1, 200);

Step4) SQL 문제

문제 1: Alice의 모든 주문 상세 조회

요구사항: 고객 Alice (ID 1)가 한 모든 주문의 상세 내역을 조회합니다. 각 주문에 대해 주문 번호, 주문 날짜, 주문 상태, 주문한 상품의 이름, 수량, 그리고 당시 가격을 표시해야 합니다.

풀이

-- goal: alice의 모든 주문 상세 내역 조회

-- step1) 주문 중에 엘리스가 주문한 주문 내역 학인
SELECT * FROM orders o WHERE o.user_id = 1;

-- step2) 주문 번호, 주문 날짜, 주문 상태, 주문 상품 이름, 수량 가격 표시하는 테이블
SELECT 
    od.order_id as order_id,
    od.created_at as order_date,
    os.status as order_status,
    p.name as product_name,
    od.quantity as order_quantity,
    od.price as order_price
FROM order_details od
INNER JOIN products p ON p.id = od.product_id
INNER JOIN orders o ON o.id = od.order_id
INNER JOIN order_status os ON os.id = o.status_id;
    
    
-- step3) 그 중에 id가 1인 경우
SELECT 
    o.user_id as customer_id,
    od.order_id as order_id,
    od.created_at as order_date,
    os.status as order_status,
    p.name as product_name,
    od.quantity as order_quantity,
    od.price as order_price
FROM order_details od
INNER JOIN products p ON p.id = od.product_id
INNER JOIN orders o ON o.id = od.order_id
INNER JOIN order_status os ON os.id = o.status_id
WHERE o.user_id IN (SELECT id FROM customers WHERE id = 1);

정답

SELECT 
    o.user_id as customer_id,
    od.order_id as order_id,
    o.created_at as order_date,  -- 여기를 주문 테이블의 created_at으로 변경
    os.status as order_status,
    p.name as product_name,
    od.quantity as order_quantity,
    od.price as order_price
FROM order_details od
INNER JOIN products p ON p.id = od.product_id
INNER JOIN orders o ON o.id = od.order_id
INNER JOIN order_status os ON os.id = o.status_id
WHERE o.user_id = 1; -- WHERE 구문을 간소화

개선안

기존 쿼리는 order_details에서 시작해서 데이터를 찾아나가서 조금 복잡한 느낌이 들었는데, order를 기준으로 찾으면 좀 더 직관적인 느낌입니다

SELECT 
    o.id AS order_id,
    o.created_at AS order_date,
    os.status AS order_status,
    o.user_id AS customer_id,
    p.name AS product_name,
    od.quantity AS quantity,
    od.price AS price
FROM orders o
INNER JOIN order_details od ON o.id = od.order_id
INNER JOIN products p ON p.id = od.product_id
INNER JOIN order_status os ON os.id = o.status_id
WHERE o.user_id = 1;

문제 2: 재고 부족 상품 조회

요구사항: 재고 수량이 10개 이하인 모든 상품의 이름, 가격, 재고 수량을 조회합니다. 이 쿼리는 재고를 재주문할 때 유용할 수 있습니다.

풀이

-- goal: 재고 수량이 10개 미만인 제품의 상세 정보

-- step1) 재고가 100개 미만인 상품 
SELECT * FROM products WHERE stock <= 10;

-- step2) 필요한 정보들 추가
SELECT name, price, stock FROM products WHERE stock <= 10;

문제 3: 가장 많이 팔린 상품 Top 5 조회

요구사항: 전체 주문에서 가장 많이 팔린 상위 5개 상품과 해당 상품들의 총 판매 수량을 조회합니다. 주문이 취소된 경우는 제외하고 계산합니다.

풀이

-- goal: 전체 주문에서 가장 많이 팔린 상품 5개와 총 판매 수량 ( 취소는 제외 )

-- step1) 주문 중에 상태가 캔슬인 경우는 제외한다.
SELECT * FROM orders o 
	INNER JOIN order_status os ON o.status_id = os.id
    WHERE os.status != 'Cancelled';

-- step2) 주문상세를 이용해서 주문에 들어있는 상품을 찾는다.
SELECT * FROM orders o 
	INNER JOIN order_details od ON o.id = od.order_id
    INNER JOIN order_status os ON o.status_id = os.id
    WHERE os.status != 'Cancelled';

-- step3) 상품 id를 기준으로 그룹바이하여 id당 Order를 찾는다.
SELECT od.product_id as product, count(*) as sales_count 
	FROM orders o 
    INNER JOIN order_details od ON o.id = od.order_id
    INNER JOIN order_status os ON o.status_id = os.id
    WHERE os.status != 'Cancelled'
    GROUP BY product 
    ORDER BY sales_count DESC 
    LIMIT 5;

정답

이 문제는 count가 아니고 수량의 합을 요구했기 때문에 sum을 사용해야했습니다.

SELECT 
    od.product_id as product, 
    SUM(od.quantity) as sales_count 
FROM orders o 
    INNER JOIN order_details od ON o.id = od.order_id
    INNER JOIN order_status os ON o.status_id = os.id
WHERE os.status != 'Cancelled'
GROUP BY product 
ORDER BY sales_count DESC 
LIMIT 5;

Step5) 추가 학습

1. 쿼리의 동작 순서

- 문제 1번의 경우 FROM > ON > JOIN > WHERE > SELECT 의 순서로 작동합니다.

- 보통 쿼리는 FROM > ON > JOIN > WHERE > SELECT > GROUP BY/DISTINCT > HAVING > ORDER BY > LIMIT/OFFSET

2. 성능 개선 고려사항

JOIN, WHERE, ORDER BY 절에 사용되는 컬럼에 인덱스를 생성하면 성능이 크게 향상될 수 있습니다.

SHOW INDEX FROM order_details;

Step6) 다음 요청 내용

- 좀 더 다양한 join 사용할 수 있는 문제

- having 절을 사용할 수 있는 문제

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

음식 배달 서비스 시스템 (0)	2024.02.01
도서관 관리 시스템 (0)	2024.01.26

ABOUT ME

단순할수록 오래간다 단순할수록 오래간다

테이블 설계 요구사항: 온라인 쇼핑몰 주문 관리 시스템

Step1) 최초 작성

Step2) 피드백

Step3. 더미 데이터 추가

Step4) SQL 문제

문제 2: 재고 부족 상품 조회

문제 3: 가장 많이 팔린 상품 Top 5 조회

Step5) 추가 학습

1. 쿼리의 동작 순서

2. 성능 개선 고려사항

Step6) 다음 요청 내용

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

티스토리툴바

ABOUT ME

테이블 설계 요구사항: 온라인 쇼핑몰 주문 관리 시스템

Step1) 최초 작성

Step2) 피드백

Step3. 더미 데이터 추가

Step4) SQL 문제

문제 2: 재고 부족 상품 조회

문제 3: 가장 많이 팔린 상품 Top 5 조회

Step5) 추가 학습

1. 쿼리의 동작 순서

2. 성능 개선 고려사항

Step6) 다음 요청 내용

'문제풀이 > 데이터베이스 설계' 카테고리의 다른 글

관련글 관련글 더보기

티스토리툴바