화면대로 테이블을 만들었다

주문 관리 화면 시안을 받아서 그대로 MySQL 테이블을 만들었다. 화면 항목이 열아홉 개라 컬럼도 열아홉 개다. 두 달쯤 지나 항목 하나를 옮기자는 얘기가 나오면서 이상해졌다.

Open Table of contents

화면을 그대로 옮겼다
정해진 수를 넘을 수 없었다
행으로 옮겼다
검증 — 옮기면서 한 대조
정리

화면을 그대로 옮겼다

시안에 이런 항목들이 있었다.

주문번호 / 주문일 / 주문자 / 연락처 / 배송지
상품1 / 수량1 / 금액1
상품2 / 수량2 / 금액2
상품3 / 수량3 / 금액3
결제수단 / 결제금액 / 상태

화면에 상품 줄이 세 개라 컬럼도 세 벌 만들었다.

CREATE TABLE orders (
  order_no    INT NOT NULL AUTO_INCREMENT,
  ...
  product1_no INT NULL,  qty1 INT NULL,  price1 INT NULL,
  product2_no INT NULL,  qty2 INT NULL,  price2 INT NULL,
  product3_no INT NULL,  qty3 INT NULL,  price3 INT NULL,
  ...
);

product1_no 부터 product3_no 까지가 화면의 세 줄을 그대로 옮긴 것이다. 화면 배치를 저장 형식으로 삼은 것이 원인이었다.

정해진 수를 넘을 수 없었다

주문에 상품이 네 개 들어오면 못 담는다. 화면이 세 줄이었을 뿐 주문 자체에 그런 제한은 없었다.

이 상태로 할 수 있는 것이 몇 가지 있었다.

컬럼을 더 만든다        다섯 개가 오면 또 만든다
주문을 나눈다           주문번호가 두 개가 되어 결제와 안 맞는다
화면에 세 개까지만 담게 한다   사업을 코드에 맞추는 것이다

조회도 안 됐다. 어떤 상품이 몇 번 팔렸는지 세려고 하니 이렇게 됐다.

SELECT product1_no, COUNT(*) FROM orders WHERE product1_no = ? GROUP BY product1_no
UNION ALL
SELECT product2_no, COUNT(*) FROM orders WHERE product2_no = ? GROUP BY product2_no
UNION ALL
SELECT product3_no, COUNT(*) FROM orders WHERE product3_no = ? GROUP BY product3_no

컬럼이 다르니 GROUP BY 를 세 번 해서 합쳐야 한다. 컬럼이 늘면 쿼리도 늘고 인덱스도 세 벌 필요했다.

값이 없는 컬럼에 NULL 과 0 이 섞여 들어가 있는 것도 발견했다. 어느 쪽이 없다는 뜻인지 코드마다 달랐는데 컬럼으로 나눠 두니 그런 어긋남이 눈에 안 띄었다.

행으로 옮겼다

주문과 주문 상품을 나눴다.

CREATE TABLE order_item (
  order_item_no INT NOT NULL AUTO_INCREMENT,
  order_no      INT NOT NULL,
  product_no    INT NOT NULL,
  qty           INT NOT NULL,
  price         INT NOT NULL,
  PRIMARY KEY (order_item_no),
  KEY idx_order (order_no),
  KEY idx_product (product_no)
);

order_item 에 상품이 몇 개든 행으로 들어간다. 집계도 한 번이다.

SELECT product_no, SUM(qty) FROM order_item GROUP BY product_no;

화면에 세 줄까지만 보이는 것은 그대로 둬도 된다. 저장 형식과 무관하기 때문이다.

$items = $this->order_model->get_items($orderNo);
$view  = array_slice($items, 0, 3);
$more  = count($items) - 3;

array_slice 로 세 개까지 보이고 나머지는 외 N건으로 표시한다.

화면이 몇 줄인지는 표시 방식이고 몇 개까지 담을 수 있는지는 자료 구조다. 이 둘을 같은 것으로 다루면 화면이 바뀔 때마다 스키마를 고친다.

검증 — 옮기면서 한 대조

기존 자료를 새 테이블로 옮겼다.

INSERT INTO order_item (order_no, product_no, qty, price)
SELECT order_no, product1_no, qty1, price1 FROM orders WHERE product1_no IS NOT NULL
UNION ALL
SELECT order_no, product2_no, qty2, price2 FROM orders WHERE product2_no IS NOT NULL
UNION ALL
SELECT order_no, product3_no, qty3, price3 FROM orders WHERE product3_no IS NOT NULL;

옮긴 뒤에 개수를 대조했다.

-- 옛 구조에서 값이 든 칸 수
SELECT
  SUM(product1_no IS NOT NULL) +
  SUM(product2_no IS NOT NULL) +
  SUM(product3_no IS NOT NULL) AS old_cnt
FROM orders;

-- 새 테이블 행 수
SELECT COUNT(*) FROM order_item;

두 값이 같아야 한다. 처음에 달라서 보니 product2_no 가 0으로 들어간 행이 있었다. NULL 이 아니라 0이라 IS NOT NULL 에 걸렸는데 실제 상품은 아니었다.

WHERE product2_no IS NOT NULL AND product2_no > 0

대조를 안 했으면 상품 번호 0인 행이 들어갔을 것이다.

주문 테이블의 총 결제금액도 다시 봤다. 항목별 금액의 합과 같아야 하는데 다른 행이 있었다.

SELECT o.order_no, o.pay_amount, SUM(i.price * i.qty) AS item_sum
FROM orders o
JOIN order_item i ON i.order_no = o.order_no
GROUP BY o.order_no
HAVING o.pay_amount != item_sum;

배송비와 할인이 반영돼서 다른 것이었고 그건 정상이라 계산식을 명시해 뒀다.

pay_amount = SUM(price*qty) + delivery_fee - discount

이 식을 적어 두니 어긋난 행을 찾는 쿼리도 정확해졌다. 옮기는 작업이 기존 자료의 문제를 함께 드러낸 셈이다.

정리

화면 항목 수를 컬럼 수로 옮기면 그 수를 넘는 자료를 못 담는다
화면 배치와 저장 형식은 별개다
같은 종류가 여러 벌 컬럼으로 있으면 집계 쿼리가 그만큼 늘어난다
없다는 뜻이 NULL 과 0 으로 갈려 들어간다
개수가 정해지지 않은 것은 행으로 담는다
화면에 몇 개까지 보일지는 표시 방식이다. 저장 형식과 분리한다
옮긴 뒤 개수를 대조한다. NULL 아닌 0 같은 값이 섞여 있다
합계 컬럼이 있으면 항목 합과의 관계식을 명시한다