[데브코스] 데이터베이스 모델링 프로젝트(ERD, DDL)

오늘은 강의를 듣지 않고, 9시부터 zep에 모여서 하루종일 카메라 켜고 프로젝트 수행하는 날이었다.

프로젝트는 총 2개. 포트폴리오 페이지 만들기와, 요구사항에 맞는 데이터베이스 모델링을 하는 프로젝트였다. 포트폴리오 페이지는 이것저것 조금씩 계속 작업 중

---

🚈ERD

Entity Relationship Diagram, 시스템의 엔티티들이 무엇이 있는지, 어떤 관계가 있는지 나타내는 다이어그램

요구사항

강사님께서 화면 와이어프레임을 주셨다. 공연 티켓팅 서비스에 대한 와이어프레임이고, 주어진 페이지는 5개였다.

1. 공연 목록 페이지
2. 공연 상세 페이지
3. 로그인 페이지
4. 회원가입 페이지
5. 주문 내역 페이지

 

우선 완성한 ERD는 다음과 같다!

완성된 ERD

---

📅테이블

user_info

사용자 정보를 저장하는 테이블. 로그인/회원가입 기능과 직접적으로 연결된다.

• id: 사용자 식별을 위한 기본 키. AUTO_INCREMENT를 사용해 자동으로 증가한다.
• email, name, password: 회원가입 및 로그인에 필요한 필수 필드이다. VARCHAR(100)로 선언하였다.
• created_at, updated_at: 각각 회원가입 시각과 정보 수정 시각을 저장하는 테이블. 요구사항 페이지에서는 보이지 않지만, 사용자 테이블 생성 시 필수되는 필드라고 생각하여 포함하였다. 둘 다 시간을 자동으로 기록하도록 CURRENT_TIMESTAMP를 기본값으로 설정했다.

shows

공연 정보를 담는 테이블. 공연 목록과 공연 상세 페이지에 출력되는 데이터가 저장된다.

• id: 공연 식별용 기본 키이다. user_info와 마찬가지로 AUTO_INCREMENT를 적용하여 자동으로 증가하며, 후술할 테이블도 동일한 방식으로 id를 설정하였다.
• title, description: VARCHAR()로 선언하였으며, 설명은 긴 문장을 위해 VARCHAR(1000)을 사용했다.
• price, quantity: 티켓 가격과 공연 전체 좌석 수 이다.
• show_date: 공연 날짜 및 시간을 저장하는 필드. created_at 등과 달리, 기본값을 주지 않고 사용자가 직접 입력하는 형태로 설정하였다.
• created_at: 공연 정보를 등록한 시간을 저장한다.

poster

공연에 연결되는 이미지 정보를 담는 테이블이다.

• id: 포스터 식별용 키
• path: 실제 이미지 URL 또는 파일 경로를 저장한다.

❓왜 shows와 poster을 따로 구성했나?
하나의 공연이 여러 이미지를 가질 수도 있고, 여러 공연이 하나의 이미지를 공유할 수도 있다. 또 이미지 변경이나 재사용에 유연하게 대응할 수 있기 때문에 별도의 테이블로 구성하고, 외래키로 참조하도록 구성했다.

 

orders

사용자 전체 주문 정보를 저장하는 테이블. 장바구니라고 생각하면 편하다.

• id: 주문 식별용 기본 키
• order_code: 사용자에게 보여질 고유 주문번호이다. 데이터베이스에서의 id는 AUTO_INCREMENT 되는 INT형이지만, 사용자에게 표시되는 주문번호는 따로 생성하여 저장하는 방식을 생각하여 구성했다.
• user_id: 주문한 사용자. user_info.id를 참조하는 외래키이다.
• total_price: 전체 주문 금액
• order_date: 주문 시점을 자동으로 기록하도록 DATETIME CURRENTSTAMP를 사용하였음

order_items

주문하는 공연 정보를 저장하는 테이블

• id: 주문 항목 식별 키
• order_id: 어떤 주문에 속한 항목인지를 나타낸다.
• show_id: 어떤 공연을 주문했는지 나타낸다.
• price: 주문하는 공연 가격
• quantity: 주문한 수량

❓orders와 order_items를 왜 따로 구성했나?
한 번의 주문에 여러 개의 공연을 담을 수 잇어, 이를 묶는 상위 개념으로 order을 생성했다.

 

테이블 관계

📅user_info <-> orders

• 관계: 1:N
• 설정: orders.user_id -> user_info.id(FK)
• 이유: 한 명의 사용자가 여러 번 구매할 수 있어야 하기 때문

 

📅orders <-> order_items

• 관계: 1:N
• 설정: ordes_items.order_id -> order.id
• 이유: 한 번 주문할 때 여러 개 공연을 예매할 수 있기 때문에 항목 단위로 분리하였다.

 

📅order_items <-> shows

• 관계: N:1
• 설정: order_items.shows_id ->shows.id
• 이유: 주문 항목 하나는 하나의 공연을 참조하지만, 한 개의 공연을 여러개 주문할 수 있음

 

📅shows <-> poster

