MySQL 계층형 구조 테이블 작성해서 JSON데이터로 확인하기

테이블 구조는 간단합니다. Products 테이블에 있는 cat_id가 Categories 테이블에 있는 cat_id를 참조합니다. 현재 Categories 테이블을 참조한 상태로 조인한 쿼리는 다음과 같습니다.

CREATE TABLE Products (
    prod_id INT,
    prod_name VARCHAR(128),
    prod_price INT,
    cat_id INT
    PRIMARY KEY (prod_id),
    FOREIGN KEY (cat_id) REFERENCES Categories(cat_id)
    );

CREATE TABLE Categories (
    cat_id INT,
    cat_name VARCHAR(64),
    PRIMARY KEY (cat_id)
);

기본키 설정과 외래키 제약까지 걸어주고 나서 가장 쉽게 접근할 수 있는 방법으로 데이터들을 뽑아내 읽어옵니다. LEFT OUTER JOIN은 다음 링크로 들어가면 어떤 종류의 조인인지 확인할 수 있습니다. 다음과 같이 페이크 데이터를 몇개 입력해줍니다.

INSERT INTO Products
VALUES
(1, 'TEST1', 500, 11),
(2, 'TEST2', 1000, 12),
(3, 'TEST3', 1500, 13),
(4, 'TEST4', 2000, 14),
(5, 'TEST5', 2500, 15);

INSERT INTO Categories
VALUES
(10, 'bottom'),
(11, 'bottom_leggings'),
(12, 'bottom_jean'),
(13, 'bottom_half'),
(14, 'bottom_cotton'),
(15, 'bottom_skirt');

SELECT products.prod_id, products.prod_name, products.prod_price, cat.cat_id, cat.cat_name
FROM Products AS p
LEFT OUTER JOIN Categories AS cat
ON products.cat_id = cat.cat_id

쿼리를 실행하면 개발환경에서 JSON데이터로 뽑아오면 다음과 같은 데이터가 나올겁니다. 참고로 LEFT OUTER JOIN과 LEFT JOIN은 똑같습니다.

{
  "products": {
    "prod_id": 1,
    "prod_name": "TEST1",
    "prod_price": 500,
    "cat": {
      "cat_id": 11,
      "cat_name": "bottom_leggings"
    }
  }
}

위와 같은 JSON데이터는 키의 중복이 많아지죠. 예를 들어 아래와 같은 부분들이 각 상품마다 포함되어있으니 상품이 몇 개밖에 안된다면 상관없겠지만 무수히 많은 상품이 있다면 앱의 성능에 영향을 줄겁니다.

{
  "cat": {
    "cat_id": 11,
    "cat_name": "bottom_leggings"
  }
}

그래서 최대한 중복을 피하고자 계층적 구조로 JSON데이터를 가져와봤습니다.카테고리가 가장 상단에 있으니 중복이 상품 수만큼 줄어들겠죠. 계층적 구조 = 트리 구조를 RDBMS에서 사용하기엔 적합하지 않은 부모-자식 관계를 필연적으로 가집니다. 왜냐면 RDBMS의 테이블은 단순 플랫 목록일 뿐이니까요. 하지만 리덕스 스토어에 저장해서 필요할 떄마다 편하고 빠르게 꺼내쓰려면 아래 JSON데이터를 뽑아내는 쿼리를 만들어야 합니다.

{
  "cat": [
    {
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat": [
        {
          "cat_id": "11",
          "cat_name": "bottom_leggings",
          "products": [
            {
              "prod_id": 1,
              "prod_name": "TEST1",
              "prod_price": 500
            }
          ]
        }
      ]
    }
  ]
}

계층적 구조가 필요하다고 했죠. 그러려면 테이블 구조를 수정해야합니다. Categories 테이블에 parent 컬럼을 추가하고 테이블의 데이터를 싹 갈아엎었다가 새로운 데이터를 입력해줍니다.

ALTER TABLE Categories
ADD COLUMN (
    parent INT DEFAULT NULL
);

DELETE FROM Categories;

INSERT INTO Categories
VALUES
(10, 'bottom', NULL),
(11, 'bottom_leggings', 10),
(12, 'bottom_jean', 10),
(13, 'bottom_half', 10),
(14, 'bottom_cotton', 10),
(15, 'bottom_skirt', 10);

SELECT문을 써서 불러온 정보를 JSON데이터로 가져온다면 아래와 같이 되겠죠.

