テーブルの継承について学ぶ

Dec 28, 2024

はじめに

データベース設計において、テーブルの継承は取り扱いが難しい内容です。とくに、異なる種類のデータを効率的に管理するためには、適切な継承戦略を選択することが求められます。

本記事では、単一テーブル継承、具象テーブル継承、クラステーブル継承の3つの主要な継承戦略について、それぞれのメリットとデメリットを比較しながら学びます。

また、実際の実装例を通じて、どのようにこれらの戦略を適用できるかをsqlc を使い、具体的に示します。

成果物

https://github.com/kntks/blog-code/tree/main/2024/12/database-polymorphism

環境

バージョン

	バージョン
Mac	Ventura 13.2.1
MySQL	8.0.32
Docker	26.0.0
Docker Compose	v2.24.5
Golang	1.23.4

環境構築

はじめに、以下のコマンドで MySQL を起動します。

docker compose up -d

sqlc をインストールします。

go mod init database-polymorphism
go install github.com/sqlc-dev/sqlc/cmd/sqlc@latest

以下のコマンドで　sqlc.yaml を作成します。

sqlc init

Getting started with MySQL を参考にして、sqlc.yaml を編集します。

sqlc.yaml

version: "2"
cloud:
    organization: ""
    project: ""
    hostname: ""
servers: []
sql: []
sql:
  - engine: "mysql"
    queries: "single_table_inheritance/query.sql"
    schema: "single_table_inheritance/schema.sql"
    gen:
      go:
        package: "gen"
        out: "single_table_inheritance/gen"

  - engine: "mysql"
    queries: "concrete_table_inheritance/query.sql"
    schema: "concrete_table_inheritance/schema.sql"
    gen:
      go:
        package: "gen"
        out: "concrete_table_inheritance/gen"

  - engine: "mysql"
    queries: "class_table_inheritance/query.sql"
    schema: "class_table_inheritance/schema.sql"
    gen:
      go:
        package: "gen"
        out: "class_table_inheritance/gen"
overrides:
    go: null
plugins: []
rules: []
options: {}

それぞれのディレクトリ（class_table_inheritance、concrete_table_inheritance、single_table_inheritance）を作成し、その配下に query.sql と schema.sql を作成します。

$ tree -L 2 -P 'query.sql|schema.sql'
.
├── class_table_inheritance
│   ├── cmd
│   ├── gen
│   ├── query.sql
│   └── schema.sql
├── concrete_table_inheritance
│   ├── cmd
│   ├── gen
│   ├── query.sql
│   └── schema.sql
└── single_table_inheritance
    ├── cmd
    ├── gen
    ├── query.sql
    └── schema.sql

それぞれの query.sql と schema.sql の詳細は以下のURLを参照してください。
https://github.com/kntks/blog-code/tree/main/2024/12/database-polymorphism

Tip

query.sql にはクエリの直前に -- name: <name> <command> というクエリアノテーションを入れる必要があります。

https://docs.sqlc.dev/en/stable/reference/query-annotations.html

もし query.sql に1つもクエリアノテーションが存在しない場合、error parsing queries: no queries contained in paths というエラーが出ます。

$ sqlc compile
# package gen
error parsing queries: no queries contained in paths /xxxx/database-polymorphism/query.sql

課題

チケット管理システムを作るために、Tickets テーブルをデータベースに定義したと仮定します。チケットには種類があり、それぞれの種類に応じてカラムが異なります。たとえば、バグレポートの場合はバグの内容を記録するカラム（bug_report）を必須にしたいと思いますが、その他の種類のチケットには不要です。このとき、bug_report に NOT NULL 制約をつけたいです。しかし、他の種類のチケットには bug_report カラムが必要ないため、NOT NULL にする必要がありません。

このようなカラムが数個ならいいのですが、チケットの種類が増えたり、カラムが増えたりすると、テーブルのカラムが膨大になり管理が難しくなります。

このような状況でのテーブル設計には、以下の3つの戦略が知られています。

1. Single Table Inheritance（単一テーブル継承）
   すべてのチケットタイプを1つのテーブルに格納し、タイプを識別するためのカラムを追加します。この方法はテーブル数が少なく、クエリが簡単ですが、NULL 値が増え、制約の適用が難しくなります。

2. Concrete Table Inheritance（具象テーブル継承）
   各チケットタイプごとに独立したテーブルを作成します。この方法は NULL 値が減り、制約の適用が容易ですが、テーブル数が増え、設計が複雑になります。

3. Class Table Inheritance（クラステーブル継承）
   共通カラムを持つ親テーブルと、各チケットタイプごとのサブテーブルを作成します。この方法は NULL 値が減り、スキーマの変更が容易ですが、JOINが増え、パフォーマンスに影響が出る可能性があります。

