TIL [0629] :1주차 - DB 테이블 Schema (MySQL)

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

1. 데이터베이스 설계

화면 설계와 응답 데이터를 보며 테이블과 필드, 제약조건을 생각해보았다.

user

CREATE TABLE user (
    id INT(11) NOT NULL AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL,
    email VARCHAR(100) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    PRIMARY KEY (id)
);

PK는 NOT NULL과 UNIQUE 제약조건을 가진다.

email은 로그인 아이디로 사용되어 유저마다 고유해야하므로 UNIQUE 제약조건을 두었다.

INT로 선언하면 INT(11)로 선언하는 것과 같다.
INT는 10자리이지만 음수까지 표현하기 위해 11자리가 default이다.

product

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

이미지 경로를 저장하므로 VARCHAR 타입을 선택했다.
만약 이미지 자체(바이너리 데이터)를 저장하려면 BLOB 타입을 사용해야 한다.

option

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

제품과 옵션 테이블을 따로 생성하는 이유
제품 하나에 여러개의 옵션이 존재하고(1:N), 옵션이 새로 추가 또는 삭제될 수 있기 때문이다.
또한 나중에 옵션 안의 속성들이 변경될 수 있다.

외래키(FK) 사용 이유
해당 상품 조회시 옵션도 함께 조회해야하므로 조인(join) 연산이 수행된다.
이때 참조 무결성을 위해서 FK를 사용하였다.

cart

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,
    price INT(11) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user(id),
    FOREIGN KEY (option_id) REFERENCES option(id),
);

사용자는 장바구니에 {옵션, 옵션 개수}를 함께 담는다.

장바구니 조회 기능은 유저 id로 cart 테이블을 조회함으로써 구현된다.

주문상품(item) 테이블과 주문(order) 테이블이 필요한 이유

주문 과정

1. 결제하기 버튼을 누른다.
2. 담은 장바구니(cart)들이 주문 상품(item)들로 이동한다.
3. 주문(order)이 생성된다.
4. 장바구니가 비워진다.

테이블 설명

주문 상품(item) 테이블
장바구니(cart)들이 주문되어 실제 주문 상품들이 된다.
장바구니와 구조가 거의 같다.(상품 옵션, 옵션 개수 + 주문 번호)

 

주문(order) 테이블
유저의 주문을 기록하고, 빈 장바구니로 갱신하기 위해 필요하다.
주문 id와 유저 id로 구성되어있다.
주문 후, 주문 테이블의 유저 id를 찾아서 해당 유저의 장바구니(cart)를 비운다.

➡️ 주문 테이블은 결제 전 담은 장바구니들(cart)로부터 결제 후 주문된 상품들(item)을 분리하여 저장해준다.

✅ 현재 요구사항에는 구매할 장바구니를 선택하는 기능은 없고, 담은 장바구니 모두를 주문하도록 설계되어있다.

 

만약 장바구니(cart)들을 선택하여 주문할 수 있다면,

1. 주문된 장바구니들만 장바구니 테이블에서 제거하거나,
2. 주문 상품들(item)의 옵션 번호를 조회하여 장바구니에서 그 옵션번호를 가진 장바구니를 삭제하여
   장바구니 비우기를 구현할 수 있다.

유저와 주문(order) 테이블, 주문상품(item) 테이블의 관계

유저와 주문(order)은 1:N 관계이고, 주문과 주문 상품(item)도 1:N 관계이다.
즉 주문들은 유저로 묶일 수 있고, 주문 상품들은 주문으로 묶일 수 있다.
주문 table은 유저 id를 가지면 되고, 주문 상품 table은 주문 id를 가지면 된다.

담은 장바구니를 비울때는
1. 주문에 대한 유저 id를 찾고, 2. 유저 id로 장바구니(cart)들을 조회하여 비운다.
(요구사항에 주문시 유저의 모든 장바구니를 비우도록 설계되어있다.)

order

CREATE TABLE order (
    id INT(11) NOT NULL AUTO_INCREMENT,
    user_id INT(11) NOT NULL,
    delivery_id INT(11) NOT NULL,
    PRIMARY KEY (id),
    FOREIGN KEY (user_id) REFERENCES user(id)
    ON DELETE SET NULL,
    FOREIGN KEY (delivery_id) REFERENCES delivery(id)
    ON UPDATE CASCADE
);

