[Prodify] DB 설계 - 상품 테이블 (products)

1. 처음 테이블 계획

• 주문 (orders) 테이블에 product_id, product_name이 있었기 때문에 상품 테이블 만들 생각을 하지 못 함
• 상품 테이블을 따로 만들어서 관리하는게 관리하기 더 편할 것 같다고 판단함

---

2. 첫 번째 설계

2.1. 수정점

1. TEXT형은 최대 64KB 저장 가능한데 대부분의 상품 설명이 그렇게 길지 않을 가능성이 높음
2.  따라서 VARCHAR(1000) 을 사용해야할 것 같음
3. promotions 테이블을 따로 만들어서 2NF 적용해야할 것 같음

---

2.2. 개선점 고려

1. promo_price, promo_start, promo_end 필드가 products 테이블에 포함되어 있는데, 프로모션 정보가 테이블로 따로 분리될 수도 있음 (제 2정규화 이상부터 고려할 것)

---

2.3. 배운 점

1. TEXT 자료형은 최대 64kB, LONGTEXT 형은 최대 4GB 문자 저장 가능
2. text형을 선언한다는 것 자체가 varchar(4000) 이상 (default page size = 16K 일 경우)의 문자를 사용하겠다는 의미이므로 컬럼이 무지 크다는 것을 항상 생각해야 함
3. varchar 형에서 사용했던 like 검색을 대체할 인덱스 (fulltext, ngram parser, 스핑크스, 엘라스틱서치 등) 선언은 반드시 필요하고, 서브쿼리로 longtext가 있는 테이블 사용 지양, JOIN을 쓴다면 인덱스 스캔여부 확인, longtext형 컬럼을 굳이 쓰지 않아도 된다면 left(컬럼, 10) 등으로 잘라서 가져오는 것이 DB 부하를 줄일 수 있음
4. TEXT 타입 컬럼은 인덱스 생성 시 반드시 Prefix 길이를 지정해야 인덱스 생성이 가능, 그렇지 않으면 인덱싱 생성 불가

---

3. 두 번째 수정

CREATE TABLE products
(
    `product_id`     INT              NOT NULL    AUTO_INCREMENT COMMENT '상품 id', 
    `product_name`   VARCHAR(50)      NOT NULL    COMMENT '상품 이름', 
    `product_price`  DECIMAL(20,0)    NOT NULL    COMMENT '상품 원가', 
    `description`    VARCHAR(1000)    NOT NULL    COMMENT '상품 설명', 
    `promo_id`       INT              NULL        COMMENT '적용된 할인 id', 
    PRIMARY KEY (product_id),
    FOREIGN KEY (promo_id) REFERENCES promotions(promo_id) ON DELETE SET NULL
);SQL

• 할인 적용되지 않은 상품도 있을 수 있으므로 promo_id를 NULL 허용
• ON DELETE SET NULL →  할인이 종료되어서 삭제할 경우, 해당 할인이 적용되던 상품들의 promo_id를 NULL 로 변경

CREATE TABLE promotions
(
    `promo_id`         INT              NOT NULL    AUTO_INCREMENT COMMENT '할인 행사 id', 
    `promo_name`       VARCHAR(50)      NOT NULL    COMMENT '할인 행사 이름', 
    `discount_amount`  DECIMAL(5,2)    NOT NULL    COMMENT '할인율', 
    `promo_start`      DATE             NOT NULL    COMMENT '할인 행사 시작일', 
    `promo_end`        DATE             NOT NULL    COMMENT '할인 행사 종료일', 
    PRIMARY KEY (promo_id)
);SQL

•  DECIMAL(5,2)는 총 5자리 숫자에서 소수점 2자리를 허용 (예: 100.00, 25.50), 금융 데이터에 적합함

---

4. 참고

1. 상품 테이블 예시 - https://www.ibm.com/docs/ko/i/7.5?topic=tables-product-table-product

2. MySQL TEXT형 어떤걸 쓸까? - https://dung-beetle.tistory.com/72

3. VARCHAR vs TEXT, 뭐가 다를까? - https://yozm.wishket.com/magazine/detail/2726/