配列と正規化テーブル
配列が適切な選択となる場合を学びます。
「配列と正規化テーブル」はCoddyKit上の無料SQL Academyレッスンです。 これはレッスン4/4です。 下記で完全なレッスンを無料で読むことができます。その後、ブラウザ内の組み込みコードエディタと24時間対応のAIチューターでハンズオン演習できます。 これはSQL Academy学習パスの一部であり、ウェブとCoddyKitアプリ全体で進捗が同期されます。 SQL Academyコースには全4レッスンが含まれています。
複数の値を格納する2つの方法
1行に複数の関連する値を格納する必要がある場合、PostgreSQLには主に2つの方法があります。同じ行の配列カラムに格納する方法と、各値に専用の行を割り当てる子テーブルを作成する方法です。
どちらの方法をいつ使うかを理解することは、効率的で保守しやすいデータベースを設計するための重要なスキルです。
正規化されたアプローチ
完全に正規化されたスキーマでは、データの各要素をそれぞれ独自の行に格納します。ユーザーが複数の電話番号を持てる場合は、user_phonesテーブルを作成し、usersへの外部キーを設定します。
これは古典的なリレーショナルモデルであり、ほとんどの状況でデフォルトの選択肢となります。
CREATE TABLE users (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL
);
CREATE TABLE user_phones (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
phone TEXT NOT NULL
);
INSERT INTO users (name) VALUES ('Alice'), ('Bob');
INSERT INTO user_phones (user_id, phone) VALUES
(1, '+1-555-0101'),
(1, '+1-555-0102'),
(2, '+1-555-0200');配列を使うアプローチ
PostgreSQLのTEXT[]（または、任意の型に[]を付けた型）を使うと、複数の値を1つのカラム内に直接格納できます。追加のテーブルは必要ありません。
同じ電話番号データを、ユーザーごとに1つのコンパクトな行へ格納できます。
CREATE TABLE users_with_phones (
id SERIAL PRIMARY KEY,
name TEXT NOT NULL,
phones TEXT[]
);
INSERT INTO users_with_phones (name, phones) VALUES
('Alice', ARRAY['+1-555-0101', '+1-555-0102']),
('Bob', ARRAY['+1-555-0200']);配列の検索は簡単
配列カラム内の検索には、ANY演算子または@>（包含）演算子を使うと簡単です。単純なWHERE句で、特定の電話番号を持つすべてのユーザーを検索できます。
-- Find users who have a specific phone number
SELECT name
FROM users_with_phones
WHERE '+1-555-0101' = ANY(phones);
-- Or using the array-contains operator
SELECT name
FROM users_with_phones
WHERE phones @> ARRAY['+1-555-0101'];配列が適している場合：単純な検索
次のような場合は、配列が適しています。
- 値のリストを1つの単位としてまとめて読み取る（タグ、ラベル、カテゴリなど）
- 個々の要素を使って結合する必要がない
- リストに自然な上限があり、部分的に更新することがほとんどない
典型的な例は、ブログ記事にタグを格納することです。通常はすべてのタグを一度に取得し、単一のタグを使った複雑な結合で記事を検索することはほとんどありません。
CREATE TABLE posts (
id SERIAL PRIMARY KEY,
title TEXT NOT NULL,
tags TEXT[]
);
INSERT INTO posts (title, tags) VALUES
('Intro to SQL', ARRAY['sql', 'beginner', 'database']),
('Advanced Indexes', ARRAY['sql', 'performance', 'indexes']),
('NoSQL Overview', ARRAY['nosql', 'beginner']);
-- Get all posts tagged 'beginner'
SELECT title FROM posts
WHERE 'beginner' = ANY(tags);正規化されたテーブルが適している場合：リレーションシップ
次のような場合は、正規化されたテーブルのほうが適しています。
- 個々の値に独自の属性が必要である（例：電話番号に自宅用・仕事用などの種類がある）
- 個々の値を使って結合する必要がある
- 値が独立して、かつ頻繁に変化する
- 外部キーによる参照整合性が必要である
-- Phone numbers need a 'type' attribute — array can't do this cleanly
CREATE TABLE user_phones (
id SERIAL PRIMARY KEY,
user_id INT REFERENCES users(id),
phone TEXT NOT NULL,
type TEXT CHECK (type IN ('home', 'work', 'mobile'))
);
INSERT INTO user_phones (user_id, phone, type) VALUES
(1, '+1-555-0101', 'home'),
(1, '+1-555-0102', 'work');インデックスの違い
正規化されたテーブルでは、外部キーまたは値のカラムに標準的なB-treeインデックスを追加できます。配列では、配列内を高速に検索するためにGINインデックス（Generalized Inverted Index）が必要です。
GINインデックスは十分に機能しますが、B-treeインデックスよりサイズが大きく、更新も遅くなります。
-- Index for fast array element lookups
CREATE INDEX idx_posts_tags ON posts USING GIN (tags);
-- Now this query uses the index efficiently
EXPLAIN SELECT title FROM posts
WHERE tags @> ARRAY['sql'];行をまたぐ集約：正規化が有利
個々の値についてカウント、グループ化、集約を行う必要がある場合は、正規化されたテーブルのほうがはるかに自然です。配列内で集約するにはunnest()が必要で、まず配列を行に展開します。これは実質的に、クエリ実行時に正規化された構造を再現する処理です。
-- Count posts per tag (array approach — needs unnest)
SELECT tag, COUNT(*) AS post_count
FROM posts, unnest(tags) AS tag
GROUP BY tag
ORDER BY post_count DESC;
-- With a normalized post_tags table this would be simpler:
-- SELECT tag, COUNT(*) FROM post_tags GROUP BY tag;配列要素の変更
配列内の単一の要素を更新または削除するには、扱いにくい構文が必要です。配列全体を置き換えるか、array_remove()を使用しなければなりません。正規化されたテーブルなら、対象の行に対してDELETEまたはUPDATEを実行するだけです。
-- Remove a single tag from an array column
UPDATE posts
SET tags = array_remove(tags, 'beginner')
WHERE id = 1;
-- Append a new tag
UPDATE posts
SET tags = array_append(tags, 'tutorial')
WHERE id = 1;
SELECT title, tags FROM posts WHERE id = 1;有効な値の保証
正規化されたテーブルでは、外部キーを使って、すべての値が既知の集合に含まれることを保証できます。配列から別のテーブルを参照することはできず、外部キーもサポートされません。
各要素について参照整合性を確実に保証する必要がある場合、子テーブルを使うしかありません。
-- Normalized: only valid category IDs allowed (FK enforced)
CREATE TABLE categories (
id SERIAL PRIMARY KEY,
name TEXT UNIQUE NOT NULL
);
CREATE TABLE post_categories (
post_id INT REFERENCES posts(id),
category_id INT REFERENCES categories(id),
PRIMARY KEY (post_id, category_id)
);
-- Array: no constraint possible — any text value is accepted
-- UPDATE posts SET tags = ARRAY['totally_invalid_tag'] WHERE id = 1;実践的な選択ガイド
データが単純なフラットなリストであり、常に1つの単位として読み取られ、要素ごとの追加属性がなく、参照整合性も必要ない場合（タグ、ラベル、検索キーワードなど）は、配列を使用してください。
各要素に独自の属性がある場合、個々の値を使って結合や集約を行う場合、外部キーが必要な場合、または個々の要素を頻繁に更新・削除する場合は、正規化された子テーブルを使用してください。
-- Summary example: tags as array (good fit)
SELECT title, tags
FROM posts
WHERE tags @> ARRAY['sql']
ORDER BY title;
-- Unnest when you need row-level processing
SELECT title, unnest(tags) AS tag
FROM posts
ORDER BY title, tag;簡単な確認
正規化された子テーブルではなく、PostgreSQLの配列としてデータを格納するのが最も適しているのは、どのような状況でしょうか。
レッスンのまとめ
このレッスンでは、PostgreSQLにおける配列と正規化されたテーブルの主なトレードオフについて学びました。
- 配列は、タグのようにフラットで単位として読み取るリストをコンパクトかつ便利に格納できます。ただし、外部キーを使えず、要素ごとの更新が扱いにくく、高速な検索にはGINインデックスが必要です。
- 正規化されたテーブルは、要素ごとの属性、外部キーによる整合性、効率的な集約、単純な行単位の更新をサポートします。その代わり、追加の結合が必要になります。
- 適切な選択は、データをどのように格納するかだけでなく、どのように検索、更新、関連付けを行うかによって決まります。
AI チューターと学ぶ SQL — 無料
ブラウザでリアルコードを書いて実行し、24/7 の AI チューターから瞬時にサポートを受け、ウェブまたはアプリで続きから学習できます。
- コース
- 46
- レッスン
- 183
よくある質問
「配列と正規化テーブル」レッスンは無料ですか？
はい。「配列と正規化テーブル」の完全なテキストはこのウェブで無料で読めます。インタラクティブに演習し（組み込みコードエディタと24時間対応のAIチューター）、SQL Academyコースの残りをアンロックするには、CoddyKit PROにアップグレードしてください。 SQL Academyコースには全4レッスンが含まれています。
「配列と正規化テーブル」で何を学びますか？
配列が適切な選択となる場合を学びます。 ブラウザで直接実行するハンズオンコードでSQL Academyを演習し、24時間対応のAIチューターがレッスンを進める中での質問に答えます。
SQL Academyを始めるのに経験は必要ですか？
事前経験は必要ありません。CoddyKitのSQL Academyは初級者から上級者向けに構成されているため、ここから始めるか最初から始めて、自分のペースで進むことができます。 これはレッスン4/4です。
「配列と正規化テーブル」レッスンにはどのくらい時間がかかりますか？
ほとんどのCoddyKitレッスンは約5～10分かかります。各レッスンはコンパクトでインタラクティブなので、着実に進歩し、ウェブとアプリ全体で正確に前回の場所から再開できます。
このSQL Academyレッスンでコードを書いて実行できますか？
はい。すべてのSQL Academyレッスンに組み込みコードエディタが含まれているため、ブラウザでリアルコードを書いて実行し、即座のAIフィードバックを取得できます。ローカル設定は不要です。