관계가 한 테이블로 표현되지 않았다

product 에 supplier_no 컬럼이 있었다. 같은 상품을 두 공급처에서 받게 되면서 이 구조가 안 맞기 시작했다.

Open Table of contents

상품 하나에 공급처 하나였다
관계를 행으로 옮겼다
운영 중이라 나눠서 옮겼다
일대다가 되면 생기는 것
정리

상품 하나에 공급처 하나였다

처음 구조는 상품 한 행에 공급처 하나와 공급가 하나를 두는 것이었다.

CREATE TABLE product (
  product_no   INT NOT NULL AUTO_INCREMENT,
  name         VARCHAR(100) NOT NULL,
  supplier_no  INT NOT NULL,
  supply_price INT NOT NULL,
  ...
  PRIMARY KEY (product_no)
);

supplier_no 와 supply_price 가 한 행에 하나씩이라는 전제가 컬럼에 박혀 있다. 처음에는 그게 맞았다.

같은 타이어를 두 곳에서 받게 됐다. 한쪽에 재고가 없으면 다른 쪽에서 받고 공급가도 다르다. 이 상태를 지금 구조에 넣는 방법을 몇 가지 생각해 봤다.

상품을 두 개로 만들면 화면에 같은 상품이 두 번 나오고 재고도 나뉜다. 컬럼을 늘리면 세 곳이 됐을 때 또 늘려야 한다. 하나만 적고 나머지를 메모로 두면 조회가 안 된다.

셋 다 이상했고 이상한 이유는 같았다. 구조가 일대일인데 실제 관계가 일대다다. 넣을 자리를 찾는 것이 아니라 자리를 다시 만들어야 하는 상황이었다.

관계를 행으로 옮겼다

관계를 별도 테이블로 뺐다.

CREATE TABLE product_supplier (
  product_no   INT NOT NULL,
  supplier_no  INT NOT NULL,
  supply_price INT NOT NULL,
  priority     TINYINT NOT NULL DEFAULT 1,
  use_yn       CHAR(1) NOT NULL DEFAULT 'Y',
  PRIMARY KEY (product_no, supplier_no),
  KEY idx_supplier (supplier_no)
);

상품 하나에 공급처가 여럿이면 행이 여럿이 된다. PRIMARY KEY (product_no, supplier_no) 로 같은 조합이 두 번 안 들어가게 했고 priority 로 어디를 먼저 쓸지 정하게 했다. product 에서는 supplier_no 와 supply_price 를 뺐다.

운영 중이라 나눠서 옮겼다

운영 중이라 한 번에 못 바꿨다. 새 테이블을 만들어 값을 옮기고, 읽는 코드를 새 테이블로 바꾸고, 쓰는 코드를 바꾸고, 마지막에 옛 컬럼을 지우는 순서로 했다.

읽기를 바꾼 뒤 쓰기를 바꾸기 전까지는 두 곳이 어긋날 수 있어서 그 기간에는 양쪽에 썼다.

여기서 replace 를 쓴 것에는 걸리는 점이 있었다. REPLACE 는 같은 키의 옛 행을 지우고 새로 넣는 것이라 적지 않은 컬럼이 기본값으로 돌아간다. use_yn 을 안 주면 'Y' 가 되는 것이다. 그래서 바꿀 값과 유지할 값을 전부 적어 넘겼다. 영향 행 수도 삭제와 삽입을 합친 값이라 그것으로 성패를 판정하지 않았다.

$this->db->update('product', ['supplier_no' => $sup, 'supply_price' => $price], ['product_no' => $no]);

$this->db->replace('product_supplier', [
    'product_no' => $no, 'supplier_no' => $sup,
    'supply_price' => $price, 'priority' => 1,
]);

그동안 값이 어긋나는지 매일 대조했다.

SELECT p.product_no, p.supplier_no, ps.supplier_no
FROM product p
LEFT JOIN product_supplier ps
  ON ps.product_no = p.product_no AND ps.priority = 1
WHERE p.supplier_no != IFNULL(ps.supplier_no, -1);

0 건이 며칠 이어진 뒤에 다음 단계로 넘어갔다. 그 0 이 쿼리가 제대로 도는 것인지 보려고 시험 자료에서 한 건을 일부러 어긋나게 두고 잡히는 것을 먼저 확인했다.

일대다가 되면 생기는 것

LEFT JOIN 에서 행이 늘어난다. 공급처가 둘이면 목록에 상품이 두 번 나온다.

LEFT JOIN product_supplier ps
  ON ps.product_no = p.product_no AND ps.priority = 1 AND ps.use_yn = 'Y'

목록에서는 우선순위 1만 쓰고 상세 화면에서는 전부 보여 주기로 했다. 화면마다 필요한 것이 다르다.

우선순위가 겹치는 경우도 남았다. priority 에 제약이 없어서 두 행이 다 1 이 되면 목록에서 다시 중복이 난다. (product_no, priority) 에 UNIQUE KEY 를 거는 방법을 봤는데 그러면 2 가 여럿인 것은 못 막고 순위를 바꾸는 중간 상태에서 걸린다. 대신 HAVING COUNT(*) > 1 확인 쿼리를 두고 crontab 으로 봤다.

SELECT product_no, COUNT(*) FROM product_supplier
WHERE priority = 1 AND use_yn = 'Y'
GROUP BY product_no HAVING COUNT(*) > 1;

제약으로 막는 것과 확인으로 잡는 것 사이에서 운영 중 순서가 바뀌는 값은 뒤쪽을 골랐다.

정리

예외를 넣을 방법이 전부 이상하면 구조가 실제 관계와 다르다는 신호다
컬럼을 늘려 대응하면 다음 경우에 또 늘린다
일대다는 PRIMARY KEY (product_no, supplier_no) 를 둔 별도 표의 행으로 옮긴다
운영 중 전환은 읽기 다음 쓰기 순으로 하고 그 사이에 양쪽에 쓴다
REPLACE 는 안 적은 컬럼을 기본값으로 되돌리니 전부 적어 넘긴다
영향 행 수는 삭제와 삽입의 합이라 성패 판정에 못 쓴다
대조는 매일 0 건을 확인하되 일부러 어긋나게 해 잡히는지 먼저 본다
LEFT JOIN 에서 행이 늘어나니 목록은 priority = 1 만 쓴다
운영 중 바뀌는 값은 UNIQUE KEY 보다 주기 확인이 맞을 때가 있다