fcmm테이블 모델링

Data Analysis/FCMM

fcmm테이블 모델링

mansoorrr 2023. 7. 18. 13:57

스크래핑을 통해 수집한 데이터 구조는 다음과 같다.

처음 table을 만들때에 정확하게 모델링을 하지 않고 시작했기 때문에 동일한 컬럼들이 겹쳐있어 어지럽다.

1. 대분류, 중분류, 소분류 컬럼이 category, product 두개의 테이블에 공통으로 존재한다. category_code만들어서 해결한다.

먼저 category 테이블의 데이터를 조회해본다. 아래와 같이 35개의 데이터가 도출됐다

SELECT * FROM CATEGORY;

category	segment	subsegment
럭키데이	티셔츠
럭키데이	팬츠
베스트
신상품
프로모션

원래 수집했던 테이블은 놔두고 싶기 때문에 category 테이블의 세개 컬럼을 하나의 뭉치로 하는 간단한 category_code컬럼이 만들어진 테이블을 다시 생성한다. 코드와 결과는 아래와 같다.

CREATE TABLE IF NOT EXISTS CATEGORY_UNIQUE
SELECT *
FROM (
	SELECT category, segment, subsegment, LPAD(no, '3', '0') AS CATEGORY
    FROM (
    	SELECT *, @ROWNUM := @ROWNUM+1 AS no
        FROM CATEGORY, (SELECT @ROWNUM:=0) r
    ) A
)C;

category	segment	subsegment	category_code
럭키데이	티셔츠		001
럭키데이	팬츠		002
베스트			003
신상품			004
프로모션			005

product table의 경우 category code를 만들어주고 product2 라는 새로운 테이블을 만들어 준다.

이를위해 product 테이블을 기준으로 category 테이블과 left조인하여 category_code컬럼을 만들어 주고 select를 통해 필요한 컬럼만 추출한다. 이후 create table을 통해 테이블을 생성한다. 코드와 결과는 아래와 같다.

CREATE TABLE IF NOT EXISTS PRODUCT2
SELECT *
FROM (
	SELECT category_code, PRODUCT_NAME, PRODUCT_CODE, ORG_PRICE, DISCOUNT_PCT, DISCOUNT_PRICE, REVIEW_NUM, URL
	FROM PRODUCT P LEFT JOIN CATEGORY_UNIQUE C
		ON P.category = C.category
		AND P.segment = c.segment
		AND P.subsegment = C.subsegment
	ORDER BY PRODUCT_CODE
) F;

category_code	product_name	product_code	org_price	discount_pct	discount_price	review_num	url
001	fcmmxrdvz-유니콘-티셔츠-블랙	4019	49000	24500	50	0	/product/fcmmxrdvz-유니콘-티셔츠-블랙/4019/category/1000/display/1/
001	어웨이큰-프로그-티셔츠-화이트	4020	49000	24500	50	0	/product/어웨이큰-프로그-티셔츠-화이트/4020/category/1000/display/1/
001	아윌-런-티셔츠-화이트	4021	42000	21000	50	0	/product/아윌-런-티셔츠-화이트/4021/category/1000/display/1/
001	아윌-런-티셔츠-블랙	4022	42000	21000	50	0	/product/아윌-런-티셔츠-블랙/4022/category/1000/display/1/
001	써니-데이-티셔츠-화이트	4023	42000	21000	50	0	/product/써니-데이-티셔츠-화이트/4023/category/1000/display/1/

이제 category_unique 테이블과 product2 테이블의 관계를 설정한다.

category_unique의 category_code를 pk / product2의 category_code를 fk로 설정한다.

ALTER TABLE CATEGORY_UNIQUE ADD CONSTRAINT PRIMARY KEY(category_code);
ALTER TABLE PRODUCT2 ADD CONSTRAINT FOREIGN KEY(category_code) REFERENCES category_unique(category_code);

2. 컬럼명을 모두 대문자로 통일한다. 아래 쿼리를 실행해서 문자로된 쿼리를 반환시키고 반환한 결과를 복사하여 다시 실행한다. category_unique 테이블과 product2 테이블 모두 적용한다.

<category_unique>

SELECT
    CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `', COLUMN_NAME, '` `',
        UPPER(COLUMN_NAME), '` ', COLUMN_TYPE, ';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='CATEGORY_UNIQUE';

ALTER TABLE category_unique CHANGE `CATEGORY` `CATEGORY` varchar(20);

ALTER TABLE category_unique CHANGE `SEGMENT` `SEGMENT` varchar(20);

ALTER TABLE category_unique CHANGE `SUBSEGMENT` `SUBSEGMENT` varchar(20);

ALTER TABLE category_unique CHANGE `CATEGORY_CODE` `CATEGORY_CODE` varchar(3);

SELECT
	CONCAT('ALTER TABLE ', TABLE_NAME, ' CHANGE `',
    	COLUMN_NAME, '` `', UPPER(COLUMN_NAME),'`', COLUMN_TYPE, ';')
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='PRODUCT2';

ALTER TABLE product2 CHANGE `CATEGORY_CODE` `CATEGORY_CODE`varchar(3);

ALTER TABLE product2 CHANGE `PRODUCT_NAME` `PRODUCT_NAME`varchar(100);

ALTER TABLE product2 CHANGE `PRODUCT_CODE` `PRODUCT_CODE`varchar(20);

ALTER TABLE product2 CHANGE `ORG_PRICE` `ORG_PRICE`int;

ALTER TABLE product2 CHANGE `DISCOUNT_PCT` `DISCOUNT_PCT`int;

ALTER TABLE product2 CHANGE `DISCOUNT_PRICE` `DISCOUNT_PRICE`int;

ALTER TABLE product2 CHANGE `REVIEW_NUM` `REVIEW_NUM`int;

ALTER TABLE product2 CHANGE `URL` `URL`varchar(200);

티스토리