카테켐 | [STEP2 clone coding] 1주차 과제 - (3)

과제명

1. 요구사항분석 / API요청 및 응답 시나리오 분석
2. 요구사항 추가 반영 및 테이블 설계도

 

과제 설명 

1. 요구사항 시나리오를 보고 부족해 보이는 기능을 하나 이상 체크하여 README에 내용을 작성하시오.

 

2. 제시된 화면설계를 보고 해당 화면설계와 배포된 기존 서버의 API주소를 매칭하여 README에 내용을 작성하시오. (카카오 화면설계 시나리오가 있음)

 

3. 배포된 서버에 모든 API를 POSTMAN으로 요청해본 뒤 응답되는 데이터를 확인하고 부족한 데이터가 무엇인지 체크하여 README에 내용을 작성하시오.

 

4. 테이블 설계를 하여 README에 ER-Diagram을 추가하여 제출하시오. 

• API 요청 응답에 따라 테이블을 예측해서 작성
• er-diagram 설계 

 

---

04) 테이블 설계를 하여 README에 ER-Diagram을 추가하여 제출하시오.

 

카카오테크를 진행하며 SQL에 대해 배우긴 했지만, 사용법과 실습 위주로 배웠기 때문에 이번 과제를 진행하며 SQL 문법들을 하나씩 익혀가보려고 한다.

 

1. User 테이블

 

- id : PK

- username : 사용자 이름

- email : 사용자 이메일 ⇒ UNIQUE

- password : 사용자 패스워드

 

 

user 테이블에 필요한 데이터는 다음과 같으며, 이를 토대로 sql문은 다음과 같이 작성할 수 있다.

 

CREATE TABLE user (
	id int(11) NOT NULL AUTO_INCREMENT,
	email varchar(100) NOT NULL,
	password varchar(256) NOT NULL,
	username varchar(45) NOT NULL,
 	PRIMARY KEY (id),
	UNIQUE KEY email_UNIQUE (email)
);

User 테이블에서 id, email, password, username은 필수적으로 입력받아야 하는 필드이므로, 'NOT NULL'과 같은 제약 사항을 넣어준다. 

id 값의 경우 PRIMARY KEY('id')로 입력하여 PK로 설정해주었으며 AUTO_INCREMENT는 데이터가 입력될 때마다 자동으로 1씩 증가시켜 주는 속성으로, id값이 입력될 때마다 1씩 증가하도록 한다.

https://m.blog.naver.com/PostView.naver?isHttpsRedirect=true&blogId=goddlaek&logNo=221005664911 

[Mysql] Auto Increment 사용하기

email 필드는 UNIQUE KEY를 걸어주어 해당 열에 중복된 값을 허용하지 않으며, 각 행은 해당 열의 값이 고유하도록 한다. 즉, 다른 유저가 같은 이메일로 가입을 할 수 없도록 한다.

https://thisisprogrammingworld.tistory.com/133

[MySQL]날짜/시간 타입과 TIMESTAMP 칼럼 생성

 

2. Product 테이블

 

- id : PK
- product_name : 상품명
- description : 상품 설명
- image : 상품 사진
- price : 상품 가격

 

Product 테이블에 필요한 데이터는 다음과 같고, SQL문을 다음과 같이 작성한다.

 

CREATE TABLE product (
	id int(11) NOT NULL AUTO_INCREMENT,
	product_name varchar(100) NOT NULL,
	description varchar(1000),
	image varchar(500),
	price int(11),
	PRIMARY KEY (id)
);

 

https://heavening.tistory.com/85

int의 종류 및 크기 tinyint, smallint, int, bigint 초간단 정리

https://m.blog.naver.com/darkxpavmffj/90089405466

Mysql varchar(50) 과 char(50)의 의미

 

3. Option 테이블

 

- id : PK
- product_id : FK ⇒ priduct(id)
- option_name : 옵션 이름
- price : 옵션 가격

 

