🐥 카카오테크캠퍼스 - 2단계 1주차 Entity 최종 설계

🐥 카카오테크캠퍼스 - 2단계 1주차 Entity 최종 설계

- 부족한 기능에 대한 요구사항을 미리 예상할 수 있는가? 
	- (예를 들면 상품등록 api가 기존 요구사항에는 없는데 추후 필요하지는 않을지, 이런 부분들을 생각하였는지)
- 요구사항에 맞는 API를 분석하고 사용자 시나리오를 설계하였는가? 
	- (예를 들어 배포된 서버와 화면 설계를 제시해줄 예정인데, 특정 버튼을 클릭했을 때 어떤 API가 호출되어야 할지를 아는지)
- 응답되는 데이터가 프론트앤드 화면에 모두 반영될 수 있는지를 체크하였는가?
	- (예를 들어 배송관련 비용이 있는데, 이런것들이 API에는 없는데 이런 부분을 캐치할 수 있는지)
- 테이블 설계가 모든 API를 만족할 수 있게 나왔는가? 
	- (테이블이 효율적으로 나왔는가 보다는 해당 테이블로 요구사항을 만족할 수 있는지에 대한 여부만)

앞서 🐥 카카오테크캠퍼스 - 2단계 1주차 Entity 세부 설계에서 작성한 테이블을 토대로, 제약 조건 등을 설정하고 아래 요구사항을 만족시키자

전체 ER - Diagram

User(사용자)

{
	"username" : "meta",
	"email" : "meta@nate.com",
	"password" : "meta1234!"
}

API 응답을 보면, username, email, password 필드가 필요합니다
User 테이블을 보면, 해당 서비스를 사용하는 모든 사용자를 말합니다.

Sudo Code

- id : PK
- userName : 유저 이름 
- email : 이메일
- password : 비밀번호
- status : 관지라/일반 사용자
- createDate : 생성 날짜
- updateDate : 수정 날짜

SQL 쿼리

CREATE TABLE User (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    userName VARCHAR(255) NOT NULL,
    email VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    status ENUM('NORMAL', 'ADMIN'),
    createDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    UNIQUE KEY `email_UNIQUE` (`email`)
);

추가적인 테이블 : User History

사용자의 로그인과 로그아웃 등등을 기록하는 것 등의 로그를 남기는 것이 필요하다 생각했습니다

CREATE TABLE UserHistory (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    userId INT,
    loginTime TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    logoutTime TIMESTAMP,
    FOREIGN KEY (userId) REFERENCES User(id)
);

Product(상품)

{
  "success": true,
  "response": [
    {
      "id": 1,
      "productName": "기본에 슬라이딩 지퍼백 크리스마스/플라워에디션 에디션 외 주방용품 특가전",
      "description": "",
      "image": "/images/1.jpg",
      "price": 1000
    },
    {
      "id": 2,
      "productName": "[황금약단밤 골드]2022년산 햇밤 칼집밤700g외/군밤용/생율",
      "description": "",
      "image": "/images/2.jpg",
      "price": 2000
    },
  ],
  "error": null
}

Sudo Code

- id : PK
- productName : 제품 명
- description : 제품 설명
- image : 제품 사진
- price : 제품 가격
- sale : 
- starCount : 별점
- createDate : 생성 날짜
- updateDate : 수정 날짜

SQL 쿼리