SELECT * FROM Categories;

[
  {
    "cat_id": 10,
    "cat_name": "bottom",
    "parent": null
  },
  {
    "cat_id": 11,
    "cat_name": "bottom_leggings",
    "parent": 10
  },
  {
    "cat_id": 12,
    "cat_name": "bottom_jean",
    "parent": 10
  },
  {
    "cat_id": 13,
    "cat_name": "bottom_half",
    "parent": 10
  },
  {
    "cat_id": 14,
    "cat_name": "bottom_cotton",
    "parent": 10
  },
  {
    "cat_id": 15,
    "cat_name": "bottom_skirt",
    "parent": 10
  }
]

다음과 같은 쿼리를 사용하면 우리가 원하는 구조로 JSON데이터를 반환합니다. "sub_cat" 키는 조인을 사용할때 참조하는 테이블 별명을 반영하더라구요. LEFT JOIN 절에 있는 Categories 테이블은 sub_cat으로 별명을 정했습니다. ORM에서 작업한다면 "cat"과 "sub_cat"과의 관계를 반드시 1:N의 관계로 설정을 해주어야 합니다. 설정을 한경우와 하지 않은 경우를 아래의 코드로 비교해보겠습니다.

SELECT cat.cat_id, cat.cat_name, sub_cat.cat_id, sub_cat.cat_name
FROM Categories AS cat
LEFT JOIN Categories AS sub_cat
ON sub_cat.parent = cat.cat_id;

// 1:N 관계
{
  "cat": [
    {
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat": [
        {
          "cat_id": "11",
          "cat_name": "bottom_leggings"
        },
        {
          "cat_id": "12",
          "cat_name": "bottom_jean"
        },
        {
          "cat_id": "13",
          "cat_name": "bottom_half"
        },
        {
          "cat_id": "14",
          "cat_name": "bottom_cotton"
        },
        {
          "cat_id": "15",
          "cat_name": "bottom_skirt"
        }
      ]
    }
  ]
}

// 1:1 관계
{
  "cat": [
    {
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat":
        {
          "cat_id": "11",
          "cat_name": "bottom_leggings"
        }
    },{
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat":
        {
          "cat_id": "12",
          "cat_name": "bottom_jean"
        }
    },{
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat":
        {
          "cat_id": "13",
          "cat_name": "bottom_half"
        }
    },{
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat":
        {
          "cat_id": "14",
          "cat_name": "bottom_cotton"
        }
    },{
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat":
        {
          "cat_id": "15",
          "cat_name": "bottom_skirt"
        }
    }
}

중복이 어마어마하게 심해집니다. 자료양이 5개가 아니라 100만개였다면 성능상 이슈가 어마어마했을 겁니다.

카테고리 부분을 만졌으니 상품부분을 카테고리 쿼리에 합쳐봐야죠. 그래야 위에서 받고자 한 JSON데이터를 받아낼 수 있으니까요.

SELECT cat.cat_id, cat.cat_name, sub_cat.cat_id, sub_cat.cat_name
FROM Categories AS c
LEFT JOIN Categories AS sub_cat
ON sub_cat.parent = cat.cat_id
LEFT JOIN Products AS products
ON products.cat_id = sub_cat.cat_id;

라고 하면 우리가 원하는 JSON데이터 구조를 받아올 수 있습니다만 아까 Products.cat_id가 Categories.cat_id를 참조하는 FK로, Categories.cat_id가 PK로 설정됐습니다.

메인테이블이 Categories라서 전자를 PK로 후자를 FK로 바꿨습니다. 그럼 다음과 같은 트리구조를 JSON으로 볼 수 있게 됩니다. 이 포스팅에서 사용한 모델은 인접목록모델(Adjancency List Model)이었는데요. 더 난이도 있지만 훨씬 유지보수하기 좋은 중첩세트모델(Nested Set Model)도 조만간 구현해볼 예정입니다.

카테고리를 FK로, 프로덕트를 PK로 바꾸는 쿼리

{
  "cat": [
    {
      "cat_id": "10",
      "cat_name": "bottom",
      "sub_cat": [
        {
          "cat_id": "11",
          "cat_name": "bottom_leggings",
          "products": [
            {
              "prod_id": 1,
              "prod_name": "TEST1",
              "prod_price": 500
            }
          ]
        }
      ]
    }
  ]
}