요구사항 정의 및 설계

제안서에 계획한 기능들을 수행하기 위한 요구사항 및 기능을 정의하였고, 이를 바탕으로 ERD를 작성하였다.

요구사항 정의 및 기능

페이지를 기준으로 나누었고, 크게
로그인 페이지 / 회원가입 선택 페이지 / 회원가입 페이지 / 메인 페이지 / 카페 등록 페이지 / 마이페이지로 나누어 각 페이지에 필요한 기능들을 정의하였다. 검색 및 조회 기능은 메인페이지에서 가능하도록 하였다.

엔티티 구성 및 테이블 정의

User / StudyCafe / Seat / Ticket / RoomReservation / Review / Tag / Cafe_Tag 로 정했고, 각 엔티티끼리의 관계 또한 정리하였다. 이를 바탕으로 테이블 정의서까지 작성해보았다.

**엔티티 관계**
- User : StudyCafe = 1 : N
- User : Ticket = 1 : N
- User : RoomReservation = 1 : N
- User : Review = 1 : N
- StudyCafe : Seat = 1 : N
- StudyCafe : Review = 1 : N
- StudyCafe : Tag = N : M
- Seat : Ticket = 1 : N
- Seat : RoomReservation = 1 : N

위의 내용을 바탕으로 draw.io를 이용하여 ERD 다이어그램을 그렸다. 관계가 많고 외래키가 많기 때문에 이점 유의하며 그리려고 하였다.

주요 SQL 시나리오

주요 기능에 대한 SQL 몇 가지를 작성해보았다.

회원가입

INSERT INTO user(
login_id, password, email, phone, role, business_number
)
VALUES('owner01', '1234', 'owner@email.com', '010-1111-2222', 'OWNER', '123-45-67890'
);

태그 기반 카페 검색

SELECT c.cafe_name
FROM study_cafe c
JOIN cafe_tag ct ON c.cafe_id = ct.cafe_id
JOIN tag t ON ct.tag_id = t.tag_id
WHERE t.tag_name = '24시간';

좌석 조회

-- 일반 좌석
SELECT
    s.seat_id,
    s.seat_name,
    s.seat_type,
    CASE
        WHEN t.ticket_id IS NULL THEN '사용 가능' -- 멘트는 추후 변경
        ELSE '사용중'
    END AS status,
    CASE
        WHEN t.ticket_id IS NOT NULL
        THEN SEC_TO_TIME(TIMESTAMPDIFF(SECOND, NOW(), t.end_time))
        ELSE NULL
    END AS remaining_time
FROM seat s
LEFT JOIN ticket t
    ON s.seat_id = t.seat_id
   AND NOW() BETWEEN t.start_time AND t.end_time
WHERE s.cafe_id = [조회할 카페 아이디]
  AND s.seat_type IN ('일반석', '노트북석') -- 좌석타입 값도 추후 변경
ORDER BY s.seat_type, s.seat_name;

-- 회의실
-- 이후 예약으로부터 1시간 이상 남아있으면 -> 사용 가능으로 표시
SELECT
    s.seat_id,
    s.seat_name,
    s.seat_type,
    CASE
        WHEN now_r.reservation_id IS NOT NULL THEN '사용중'
        WHEN next_r.start_time IS NOT NULL
             AND TIMESTAMPDIFF(MINUTE, NOW(), next_r.start_time) < 60
        THEN '예약중'
        ELSE '사용 가능'
    END AS status,
    CASE
        WHEN now_r.reservation_id IS NOT NULL
        THEN SEC_TO_TIME(TIMESTAMPDIFF(SECOND, NOW(), now_r.end_time))
        ELSE NULL
    END AS remaining_time,
    next_r.start_time AS next_reservation_start,
    next_r.end_time AS next_reservation_end
FROM seat s
LEFT JOIN room_reservation now_r
    ON s.seat_id = now_r.seat_id
   AND NOW() BETWEEN now_r.start_time AND now_r.end_time
LEFT JOIN (
    SELECT r1.seat_id, r1.start_time, r1.end_time
    FROM room_reservation r1
    WHERE r1.start_time = (
        SELECT MIN(r2.start_time) -- 가장 빠른 예약 조회
        FROM room_reservation r2
        WHERE r2.seat_id = r1.seat_id
          AND r2.start_time > NOW()
    )
) next_r
    ON s.seat_id = next_r.seat_id
WHERE s.cafe_id = [조회할 카페 아이디]
  AND s.seat_type = '회의실'
ORDER BY s.seat_name;

회의실 예약

INSERT INTO room_reservation(
user_id, seat_id, reservation_date, start_time, end_time
)
VALUES(
1, 10, '2026-05-10', '2026-05-10 15:00:00', '2026-05-10 17:00:00'
);

사장님 분석 대시보드 조회 - 좌석 유형별 인기도

-- 특정 카페 기준
SELECT
    s.seat_type,
    COUNT(*) AS usage_count
FROM seat s
JOIN (
    SELECT seat_id FROM ticket
    UNION ALL
    SELECT seat_id FROM room_reservation
) u ON s.seat_id = u.seat_id
WHERE s.cafe_id = [조회할 카페 아이디]
GROUP BY s.seat_type
ORDER BY usage_count DESC;

사장님 분석 대시보드 조회 - 피크타임 분석

-- 특정 카페 기준
SELECT
    HOUR(u.start_time) AS hour,
    COUNT(*) AS usage_count
FROM (
    SELECT seat_id, start_time FROM ticket
    UNION ALL
    SELECT seat_id, start_time FROM room_reservation
) u
JOIN seat s ON u.seat_id = s.seat_id
WHERE s.cafe_id = [조회할 카페 아이디]
GROUP BY HOUR(u.start_time)
ORDER BY usage_count DESC;

사장님 분석 대시보드 조회 - 전월 대비 증감률

-- 전체 카페 조회 기준
SELECT
    c.cafe_id,
    c.cafe_name,

    SUM(DATE_FORMAT(t.start_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')) AS this_month,
    SUM(DATE_FORMAT(t.start_time, '%Y-%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')) AS last_month,
    ROUND(
        (
            SUM(DATE_FORMAT(t.start_time, '%Y-%m') = DATE_FORMAT(CURDATE(), '%Y-%m')) -- 이번 달
            -
            SUM(DATE_FORMAT(t.start_time, '%Y-%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')) -- 지난 달
        )
        /
        NULLIF(
            SUM(DATE_FORMAT(t.start_time, '%Y-%m') = DATE_FORMAT(CURDATE() - INTERVAL 1 MONTH, '%Y-%m')),
            0
        )
        * 100, 2 -- 둘째자리까지 반올림
    ) AS growth_rate
FROM study_cafe c
LEFT JOIN seat s
    ON c.cafe_id = s.cafe_id
LEFT JOIN ticket t
    ON s.seat_id = t.seat_id
GROUP BY c.cafe_id, c.cafe_name -- 카페 각각 가져오려면 여길 수정 -> WHERE로
ORDER BY c.cafe_id;