🔖
SQL データにメタデータを増殖させない

2025/02/28に公開
 はじめに最近、オライリー・ジャパンの「SQLアンチパターン」という本を読んでいます。前回の記事に引き続き、今回は「メタデータトリブル（メタデータ大増殖）」というアンチパターンについて、私なりの実例を交えながら解説したいと思います。

 メタデータトリブル（メタデータ大増殖）とは？メタデータトリブルとは、データベース設計において、データを時系列や種類ごとに分割しようとするあまり、似たような構造のテーブルが無秩序に増殖してしまうアンチパターンを指します。
特に、年度や月ごとにテーブルを分割する「時系列分割」は、一見整理されているように見えながら、実際には多くの問題を引き起こす典型的なアンチパターンです。

 参考著書オライリー・ジャパン「SQLアンチパターン」

https://www.oreilly.co.jp/books/9784873115894/

 問題のある実装例以下のような売上管理システムを例に考えてみます。
-- 2022年の売上テーブル
CREATE TABLE sales_2022 (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    customer_id INT,
    product_id INT,
    amount DECIMAL(10,2)
);

-- 2023年の売上テーブル
CREATE TABLE sales_2023 (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    customer_id INT,
    product_id INT,
    amount DECIMAL(10,2)
);

-- 2024年の売上テーブル
CREATE TABLE sales_2024 (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    customer_id INT,
    product_id INT,
    amount DECIMAL(10,2)
);
一見、以下のようなメリットがあるように思えます。
古いデータと新しいデータを物理的に分離できる
各年のテーブルサイズが小さくなる
特定年のデータのみを操作する際にパフォーマンスが向上する

 デメリットしかし、この設計には以下のような重大な問題があります。
クエリの複雑化
複数年のデータを集計する際に、UNIONを多用する必要がある
以下のような複雑なクエリが必要になる

SELECT 
    customer_id,
    SUM(amount) as total_amount
FROM (
    SELECT customer_id, amount FROM sales_2022
    UNION ALL
    SELECT customer_id, amount FROM sales_2023
    UNION ALL
    SELECT customer_id, amount FROM sales_2024
) combined_sales
GROUP BY customer_id;
メンテナンス性の低下
新年度が始まるたびに新しいテーブルを作成する必要がある
スキーマ変更時に全テーブルを修正する必要がある
バックアップやリストアの手順が複雑化
アプリケーション開発の複雑化
テーブル名を動的に組み立てる必要がある
年をまたぐデータの処理が複雑化
トランザクション管理が困難になる

 解決策このアンチパターンを避けるためには、以下のアプローチを検討します。

 1. 単一テーブルでの管理CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    sale_date DATE,
    customer_id INT,
    product_id INT,
    amount DECIMAL(10,2)
);

 2. パーティショニングの活用物理的なデータ分割が必要な場合は、データベースの機能としてのパーティショニングを使用
論理的には1つのテーブルとして扱いながら、物理的な分割のメリットを得られる

 3. アーカイブ戦略の検討古いデータを別テーブルに移動する場合は、明確なアーカイブポリシーを策定
必要に応じてビューを作成し、アプリケーションからの透過的なアクセスを確保

 まとめ年度ごとのテーブル分割は、「やってはいけないよね」と思いつつも、なぜやってはいけないのか、言語化することができました。
年度ごとのテーブル分割が引き起こす問題点は以下の通りです。
クエリが複雑になり、保守性が低下する
スキーマ変更時に全テーブルの修正が必要
年をまたぐデータ処理が困難
代わりに、以下のポイントに注意して設計することで、より保守性の高いデータベースを実現できることを学びました。
単一テーブルでの管理を基本とする
必要に応じてパーティショニングを活用する
明確なアーカイブポリシーを策定する
これにより、シンプルで保守性が高く、かつパフォーマンスも考慮されたデータベース設計が可能になりますね！
はじめに

メタデータトリブル（メタデータ大増殖）とは？

参考著書

問題のある実装例

デメリット

解決策

1. 単一テーブルでの管理

2. パーティショニングの活用

3. アーカイブ戦略の検討

まとめ

Discussion
