2021 04 06 - sqlAntipatterns 두개 이상의 테이블이 거의 유사한 구조를 가질때

엔터티-속성-값

예를들어, 날짜별로 버그리포팅의 개수를 센다고 가정하자.

아래의 쿼리를 가장 먼저 떠올릴것이다.

SELECT date_reported, COUNT(*)
FROM Bugs
GROUP BY date_reported;

하지만 날짜가 서로 다른 형식으로 저장되어 컴퓨터에서 두 날짜를 비교 할 수 없다면 어떻게 될까?

또는, BUG와 FEATER 두개의 테이블이 있는데, 이 테이블 두개는 거의 비슷한 컬럼구조를 가진다.

왠지 하나로 합치고싶은 느낌적인 느낌이 들것이다.

목표: date_reported가 형식이 여러개여도 의도한대로 동작하게 하자!

안티패턴 : 범용 속성 테이블 사용. 이 설계는 엔터티-설계-값 또는 줄여서 EVA로 불린다.

별도 테이블을 생성해 속성을 행으로 저장. 각 행은 세개의 컬럼을 갖는다.

• 엔터티 : 부모 테이블에 대한 FK다.
• 속성 : 일반적인 테이블에서의 컬럼 이름.
• 값 : 속성에 대한 값.

CREATE TABLE Issues (
  issue_id    SERIAL PRIMARY KEY
);

INSERT INTO Issues (issue_id) VALUES (1234);

CREATE TABLE IssueAttributes (
  issue_id    BIGINT UNSIGNED NOT NULL,
  attr_name   VARCHAR(100) NOT NULL,
  attr_value  VARCHAR(100),
  PRIMARY KEY (issue_id, attr_name),
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

INSERT INTO IssueAttributes (issue_id, attr_name, attr_value)
  VALUES
    (1234, 'product',          '1'),
    (1234, 'date_reported',    '2009-06-01'),
    (1234, 'status',           'NEW'),
    (1234, 'description',      'Saving does not work'),
    (1234, 'reported_by',      'Bill'),
    (1234, 'version_affected', '1.0'),
    (1234, 'severity',         'loss of functionality'),
    (1234, 'priority',         'high');

이런짓을 왜하는지 감이 안잡힌다.

아무튼 책에 나와있는 장점은,

• 적은 컬럼을 갖고있음
  
• 새로운 컬럼을 추가 할 필요 없음.
• 특정 속성이 해당 행에 적용되지 않을떄, NULL을 인위적으로 채워야 하는 번거로움 없음.

범용속성테이블에서 특정 속성을 조회해보기!

IssueAttributes 테이블에서 모든 버그와 버그 보고일자를 조회해보기

SELECT issue_id, attr_value AS "date_reported"
FROM IssueAttributes
WHERE attr_name = 'date_reported'; -- 속성명을 조건으로 준다.

범용속성테이블에서 여러 속성을 조회해보기!

역시나, 설마 이렇게 쓰는 곳이 있다고? 라는 생각이 들 정도로 어이없는 구조다..쩜쩜..

번역해주신분이 주석에 달아주신 쿼리가 조금 더 인간적이다.

case를 사용해서 속성을 구분했다. 왜 groupby를 했는지는 모르겠다..?

SELECT i.issue_id,
  i1.attr_value AS "date_reported",
  i2.attr_value AS "status",
  i3.attr_value AS "priority",
  i4.attr_value AS "description"
FROM Issues AS i
  LEFT OUTER JOIN IssueAttributes AS i1
    ON i.issue_id = i1.issue_id AND i1.attr_name = 'date_reported'
  LEFT OUTER JOIN IssueAttributes AS i2
    ON i.issue_id = i2.issue_id AND i2.attr_name = 'status'
  LEFT OUTER JOIN IssueAttributes AS i3
    ON i.issue_id = i3.issue_id AND i3.attr_name = 'priority';
  LEFT OUTER JOIN IssueAttributes AS i4
    ON i.issue_id = i4.issue_id AND i4.attr_name = 'description';
WHERE i.issue_id = 1234;

비관계형 데이트 관리가 필요한가? -> 비관계형 기술을 사용하라.

• 분산 칼럼지향 데이터베이스 -> Cassandra
• 문서지향 데이터베이스. 분산 키-값 저장소 -> Couch DB
• 문서지향 메모리 데이터베이스 -> Redis

해결해보자

1) 하나의 테이블에 때려넣기.(Single Table Inheriance)

당연히 컬럼 하나는 어떤 테이블을 의도했는지 나타내는데 사용해야 한다.

만약 BUG 객체를 테이블에 저장한다면, FEATER전용 컬럼은 NULL이 들어가야 겠지.

CREATE TABLE Issues (
  issue_id         SERIAL PRIMARY KEY,
  reported_by      BIGINT UNSIGNED NOT NULL,
  product_id       BIGINT UNSIGNED,
  priority         VARCHAR(20),
  version_resolved VARCHAR(20),
  status           VARCHAR(20),
  issue_type       VARCHAR(10),  -- BUG or FEATURE
  severity         VARCHAR(20),  -- only for bugs
  version_affected VARCHAR(20),  -- only for bugs
  sponsor          VARCHAR(50),  -- only for feature requests
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id)
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

2) 클래스 상속 흉내내기 -> 나의 Best Pick

의도를 파악하기 쉽고, 하나의 테이블에 때려넣을때에 발생했던 NULL문제도 없다.

해당 issue_id가 BUG객체를 갖는지, FEATER객체를 갖는지 궁금하다고? 두 테이블을 JOIN해서 가져오면 된다!

SELECT i.*, b.*, f.*
FROM Issues AS i
  LEFT OUTER JOIN Bugs AS b USING (issue_id)
  LEFT OUTER JOIN FeatureRequests AS f USING (issue_id);

CREATE TABLE Issues (
  issue_id         SERIAL PRIMARY KEY,
  reported_by      BIGINT UNSIGNED NOT NULL,
  product_id       BIGINT UNSIGNED,
  priority         VARCHAR(20),
  version_resolved VARCHAR(20),
  status           VARCHAR(20),
  FOREIGN KEY (reported_by) REFERENCES Accounts(account_id),
  FOREIGN KEY (product_id) REFERENCES Products(product_id)
);

CREATE TABLE Bugs (
  issue_id         BIGINT UNSIGNED PRIMARY KEY,
  severity         VARCHAR(20),
  version_affected VARCHAR(20),
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

CREATE TABLE FeatureRequests (
  issue_id         BIGINT UNSIGNED PRIMARY KEY,
  sponsor          VARCHAR(50),
  FOREIGN KEY (issue_id) REFERENCES Issues(issue_id)
);

3) JSON혹은 XML로 넣기!

서브 타입수가 많거나 새로운 속성을 지원해야 하는 경우가 많다면 JSON으로 변환해서 TEXT 혹은 CLOB으로 저장한다.

직렬화된 LOB라고 부른다.