유저의 주문 기록을 남기고, 장바구니를 비울 때 필요한 테이블이다.

item

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

결제 버튼을 누르면, 담은 장바구니(cart)들을 가져와 주문 상품(item) 테이블에 저장한다.

delivery

CREATE TABLE delivery (
    id INT(11) NOT NULL AUTO_INCREMENT,
    postal_code INT(11) NOT NULL,
    address varchar(300) NOT NULL,
    instruction varchar(500) NOT NULL,
    fee int(11) NOT NULL
    PRIMARY KEY (id)
);

우편번호, 주소, 주문 요청사항, 배달비로 구성되며,
배달정보에 매칭되는 유저 id와 주문 id를 외래키로 참조한다. 

요구사항에 없더라도 구현에 필요한 테이블이므로 추가로 작성했다.

유저 id를 외래키로 참조하지 않은 이유

주문시 주문 table에서 배송 table을 참조하는데, 이때 주문 table이 유저 id를 이미 참조하고 있기 때문이다.
또한 해당 유저의 배달 정보는 주문마다 바뀌므로 굳이 유저 id를 참조할 필요가 없다.

유저가 탈퇴하면 배달정보도 함께 삭제되도록 ON DELETE CASCADE를 사용했다.

 

데이터베이스 설계하며 추가 공부한 내용

• char vs varchar
  • char
    • 고정형 , 할당된 길이(괄호 안 숫자)만큼 공간 차지
    • 남은 공간은 공백이 차지
      • ex)char(10) : ”12345 “, 10 byte 소모
    • 용량 많이 차지하지만 검색에 유리 ex)id , 주민등록번호
  • varchar
    • 가변형 , 데이터의 길이에 따라 가변적으로 길이 결정
    • 입력된 크기 + 길이(1 byte)만큼 저장
      • ex) varchar(10) : “12345” , 6 byte 소모
      • 255 byte 길이를 넘어가면 1 byte가 아닌 2 byte로 저장
    • 용량 적게 차지하지만 검색에 불리 (내부적으로 엉켜있음) ex) email
      • 데이터 길이를 확인하는 연산을 추가로 거치므로 미세하게 살짝 느리다고 한다.
      • MySQL 필드 데이터 타입(자료형) 총정리
    • 데이터 파편화가 발생할 수 있다.

MySQL 필드 데이터 타입(자료형) 총정리

 

varchar(255)

char과 varchar의 괄호에 문자열 최대크기를 설정한다.
varchar 타입은 varchar(255)가 관습적으로 사용된다. (Mysql 5.0.3 이전 최대길이)
하지만 varchar(100)으로 설정하더라도 255 byte 이하까지 변경 가능하므로, 스키마에 맞게 보수적으로 설정하는 것이 좋다.
(256 byte부터는 2 byte로 저장되므로 저장 구조가 바뀐다.)

MySQL varchar(255)를 사용하는 이유?

MySQL varchar(255)를 사용하는 이유?

 

• int(10) vs int(50)
  • int(10)과 int(50)에 저장되는 값은 같다.
  • int의 괄호 옵션은 ZEROFILL (남는 공간을 0으로 채움) 옵션을 위한 것이다.
  • Mysql varchar(50) 과 char(50)의 의미

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

 

@Id 
@GeneratedValue(strategy = GenerationType.IDENTITY) 
private Long id;

• JPA에서 id 생성시 일어나는 일들
  • Long 타입은 bigint 형으로 저장된다.
  • int형은 bigint형보다 10% 이상의 디스크 용량을 절약할 수 있지만, 향후 업그레이드 용이성을 고려한다면 bigint를 사용하는 것이 좋다.
  • id값이 43억이 넘어가는 아주 큰 DB 사용시에는 bigint를 사용해야한다.
  • 분산 환경일 경우에 auto_increment시 같은 id 2명(중복 키)가 생길 수 있다..😱 (UUID 사용 추천)
  • [면접] id를 왜..bigint..?

[면접] id를 왜..bigint..?

 

• 데이터베이스 설계시 이름 짓는 방법
  • MySQL 데이터베이스명, 테이블명, 컬럼명은 어떻게 지어야 할까?

MySQL 데이터베이스명, 테이블명, 컬럼명은 어떻게 지어야 할까?