음식 배달 서비스 시스템

문제풀이/데이터베이스 설계 2024. 2. 1. 23:51

테이블 설계 요구사항 : 음식 배달 서비스 시스템

음식 배달 서비스는 사용자들이 온라인으로 음식을 주문하고, 지역 레스토랑에서 배달을 받을 수 있는 서비스입니다. 이 시스템은 고객, 레스토랑, 주문, 주문 상세, 배달원 등 여러 엔터티를 포함합니다.

고객(Customers): 고객은 이름, 이메일, 주소, 가입일 등의 정보를 가집니다.
레스토랑(Restaurants): 각 레스토랑은 이름, 주소, 연락처, 평균 평점 등의 정보를 가집니다.
음식 메뉴(FoodItems): 음식 메뉴는 해당 레스토랑, 음식 이름, 가격, 설명 등의 정보를 포함합니다.
주문(Orders): 주문은 주문한 고객, 주문한 레스토랑, 주문 날짜, 총 가격, 배달 상태(처리 중, 배달 중, 배달 완료 등)를 포함합니다.
주문 상세(OrderDetails): 각 주문에는 하나 이상의 음식 메뉴가 포함될 수 있습니다. 주문 상세는 주문된 음식 메뉴, 수량, 각 메뉴에 대한 가격 정보를 포함합니다.
배달원(DeliveryAgents): 배달원은 이름, 연락처, 현재 상태(예: 배달 가능, 배달 중) 등의 정보를 가집니다.
배달 상태(DeliveryStatus): 각 주문의 배달 상태를 추적합니다. 배달 상태는 주문 번호, 배달원, 배달 시작 시간, 배달 완료 시간 등의 정보를 포함할 수 있습니다.

Step1) 최초 작성

use derd;