Single Table Inheritance （単一テーブル継承）

単一テーブル継承は、全タイプの属性を1つの個別の列に格納し、関連するすべてのサブタイプを1つのテーブルに格納します。
その列の中にどのサブタイプであるかを判定する列（typeなど）を使用します。

先ほど課題にあげたテーブル設計は単一テーブル継承です。

メリット

単一テーブル継承のメリットは以下が挙げられます。

• テーブル数の削減：すべてのクラスを1つのテーブルで管理するため、テーブル数が少なくなり、データベースの構造がシンプルになる。
• クエリの簡素化：データ取得の際にジョイン操作が不要になるため、クエリが簡潔になり、パフォーマンスが向上する可能性がある。
• スキーマの統一性：1つのテーブルに統一されているため、スキーマの管理が容易になる。

デメリット

単一テーブル継承には、どの列がどのサブタイプに属するのかを定義するメタデータが存在しません。

そのため以下のようなデメリットが考えられます。

• NULL値の増加：サブタイプごとに特有のカラムが存在するため、他のサブクラスには不要なカラムとなり、NULL値が多く発生する
• 制約の適用が困難：サブタイプ特有の必須項目に対して、NOT NULL 制約などを個別に設定することが難しくなる
• テーブルの肥大化：すべての属性を含めるため、カラム数が増加し、テーブルが肥大化する
• データの整合性リスク：不適切なデータ入力を防ぐための制約やバリデーションが困難になり、データの整合性が損なわれる可能性がある

ちなみに GitLab では 単一テーブル継承 の設計は避けるようにしているそうです。

以下の引用（翻訳）でその理由を説明しています。

STIテーブルの新規作成は認めない。なぜなら、以下のような理由からである

• テーブルに非常に多くの行が含まれる原因となり、テーブルは小さく保つべきです。
• 追加のインデックスが必要となり、軽量ロックの使用が増加し、それが飽和するとインシデントを引き起こす可能性があります。
• データを特定の値でフィルタリングする必要があり、それが読み取り時のページアクセスを増やし、オーバーヘッドを発生させます。
• オブジェクトの正しいクラスを読み込むために class_name を使用しますが、クラス名を保存することはコストが高く、不必要です。

引用：https://docs.gitlab.com/ee/development/database/single_table_inheritance.html

そして、以下のような代替案を提案しています。

単一テーブル継承を使用する代わりに、以下の代替案を検討してください

• 各タイプごとに別々のテーブルを使用する。
• *_type カラムを追加するのは避ける。これはコードの悪臭（code smell）とみなされ、将来的に新しいタイプが追加される可能性が示唆されます。将来リファクタリングすることが非常に困難になるかもしれません。
• すでに *_type カラムで事実上STIを使用しているテーブルがある場合、以下を検討してください
  • 既存のデータを複数のテーブルに分割する。
  • 新しいタイプを新しいテーブルとして追加しつつ、既存のテーブルを維持できるようにリファクタリングする（例えば、基底クラスのロジックを共通モジュールや関心事（concern）に移動する）。

引用:：https://docs.gitlab.com/ee/development/database/single_table_inheritance.html

実装例

単一テーブル継承は、全タイプの属性を1つの個別の列に格納するため、tickets テーブルを以下のように定義します。

bug_report と feature_request はそれぞれのサブクラスに属するカラムです。

single_table_inheritance/schema.sql

USE single_table_inheritance;