Product 테이블과 Option 테이블을 별도로 생성하는 이유는, 이 둘의 관계가 1 대 N 관계이기 때문이다. 데이터 베이스에서는 중복을 최소화하고 데이터의 일관성을 유지 및 효율적으로 구성하기 위해 데이터를 정규화한다. 이때 Product 테이블과 Option 테이블을 나누어 설계함으로써 중복된 데이터를 피하고 데이터의 일관성과 무결성을 유지할 수 있다. 또한, 옵션의 가격, 재고 수량, 제조사 정보 등을 추가로 저장해야 할 수도 있기 때문에, 이러한 확장성과 유연성을 위해 option 테이블을 따로 생성하고 필요한 속성을 추가할 수 있다.

 

https://appmaster.io/ko/blog/deiteo-jeonggyuhwaran-mueosinga

데이터 정규화란 무엇입니까? | AppMaster

 

CREATE TABLE option (
	id int(11) NOT NULL AUTO_INCREMENT,
	product_id int(11) DEFAULT NULL,
	option_name varchar(100) NOT NULL,
	price int(11) NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (product_id) REFERENCES product (id)
);

FK(Foreign Key)를 사용하여 Product 테이블의 id을 불러와 후에 조인하여 제품과 옵션의 데이터를 한번에 가져올 때 사용하는 제약조건(constraint)를 걸어준다.

https://jerryjerryjerry.tistory.com/49

[SQL] 키(슈퍼키,대체키,후보키,기본키,외래키)

https://runtoyourdream.tistory.com/129

[SQL] 제약조건, Constraint(NOT NULL, UNIQUE, PRIMARY KEY(기본키), FOREIGN KEY(외래키))!

실습 멘토님과의 Q&A 시간에서 외래키를 사용하지 않고 인덱스를 사용하여 참조하는 방법을 많이 사용한다고 하시면서 성능 문제를 들어주셨는데, 외래키를 사용하지 않고 인덱스를 사용하는 방법도 추후에 알아보고 싶다.

 

4. Cart 테이블

 

- id : PK
- user_id : FK ⇒ user(id)
- option_id : FK ⇒ option(id)
- total_price : 장바구니 가격
- quantity : 장바구니에 들어간 갯수

 

CREATE TABLE cart (
	id int(11) NOT NULL AUTO_INCREMENT,
	user_id int(11) NOT NULL,
	option_id int(11) NOT NULL,
	quantity int(11) NOT NULL,
	total_price int(11) NOT NULL,
	PRIMARY KEY (id),
	FOREIGN KEY (user_id) REFERENCES user(id),
	FOREIGN KEY (option_id) REFERENCES option(id),
);

user 테이블의 id, option 테이블의 id 정보가 필요하기 때문에 두 키를 참조한다. 어떤 제품인지에 대한 정보가 필요없는 이유는 Option 테이블에 이미 해당 옵션의 제품 정보를 가지고 있기 때문이다.

 

5. Order 테이블

 

- id : 주문 번호(PK)
- user_id : FK ⇒ user(id)
- totalPrice : 장바구니 가격

 

CREATE TABLE order (
	id int(11) NOT NULL AUTO_INCREMENT,
	user_id int(11) NOT NULL,
	totalprice int(11),
	PRIMARY KEY (id),
	FOREIGN KEY (user_id) REFERENCES user(id)
);

Order 테이블에는 유저 정보에 대한 필드만 필요한 이유는 하나의 주문은 여러 명의 유저를 가질 수 없기 때문에 주문 테이블에서는 유저의 정보만 가지고, 아이템 테이블에서 주문에 대한 정보와 구매된 제품 정보를 가지고 있으면 된다.

 

6. Item 테이블

 

- id : PK
- orderId : FK ⇒ order(id)
- optionId : FK ⇒ option(id)
- quantity : 옵션 갯수
- price : 가격

 

CREATE TABLE item (
    id int(11) NOT NULL AUTO_INCREMENT,
    option_id int(11) NOT NULL,
    quantity int(11) NOT NULL,
    price int(11) NOT NULL,
    order_id int(11) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (order_id) REFERENCES order(id),
    FOREIGN KEY (option_id) REFERENCES option(id),
);

구매된 제품의 옵션에 대한 정보가 필요하고 구매한 수량과 가격에 대한 정보가 필요하다. 해당 옵션의 가격이 달라질수 있으므로, (구매 시기의 가격과 현재의 가격의 변동 등) 가격 필드를 추가적으로 넣어준다. 또한, 어떤 주문의 아이템인지에 대한 정보가 필요하다.

 

---

ER-Diagram