[CAFE] 프로젝트 테이블 설계 - 데이터 타입 설정, ddl 작성

카페 키오스크 구현 개인 프로젝트 

2023/01/09 ~ 진행중

ERD

 

 

포인트 이력 (point_history)

 

• 사용자가 포인트를 이용해 결제를 하기 때문에 포인트 이력이 없으면 충전, 사용, 환불 기록을 볼 수 없기 때문에 데이터 일관성 유지를 위해 반드시 필요한 테이블이다.
• 타입에는 ENUM 클래스로 payment, charge 타입을 넣을 수 있다. 처음에는 충전과 사용 컬럼을 별도로 만드려고 하였으나 환불과 같이 포인트 관련 다른 종류의 사용이 가능하기 때문에 서비스 확장성을 위해서 enum 클래스로 만들었다.
• 포인트 이력은 수정되는 데이터 값이 없기 때문에 데이터 수정시간은 컬럼에 추가하지 않았다.
• 사용자와 다대일 연관관계를 맺었고 '다' 쪽인 포인트 이력 테이블이 연관관계 주인이다.

주문 (orders)

 

• 총 주문 가격과 주문 시간, 유저 id 컬럼을 가진다. 
• create TABLE `order`; 로 sql로는 예약어인 'order' 로 테이블 생성이 가능하지만 spring을 실행하여 테이블에 매핑할 때 SQLGrammarException 이 발생하여 테이블명을 orders로 변경하였다.
• (추가) Order 엔티티 코드에 @Table(name = "`order`") 를 추가하면 'order' 테이블이 예외 없이 생성된다.

제품별 주문 (order_item)

 

• 한 주문에서 제품별로 데이터를 저장하는 테이블이다. 메뉴 가격이 변경될 수 있으니 메뉴 id와 결제한 메뉴 가격을 모두 저장하여 반정규화를 하였다.  
• 주문 테이블과 다대일 연관관계를 맺었으며, 메뉴와도 다대일 연관관계를 맺었으나 구현 과정에서 필요성을 못 느껴 삭제하였다. 

DDL 작성

CREATE SCHEMA IF NOT EXISTS `mydb` DEFAULT CHARACTER SET utf8 ;
USE `mydb`;

CREATE TABLE IF NOT EXISTS `mydb`.`user` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `nickname` VARCHAR(20) NOT NULL,
  `point` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `modified_time` TIMESTAMP(3) NULL,
  PRIMARY KEY (`id`));

CREATE TABLE IF NOT EXISTS `mydb`.`point_history` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(50) NOT NULL,
  `point` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_point_history_user_idx` (`user_id` ASC)) ;

CREATE TABLE IF NOT EXISTS `mydb`.`menu` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `name` VARCHAR(30) NOT NULL,
  `price` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `modified_time` TIMESTAMP(3) NULL,
  PRIMARY KEY (`id`));
  
  CREATE TABLE IF NOT EXISTS `mydb`.`orders` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `amount` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`));

CREATE TABLE IF NOT EXISTS `mydb`.`order_item` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `price` BIGINT NOT NULL,
  `number` INT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `menu_id` BIGINT NOT NULL,
  `order_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_order_item_orders1_idx` (`order_id` ASC));

 

예전에는 spring.jpa.hibernate: ddl-auto: update 혹은 create로 JPA로 테이블을 생성해서 datetime, varchar(255), fk를 생성된 대로 사용하였다. 하지만 원래 테이블 생성은 직접 SQL문으로 작성한다고 한다.

 

아래 블로그를 참고해서 workbench에서 데이터 타입이나 null, index, fk 옵션 같은 것들을 체크하여 ddl을 작성했고, 데이터 타입과 fk constraint 같은 것들을 추가적으로 수정을 했다.

(DDL이란 데이터를 생성하거나 수정, 삭제 등 데이터의 전체 골격을 결정하는 역할의 데이터베이스를 정의하는 언어)

 

https://litiblue.com/post/mysql-workbench-db/

MySQL Workbench 로 DB 설계하기

 

AUTO_INCREMENT

 

JPA 기본키 자동 생성 전략을 Identity로 설정하여 기본키 생성을 MySQL에 위임하였다. 

 

TIMESTAMP

 

TIMESTAMP가 DATETIME보다 저장공간을 더 적게 차지하며, 여러 타임존에서도 같은 시간을 보장하기 때문에 선택하였고, 정교한 시간 입력을 위해 소수점 세 자리, 즉 1ms까지 시간 데이터를 저장하도록 하였다.

 

2023.01.16 - [SQL] - [MySQL] 타입 비교 1 - Timestamp 와 Datetime

 

BIGINT vs INT

 

포인트, 가격, id는 모두 2억대인 int 범위를 넘을 가능성이 조금이나마 있으므로 BIGINT로 설정하였고, int 범위를 벗어날 가능성이 없는 주문 수량만 INT로 데이터 타입을 설정하였다. 

 

VARCHAR

 

varchar(n)은 알파벳 n개까지의 저장할 수 있는 가변 길이 문자열이다.

mySQL 4.1 이후부터는 한글도 문자 수로 계산이 되기 때문에 한글 n글자까지 저장이 가능하다.

 

문자 길이를 제한하여 불필요한 메모리 낭비 방지를 위해 유저 닉네임, 포인트 이력 타입, 메뉴 이름 모두 필요한 만큼의 varchar 길이를 설정하였다.

 

INDEX

 

CREATE TABLE IF NOT EXISTS `mydb`.`point_history` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(50) NOT NULL,
  `point` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_point_history_user_idx` (`user_id` ASC) VISIBLE,
  CONSTRAINT `fk_point_history_user`
    FOREIGN KEY (`user_id`)
    REFERENCES `mydb`.`user` (`id`)
    ON DELETE NO ACTION
    ON UPDATE NO ACTION);

 

Workbench 또는 JPA로 테이블을 생성하면 위와 같이 FK 제약이 생성된다. 이렇게 되면 데이터 삭제/업데이트할 때 FK 제약이 있다는 경고 뜨며 실행이 되지 않고, 키가 있는 데이터부터 순서대로 삭제해야 하는 번거로움이 있다. 그래서 아래와 같이 FK 대신 INDEX로만 FK를 설정하기도 한다고 한다.

 

CREATE TABLE IF NOT EXISTS `mydb`.`point_history` (
  `id` BIGINT NOT NULL AUTO_INCREMENT,
  `type` VARCHAR(50) NOT NULL,
  `point` BIGINT NOT NULL,
  `created_time` TIMESTAMP(3) NULL,
  `user_id` BIGINT NOT NULL,
  PRIMARY KEY (`id`),
  INDEX `fk_point_history_user_idx` (`user_id` ASC)) ;

 

---

 

https://github.com/Suyoung225/CAFE

GitHub - Suyoung225/CAFE: 카페 키오스크 구현 개인 프로젝트 - 진행중