CREATE TABLE IF NOT EXISTS ticket_types (
    id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type        VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE IF NOT EXISTS tickets (
    id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    type_id         BIGINT NOT NULL,
    title           VARCHAR(255) NOT NULL,
    description     TEXT NOT NULL,
    bug_report      TEXT,
    feature_request TEXT
);

-- Insert Master Data
INSERT INTO ticket_types (id, type)
VALUES
    (1, 'bug'),
    (2, 'featureRequest');

bug チケットまたは、featureRequest チケットをそれぞれ取得するには以下のように JOIN 句と WHERE 句を使います。

single_table_inheritance/query.sql

-- name: ListBugTickets :many
SELECT
    t.id,
    tt.type,
    t.title,
    t.description,
    t.bug_report
FROM tickets t
    JOIN ticket_types tt
        ON t.type_id = tt.id
WHERE tt.type = 'bug'
ORDER BY t.id;

-- name: ListFeatureRequestTickets :many
SELECT
    t.id,
    tt.type,
    t.title,
    t.description,
    t.feature_request
FROM tickets t
    JOIN ticket_types tt
        ON t.type_id = tt.id
WHERE tt.type = 'featureRequest'
ORDER BY t.id;

sqlc を使って、クエリをコンパイルし、コードを実行します。

$ sqlc generate

$ go run single_table_inheritance/cmd/create_bug_ticket/main.go
$ go run single_table_inheritance/cmd/create_feature_request_ticket/main.go
$ go run single_table_inheritance/cmd/list_tickets/main.go
List of bug tickets
[
 {
  "id": 1,
  "type": "bug",
  "title": "title",
  "description": "description",
  "bugReport": "bug report"
 }
]
List of feature request tickets
[
 {
  "id": 2,
  "type": "featureRequest",
  "title": "title",
  "description": "description",
  "featureRequest": "feture reuqeust"
 }
]

詳しい実装については、こちら を参照してください。

Concrete Table Inheritance （具象テーブル継承）

具象テーブル継承は、サブタイプごとに独立したテーブルを作成し、それぞれのテーブルで専用のカラムや制約を管理する手法です。
すべての共通項目を基底クラス相当のテーブルに集約せず、必要に応じて重複したカラムをサブタイプ側に持たせます。

主に、すべてのサブタイプを跨いだ検索を行うことが少ない場合に適しています。

メリット

具象テーブル継承のメリットは以下が挙げられます。

• タイプカラムの削除：サブタイプごとにテーブルを分割するため、タイプカラムが不要になる
• NULLの削減：サブタイプごとにテーブルを分割するため、不要な NULL カラムが減る
• 制約の適用：個別のテーブルに特化した制約やバリデーションを適用しやすい
• 可読性の向上：サブタイプ独自の実装を分離でき、可読性が高まる

デメリット

具象テーブル継承のデメリットは以下が挙げられます。

• 設計が複雑になる：テーブル数が増え、スキーマ設計やマイグレーションが複雑になる
• 結合が増える：データをまとめて扱う場合は結合が増え、クエリが煩雑になる
• 共通カラム管理が難しくなる：すべてのタイプに共通するカラムを追加するとき、すべてのテーブルに影響が出る

実装例

具象テーブル継承は、サブタイプごとに独立したテーブルを作成するため、bug_tickets テーブルと feature_tickets テーブルを以下のように定義します。単一テーブル継承とは異なり、bug_report と feature_request はそれぞれのテーブルに属するカラムであり、タイプカラムを使用しません。

メリットにもあるように、具象テーブル継承は NULL 値が減り、制約の適用が容易です。 しかし、2つのチケットに共通するカラム（例：created_at など）を追加する場合、すべてのテーブルに影響が出るため、管理が難しくなります。

concrete_table_inheritance/schema.sql

USE concrete_table_inheritance;

CREATE TABLE bug_tickets (
    id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT NOT NULL,
    bug_report      TEXT NOT NULL
);

CREATE TABLE feature_tickets (
    id              BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title           VARCHAR(255) NOT NULL,
    description     TEXT NOT NULL,
    feature_request TEXT NOT NULL
);

bug チケット、featureRequest チケットはそれぞれ別のテーブルなので、データの取得はシンプルになります。
しかし、すべてのチケットを取得する際には、UNION を使用して、複数のテーブルを結合する必要があります。

concrete_table_inheritance/query.sql

-- name: ListBugTickets :many
SELECT *
FROM bug_tickets
ORDER BY id;

-- name: ListFeatureRequestTickets :many
SELECT *
FROM feature_tickets
ORDER BY id;

sqlc を使って、クエリをコンパイルし、コードを実行します。

$ sqlc generate

$ go run concrete_table_inheritance/cmd/create_bug_ticket/main.go
$ go run concrete_table_inheritance/cmd/create_feature_request_ticket/main.go
$ go run concrete_table_inheritance/cmd/list_tickets/main.go
List of bug tickets
[
 {
  "id": 1,
  "title": "title",
  "description": "description",
  "bugReport": "bug report"
 }
]
List of feature request tickets
[
 {
  "id": 1,
  "title": "title",
  "description": "description",
  "featureRequest": "feture reuqeust"
 }
]

詳しい実装については、こちら を参照してください。

Class Table Inheritance （クラステーブル継承）

クラステーブル継承は、データベース設計の一種で、オブジェクト指向プログラミングの継承概念をデータベースに適用したものです。この設計では、親クラスとサブクラスの関係をそれぞれ別々のテーブルに分割して管理します。

メリット

• NULLの削減：サブタイプごとにテーブルを分割するため、不要な NULL カラムが減る
• タイプカラムの削除：サブタイプごとにテーブルを分割するため、タイプカラムが不要になる
• 冗長カラムの削減：共通カラムを親テーブルに集約でき、冗長なカラムを減らせる
• スキーマの変更容易性：サブクラスを追加しやすく、スキーマの変更が比較的容易
• 変換が容易：データベースからアプリケーションのクラスにマッピングしやすい

デメリット

• JOINの増加：派生クラスごとに別のテーブルが増え、JOIN が多くなる
• パフォーマンス：パフォーマンスを考慮した最適なインデックス設計が難しくなる
• 保守性：テーブル分割により保守やマイグレーションが複雑化しやすい

実装例

具象テーブル継承と同じように、bug_report と feature_request はそれぞれのサブクラスに属するカラムで NOT NULL 制約をつけます。

class_table_inheritance/schema.sql

USE class_table_inheritance;

CREATE TABLE IF NOT EXISTS tickets (
    id          BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    title       VARCHAR(255) NOT NULL,
    description TEXT NOT NULL
);

CREATE TABLE IF NOT EXISTS bug_tickets (
    ticket_id   BIGINT NOT NULL PRIMARY KEY,
    bug_report  TEXT NOT NULL,
    FOREIGN KEY (ticket_id)
        REFERENCES tickets(id)
        ON DELETE CASCADE
);

CREATE TABLE IF NOT EXISTS feature_tickets (
    ticket_id       BIGINT NOT NULL PRIMARY KEY,
    feature_request TEXT NOT NULL,
    FOREIGN KEY (ticket_id)
        REFERENCES tickets(id)
        ON DELETE CASCADE
);

チケットを取得する際には、INNER JOIN を使用して、サブクラスのカラムを取得します。

Tip

MySQLでは、INNER JOIN と JOIN は同じ意味を持ちます。
https://dev.mysql.com/doc/refman/8.0/ja/join.html

class_table_inheritance/query.sql

-- name: ListBugTickets :many
SELECT
    t.id,
    t.title,
    t.description,
    bt.bug_report
FROM tickets t
    INNER JOIN bug_tickets bt
        ON t.id = bt.ticket_id
ORDER BY id;

-- name: ListFeatureRequestTickets :many
SELECT
    t.id,
    t.title,
    t.description,
    ft.feature_request
FROM tickets t
    INNER JOIN feature_tickets ft
        ON t.id = ft.ticket_id
ORDER BY id;

sqlc を使って、クエリをコンパイルし、コードを実行します。

$ sqlc generate

$ go run class_table_inheritance/cmd/create_bug_ticket/main.go
$ go run class_table_inheritance/cmd/create_feature_request_ticket/main.go
$ go run class_table_inheritance/cmd/create_bug_ticket/main.go
$ go run class_table_inheritance/cmd/list_tickets/main.go
List of bug tickets
[
 {
  "id": 1,
  "title": "title",
  "description": "description",
  "bugReport": "bug report"
 },
 {
  "id": 3,
  "title": "title",
  "description": "description",
  "bugReport": "bug report"
 }
]
List of feature request tickets
[
 {
  "id": 2,
  "title": "title",
  "description": "description",
  "featureRequest": "feture reuqeust"
 }
]

詳しい実装については、こちら を参照してください。

Caution

今回のサンプルでは、チケットテーブルに対して2つのサブクラス（バグレポート、機能リクエスト）を作成しました。 そのためすべてのチケットを取得する際には、LEFT JOIN を使用して、サブクラスのカラムを取得します。

デメリットにもあげたように、JOIN が増えたり、パフォーマンスに影響が出る可能性があります。

mysql> SELECT *
    -> FROM tickets t
    -> LEFT JOIN bug_tickets bt ON t.id = bt.ticket_id
    -> LEFT JOIN feature_tickets ft ON t.id = ft.ticket_id
    -> ORDER BY id;
+----+-------+-------------+-----------+------------+-----------+-----------------+
| id | title | description | ticket_id | bug_report | ticket_id | feature_request |
+----+-------+-------------+-----------+------------+-----------+-----------------+
|  1 | title | description |         1 | bug report |      NULL | NULL            |
|  2 | title | description |      NULL | NULL       |         2 | feture reuqeust |
+----+-------+-------------+-----------+------------+-----------+-----------------+

参考

• 単一テーブル継承
• 具象テーブル継承
• クラステーブル継承
• Single Table Inheritance - GitLab
• Table inheritance - Prisma
• Tackling Polymorphism in Prisma - ZenStack
• SQLアンチパターン - O’Reilly
• Inheritance and polymorphism: where the cracks in SQL begin to show - TypeDB

Tags:

• Database
• MySQL
• Golang

【Next.js】Auth.jsとKeycloakで実装する認証基盤

Pydantic Settings で CLI アプリケーションを作成する