CREATE TABLE IF NOT EXISTS restaurants (
	restaurant_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    contact VARCHAR(255) NOT NULL,
    avg_rate DECIMAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
	user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    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 agent_status (
	agent_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status 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 agents (
	agent_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    contact VARCHAR(255) NOT NULL,
    agent_status_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    FOREIGN KEY (agent_status_id) REFERENCES agent_status(agent_status_id)
);

CREATE TABLE IF NOT EXISTS food_items (
	food_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

CREATE TABLE IF NOT EXISTS order_status (
	order_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
	order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    restaurant_id BIGINT NOT NULL,
    order_status_id BIGINT NOT NULL,
    total_price INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES customers(user_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id),
    FOREIGN KEY (order_status_id) REFERENCES order_status(order_status_id)
);

CREATE TABLE IF NOT EXISTS order_details (
	detail_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    food_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    count INT NOT NULL,
    price INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (food_id) REFERENCES food_items(food_item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE IF NOT EXISTS delivery_status (
	delivery_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    agent_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    finished_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Step2) 피드백

1. 참조되는 테이블과 사용하는 측에서 일관된 id 사용하는 것이 좋다.

// 기존 food_id <=> food_item_id   ==> food_item_id <=> food_item_id

CREATE TABLE IF NOT EXISTS order_details (
	detail_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    food_item_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    count INT NOT NULL,
    price INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (food_item_id) REFERENCES food_items(food_item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

2. delivery_status에서 finished_at 컬럼에 기본값을 넣는것은 바람직하지 않다.

CREATE TABLE IF NOT EXISTS delivery_status (
	delivery_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    agent_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    finished_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

최종쿼리

CREATE TABLE IF NOT EXISTS restaurants (
	restaurant_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    address VARCHAR(255) NOT NULL,
    contact VARCHAR(255) NOT NULL,
    avg_rate DECIMAL NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS customers (
	user_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    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 agent_status (
	agent_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status 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 agents (
	agent_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(255) NOT NULL,
    contact VARCHAR(255) NOT NULL,
    agent_status_id BIGINT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    FOREIGN KEY (agent_status_id) REFERENCES agent_status(agent_status_id)
);

CREATE TABLE IF NOT EXISTS food_items (
	food_item_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    restaurant_id BIGINT NOT NULL,
    name VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    description VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id)
);

CREATE TABLE IF NOT EXISTS order_status (
	order_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    status VARCHAR(255) NOT NULL UNIQUE,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE IF NOT EXISTS orders (
	order_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    restaurant_id BIGINT NOT NULL,
    order_status_id BIGINT NOT NULL,
    total_price INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (user_id) REFERENCES customers(user_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id),
    FOREIGN KEY (order_status_id) REFERENCES order_status(order_status_id)
);

CREATE TABLE IF NOT EXISTS order_details (
	detail_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    food_item_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    count INT NOT NULL,
    price INT NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (food_item_id) REFERENCES food_items(food_item_id),
    FOREIGN KEY (order_id) REFERENCES orders(order_id)
);

CREATE TABLE IF NOT EXISTS delivery_status (
	delivery_status_id BIGINT PRIMARY KEY AUTO_INCREMENT,
    agent_id BIGINT NOT NULL,
    order_id BIGINT NOT NULL,
    finished_at DATETIME,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

Step3 문제풀이

문제 1: 고객별 주문 히스토리 조회

요구사항: 고객 ID 1 의 모든 주문 히스토리를 조회합니다. 각 주문에 대해 주문 번호, 주문 날짜, 주문한 레스토랑 이름, 주문 상태, 총 가격을 표시해야 합니다.

SELECT 
    o.order_id as order_num,
    o.created_at as order_date,
    r.name as restaurant_name,
    os.status as order_status,
    o.total_price
FROM orders o
INNER JOIN restaurants r ON r.restaurant_id =  o.restaurant_id
INNER JOIN order_status os ON os.order_status_id = o.order_status_id
WHERE o.user_id = 1;

문제 2: 특정 레스토랑의 가장 인기 있는 음식 메뉴 조회

요구사항: 레스토랑 ID 1의 가장 인기 있는 음식 메뉴 Top 3를 조회합니다. 음식 메뉴의 인기도는 해당 메뉴의 총 판매 수량으로 결정됩니다. 레스토랑 ID를 입력으로 사용합니다.

문제를 해결할 때, food_items, order_details, orders, order_status 테이블을 적절히 조인하여 필요한 정보를 조회하고, 취소된 주문은 제외하며, 판매 수량을 기준으로 상위 3개의 메뉴를 선택하시면 됩니다. 문제를 풀어보시고, 결과를 검토해보세요. 추가적인 질문이나 도움이 필요하면 언제든지 말씀해 주세요!

SELECT od.food_item_id, sum(od.count) AS ranking FROM orders o
    INNER JOIN order_status os ON o.order_status_id = os.order_status_id
    INNER JOIN order_details od ON o.order_id = od.order_id
    WHERE os.status != 'Cancelled'
    GROUP BY od.food_item_id
    ORDER BY ranking DESC
    LIMIT 3;

Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'derd.o.order_id' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

중간에 이런 에러를 만났는데, 이건 GROUP BY를 할 때, group by 할 수 없는 컬럼들이 조회 대상에 포함되어 있을 때 발생합니다.

// Error Code: 1055. Expression #1 of SELECT list is not in GROUP BY clause and 
// contains nonaggregated column 'derd.o.order_id' which is not functionally 
// dependent on columns in GROUP BY clause; this is incompatible with 
// sql_mode=only_full_group_by


SELECT * FROM orders o
    INNER JOIN order_status os ON o.order_status_id = os.order_status_id
    INNER JOIN order_details od ON o.order_id = od.order_id
    WHERE os.status != 'Cancelled'
    GROUP BY od.food_item_id
    ORDER BY ranking DESC
    LIMIT 3;

문제 3: 배달 완료된 주문 중 총 주문 가격이 특정 금액 이상인 주문 조회

요구사항: 배달이 완료된 모든 주문 중에서 총 주문 가격이 50 이상인 주문을 조회합니다. 각 주문에 대해 주문 번호, 주문 날짜, 주문한 레스토랑 이름, 주문 상태, 총 가격을 표시합니다.

SELECT o.order_id, o.created_at, r.name, os.status, total_price FROM orders o
    INNER JOIN order_status os ON os.order_status_id = o.order_status_id
    INNER JOIN restaurants r ON r.restaurant_id = o.restaurant_id
    WHERE os.status = 'Delivered'
    AND total_price >= 50;

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

온라인 쇼핑몰 주문 관리 시스템 (1)	2024.01.29
도서관 관리 시스템 (0)	2024.01.26

ABOUT ME

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

테이블 설계 요구사항 : 음식 배달 서비스 시스템

Step1) 최초 작성

Step2) 피드백

Step3 문제풀이

문제 1: 고객별 주문 히스토리 조회

문제 2: 특정 레스토랑의 가장 인기 있는 음식 메뉴 조회

문제 3: 배달 완료된 주문 중 총 주문 가격이 특정 금액 이상인 주문 조회

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

티스토리툴바

ABOUT ME

테이블 설계 요구사항 : 음식 배달 서비스 시스템

Step1) 최초 작성

Step2) 피드백

Step3 문제풀이

문제 1: 고객별 주문 히스토리 조회

문제 2: 특정 레스토랑의 가장 인기 있는 음식 메뉴 조회

문제 3: 배달 완료된 주문 중 총 주문 가격이 특정 금액 이상인 주문 조회

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

관련글 관련글 더보기

티스토리툴바

Step2) 피드백