• 관계: N:1
• 설정: shows.poster_id => poster.id(FK)
• 이유: 하나의 포스터를 여러 공연이 재사용할 수 있다.

🙄원래는 하나의 공연이 여러 이미지를 참조할 수 있는 구조(다대다 관계)를 염두에 두었지만, 현재 테이블 구조에서는 중간 테이블 없이 이를 구현하기 어렵다. 현재 구조에서는 하나의 공연이 하나의 이미지만 참조할 수 있으며, 하나의 이미지는 여러 공연에서 재사용될 수 있다.

 

DDL

✅user_info 테이블
CREATE TABLE user_info (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> email VARCHAR(100) NOT NULL,
    -> name VARCHAR(100) NOT NULL,
    -> password VARCHAR(100) NOT NULL,
    -> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
    -> );

✅shows 테이블
CREATE TABLE shows(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> title VARCHAR(100) NOT NULL,
    -> poster_id INT NOT NULL,
    -> description VARCHAR(1000),
    -> price INT NOT NULL,
    -> quantity INT NOT NULL,
    -> created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    -> FOREIGN KEY (poster_id) REFERENCES poster(id)
    -> );

✅poster 테이블
CREATE TABLE poster (
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> path VARCHAR(255) NOT NULL
    -> );

✅orders 테이블
CREATE TABLE orders(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> order_code VARCHAR(50) NOT NULL,
    -> user_id INT NOT NULL,
    -> total_PRICE INT NOT NULL,
    -> order_date DATETIME NOT NULL,
    -> FOREIGN KEY (user_id) REFERENCES user_info(id)
    -> );

✅orders_item 테이블
CREATE TABLE order_items(
    -> id INT AUTO_INCREMENT PRIMARY KEY,
    -> order_id INT NOT NULL,
    -> show_id INT NOT NULL,
    -> price INT NOT NULL,
    -> quantity INT NOT NULL,
    -> FOREIGN KEY (order_id) REFERENCES orders(id),
    -> FOREIGN KEY (show_id) REFERENCES shows(id)
    -> );

 

❌오류: 예약어 충돌

처음에는 테이블명을 show, order로 설정하고 DDL을 작성했더니 오류가 발생했다.

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'show(
id INT AUTO_INCREMENT PRIMARY KEY,
title VARCHAR(100) NOT NULL,
poster_...' at line 1

 

이는 MariaDB에서 예약어와 충돌해서 발생하는 오류였다.

 

백틱으로 감싸는 방식으로 해결하거나, 또는 다른 이름으로 변경하여 쉽게 가능한 문제였다. 나는 shows, orders로 복수형으로 테이블명을 변경하여 문제를 해결했다. 예약어를 충분히 고려하고 데이터베이스를 모델링하라는 교훈을 얻었다..

 

❌새로운 컬럼 추가하기

테이블을 생성할 때 실수로 컬럼을 하나 빼먹었다... ALTER TABLE 명령어를 사용하여 빼먹은 컬럼을 추가해주었다.

ALTER TABLE shows
ADD COLUMN show_date DATETIME NOT NULL;

 

SQL문

DDL로 데이터베이스를 생성하고, SQL문을 사용해 데이터 삽입, 조회, 수정을 해보았다.

✅user_info
//조회 
SELECT * FROM user_info

//입력
INSERT INTO user_info(email, name password) VALUES ("monicx@email.com", "dakyung", "1234@");

//수정
UPDATE user_info SET name = "hwang dakyung" WHERE id = 1;

✅shows
//조회
SELECT * FROM shows;

//입력
ISERT INTO shows (title, poste_id, description, price, quantity) VALUES ("....");

//수정
UPDATE shows SET description = "재밌음" WHERE id = 1;

✅poster
//조회
SELECT * FROM poster;

//입력
INSERT INTO poster (path) VALUES ("https://img.com");

//수정 
UPDATE poster SET poster = "https://wicked.com" WHERE id = 1;

✅orders
//조회
SELECT * FROM orders;

//입력
INSERT INTO orders (order_code, user_id, total_price) VALUES ("...");

✅order_items
//조회
SELECT * FROM order_items;

//입력
INSERT INTO order_items (order_id, show_id, price, quantity) VALUES (1, 1, 132000, 2);

//수정
UPDATE order_items SET quantity = 1 WHERE id = 1;

결과

---

데이터베이스 스키마는 많이 짜봤지만, ERD를 이렇게 각 잡고 만들어본건 거의 처음인 것 같다. 소공 할 때 해본 것 같기도 하고..? 테이블 다 만들고 선으로 연결까지 해놓으니 뭔가 뭔가 멋있다. 

 

DDL로 데이터베이스 만들 때 오타를 너무 많이 내서 하나 테이블 만드는데 10분도 더 걸렸다. 진짜 너무 화나고 짜증났지만 덕분에 이제 SQL문 기본 정도는 너무 쉽게 쓸 수 있게 된듯. 간단하지만 많은 도움이 된 프로젝트인 것 같다.

 

+ 포트폴리오 만드는 중🛠️