CREATE TABLE Product (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    productName VARCHAR(255) NOT NULL,
    description TEXT,
    image VARCHAR(255),
    price INT NOT NULL,
    sale INT,
    starCount DECIMAL(3,2),
    createDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

할인 필드

전체 상품 목록 조회
-

• 개별 상품 상세 조회
  • 

전체 상품 목록 조회를 보면, 특별가 페이지가 있는데 상세 조회를 보면 Option들 중 가장 값이 낮은 Option의 값을 출력하는 것을 알 수 있다
즉 Prodcut 테이블의 Price는 Option 중 가장 낮은 값이며, 할인을 적용한다면, 전체 상품에 할인을 적용하고 Option으로 할인하는 정도를 뿌리면 될거라 생각했다

Option(옵션)

제품 상세 보기의 데이터 형식을 확인합시다

{
  "success": true,
  "response": {
    "id": 1,
    "productName": "기본에 슬라이딩 지퍼백 크리스마스/플라워에디션 에디션 외 주방용품 특가전",
    "description": "",
    "image": "/images/1.jpg",
    "price": 1000,
    "starCount": 5,
    "options": [
      {
        "id": 1,
        "optionName": "01. 슬라이딩 지퍼백 크리스마스에디션 4종",
        "price": 10000
      },
      {
        "id": 2,
        "optionName": "02. 슬라이딩 지퍼백 플라워에디션 5종",
        "price": 10900
      },
      {
        "id": 3,
        "optionName": "고무장갑 베이지 S(소형) 6팩",
        "price": 9900
      },
      {
        "id": 4,
        "optionName": "뽑아쓰는 키친타올 130매 12팩",
        "price": 16900
      },
      {
        "id": 5,
        "optionName": "2겹 식빵수세미 6매",
        "price": 8900
      }
    ]
  },
  "error": null
}

options를 보니, 아래와 같이 필요해보입니다

Sudo Code

- id : PK
- productId : FK -> Product.id
- optionName : 옵션 이름
- price : 옵션 가격
- createDate : 생성 날짜
- updateDate : 수정 날짜

SQL 쿼리

CREATE TABLE Option (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    productId BIGINT,
    optionName VARCHAR(255) NOT NULL,
    price INT NOT NULL,
    createDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    FOREIGN KEY (productId) REFERENCES Product(id)
);

Cart (장바구니)

{
    "success": true,
    "response": {
        "products": [
            {
                "id": 1,
                "productName": "기본에 슬라이딩 지퍼백 크리스마스/플라워에디션 에디션 외 주방용품 특가전",
                "carts": [
                    {
                        "id": 65,
                        "option": {
                            "id": 1,
                            "optionName": "01. 슬라이딩 지퍼백 크리스마스에디션 4종",
                            "price": 10000
                        },
                        "quantity": 5,
                        "price": 50000
                    },
                    {
                        "id": 66,
                        "option": {
                            "id": 2,
                            "optionName": "02. 슬라이딩 지퍼백 플라워에디션 5종",
                            "price": 10900
                        },
                        "quantity": 5,
                        "price": 54500
                    }
                ]
            }
        ],
        "totalPrice": 104500
    },
    "error": null
}

Sudo Code

- id : PK
- userId : FK -> User.id
- orderItem : FK -> Item.id
- totalPrice : 장바구니 가격
- quantity : 장바구니에 들어간 갯수
- createDate : 생성 날짜
- updateDate : 수정 날짜

SQL 쿼리

CREATE TABLE cart (
  id BIGINT PRIMARY KEY,
  userId BIGINT NOT NULL,
  optionId BIGINT,
  totalPrice BIGINT,
  quantity INT,
  createDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updateDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  FOREIGN KEY (userId) REFERENCES User(id),
  FOREIGN KEY (optionId) REFERENCES Option(id)
);

Order(주문)

{
    "success": true,
    "response": {
        "products": [
            {
                "productName": "기본에 슬라이딩 지퍼백 크리스마스/플라워에디션 에디션 외 주방용품 특가전",
                "items": [
                    {
                        "id": 65,
                        "option": {
                            "id": 1,
                            "optionName": "01. 슬라이딩 지퍼백 크리스마스에디션 4종",
                            "price": 10000
                        },
                        "quantity": 5,
                        "price": 50000
                    }
                ],
            },
            {
                "productName": "삼성전자 JBL JR310 외 어린이용/성인용 헤드셋 3종!",
                "items": [
                    {
                        "id": 3,
                        "option": {
                            "id": 1,
                            "optionName": "JR310BT (무선 전용) - 레드",
                            "price": 10000
                        },
                        "quantity": 5,
                        "price": 50000
                    }
                ],
            }
        ],
        "totalPrice": 104500
    },
    "error": null
}

Sudo Code

- id : 주문 번호(PK)
- userId : FK -> User.id
- totalPrice : 장바구니 가격
- createDate : 생성 날짜
- updateDate : 수정 날짜

생각해보니, Item Id가 없어도, userid로 cart를 조회하면 내가 구매할 상품들을 가져 올 수 있으니까
userId만 가지고 있으면 된다

SQL 쿼리

CREATE TABLE `order` (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  userId BIGINT,
  orderItem BIGINT,
  totalPrice BIGINT,
  createDate DATETIME,
  updateDate DATETIME,
  FOREIGN KEY (userId) REFERENCES User(id),
);

Item(주문 아이템)

위의 주문 Item 필드를 확인하면 아래와 같은 데이터가 필요합니다

Sudo Code

- id : PK
- orderId : FK -> Order.id : 주문 번호
- optionId : FK -> Option.id
- quantity : 옵션 갯수
- price : 가격
- createDate : 생성 날짜
- updateDate : 수정 날짜

Product Id를 지운 이유는 option Table을 참조하는데 option table에 productId가 있어서 item을 unique하게 구분 할 수 있기 때문입니다

SQL 쿼리

CREATE TABLE item (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  orderId BIGINT NOT NULL,
  optionId BIGINT NOT NULL,
  quantity INT NOT NULL,
  createDate DATETIME NOT NULL,
  updateDate DATETIME NOT NULL,
  FOREIGN KEY (orderId) REFERENCES `order`(id),
  FOREIGN KEY (optionId) REFERENCES `option`(id)
);

질문

1. 정규화를 어느 정도해야 읽기 성능과 쓰기 성능의 Tradeoff를 적절히 조절할 수 있는가
   • 3단계 정도
2. JOIN vs FK
   Order 테이블과 item 테이블을 사용 할 때 FK(외래 키)와 JOIN 후 조회를 하는 것중 어떤게 더 효율적인가
   • 여러 테이블에 있는 데이터를 1번에 조회 한다던가 여러테이블의 다수의 조건을 통해 조회하는 경우 JOIN을 사용 합니다.
   • FK를 사용하지 않는 경우는 DB에서는 실제로 연관관계를 맺지 않고 있기 떄문에 비지니스 로직에서 검증이 필요합니다.
   • FK가 맺어져 있는 경우 데이터 삭제등 제약조건이 있어서 실제 서비스에서는 FK는 맺지 않고 사용하기도 합니다.
3. 만약 비밀번호 필드가 8글자이하의 조건이 있다면, 추후 비즈니스 변경을 염두에 두고 varchar(255)로 설정을 해야할까요? 아니면 varchar(8)로 설정을 해야할까요?
   • 일단은 비즈니스 변경을 염두에 두고 여유롭게 하는게 맞고, 데이터베이스 스키마도 인프라이기에 비지니스 로직이 담기면 안된다.
   • 8글자 조건 설정은 에플리케이션에서 설정
4. 예를 들어서 상품 가격 같은 경우에는 데이터 타입으로 varchar로 정의하나요? 아니면 bigint로 정의하는가
   • bigin가 맞습니다.
   • Varchar로 하면 일반적으로 상품 가격을 올려야 하는 상황에서 애플리케이션에서만 가능, 데이터베이스에서 원자적 연산이 가능하기 위해서는 bigint를 사용해야 한다.
   • 연산이 필요할 때는 bigint를 사용하자.
5. 데이터베이스에 관한 강의나 교재를 추천해주세요!
   • 전반적인 개념을 배울 때는 책으로 공부 추천
   • 그 때 그 때 필요한 것들은 책 뿐만 아니라 인터넷에서 블로그들을 많이 찾아보는 것을 추천
   • 책은 Real My SQL 추천

부족한 점이나 잘못 된 점을 알려주시면 시정하겠습니다 :>