2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@takkenai(宅建AI)

個人開発アプリのデータベース設計を3回やり直した話：最終的にたどり着いたSupabaseのスキーマ設計

2
Posted at

個人開発アプリのデータベース設計を3回やり直した話：最終的にたどり着いたSupabaseのスキーマ設計

はじめに

個人開発で一番つらいのは、コードを書くことではなく、過去の自分が書いたコードをぶっ壊して作り直すことです。

私は宅建試験のAI対策アプリ（takkenai.jp）を個人開発しています。1250問以上の問題データを扱うアプリで、バックエンドにはSupabase（PostgreSQL）、フロントにはNext.jsを採用しました。

このアプリのデータベース設計を、私は3回やり直しました。

1回目は「正規化しすぎ」で破綻。
2回目は「JSON列を使いすぎ」て後悔。
3回目は「RLS（Row Level Security）の設計ミス」でデータが丸見えに。

この記事では、それぞれの失敗パターンを正直に晒しつつ、最終的にたどり着いたスキーマ設計を共有します。同じように個人開発でSupabaseを使っている方の参考になれば幸いです。

失敗1：正規化しすぎて、JOINの嵐になった

やったこと

データベース設計の教科書を読んだ直後の私は、とにかく正規化したくてたまりませんでした。

宅建の問題データを格納するにあたって、こんなテーブル構造を設計しました。

-- 当時の設計（簡略化）
CREATE TABLE exams (id UUID PRIMARY KEY, year INT, name TEXT);
CREATE TABLE sections (id UUID PRIMARY KEY, exam_id UUID REFERENCES exams(id), name TEXT);
CREATE TABLE questions (id UUID PRIMARY KEY, section_id UUID REFERENCES sections(id), body TEXT);
CREATE TABLE choices (id UUID PRIMARY KEY, question_id UUID REFERENCES questions(id), label TEXT, body TEXT);
CREATE TABLE answers (id UUID PRIMARY KEY, question_id UUID REFERENCES questions(id), correct_choice_id UUID REFERENCES choices(id));
CREATE TABLE explanations (id UUID PRIMARY KEY, question_id UUID REFERENCES questions(id), body TEXT);

試験 → セクション → 問題 → 選択肢、正答、解説…すべて別テーブルです。

何が起きたか

1問を表示するために5〜6テーブルをJOINする必要がありました。

SELECT q.body, c.label, c.body, a.correct_choice_id, e.body
FROM questions q
JOIN choices c ON c.question_id = q.id
JOIN answers a ON a.question_id = q.id
JOIN explanations e ON e.question_id = q.id
JOIN sections s ON q.section_id = s.id
WHERE s.exam_id = '...'

Supabaseのクライアントライブラリで書くと、ネストしたselectが地獄のようになりました。個人開発で自分しかメンテしないのに、クエリの可読性が最悪。

1250問を一覧取得するときのレスポンスも遅く、ページネーションとの組み合わせでバグが頻発。

学び

正規化は「チームで長期運用するシステム」のための技術。個人開発で、更新頻度が低い問題データに対して第3正規形まで追い込む意味はほぼなかった。

失敗2：振り子が振れすぎて、JSON列を乱用した

やったこと

正規化に懲りた私は、真逆の方向に走りました。

「選択肢も正答も解説も、全部1つのカラムに入れればJOINいらないじゃん」

CREATE TABLE questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  year INT,
  section TEXT,
  body TEXT,
  data JSONB NOT NULL  -- ここに全部入れた
);

dataカラムの中身はこんな感じです。

{
  "choices": [
    { "label": "1", "body": "AはBに対して..." },
    { "label": "2", "body": "CはDに対して..." },
    { "label": "3", "body": "EはFに対して..." },
    { "label": "4", "body": "GはHに対して..." }
  ],
  "correct": "3",
  "explanation": "民法第○条により...",
  "tags": ["民法", "抵当権"]
}

最初は快適でした。JOINなし、クエリはシンプル、データ投入も楽。

何が起きたか

問題が出たのは、ユーザーの回答履歴を分析するときでした。

「この選択肢を選んだ人が何%いるか」を集計しようとすると、JSONBの中をPostgreSQLの関数で掘る必要があります。

SELECT
  q.id,
  ua.selected_choice,
  COUNT(*)
FROM user_answers ua
JOIN questions q ON q.id = ua.question_id
GROUP BY q.id, ua.selected_choice;

ここまではいいのですが、「正解の選択肢のbodyテキスト」を一緒に引きたいとなると、こうなります。

SELECT
  q.id,
  q.data->'choices' AS choices,
  (SELECT elem->>'body'
   FROM jsonb_array_elements(q.data->'choices') AS elem
   WHERE elem->>'label' = q.data->>'correct'
  ) AS correct_text
FROM questions q;

つらい。

さらに、TypeScript側の型安全性が完全に崩壊しました。Supabaseの型生成ツール（supabase gen types）を使っても、JSONB列はJson型になるだけ。フロント側で毎回asでキャストする羽目に。

学び

JSON列は「検索・集計しないデータ」にだけ使う。構造化してクエリしたいデータをJSONBに入れると、SQLの地獄とTypeScriptの型崩壊のダブルパンチが来る。

失敗3：RLS（Row Level Security）の設計ミスで、他人の回答が見えた

やったこと

2回目の失敗を踏まえ、テーブル設計自体はバランスの良い形に落ち着きました。しかし、RLSの設計で致命的なミスをしました。

問題テーブルは全ユーザーに公開。回答履歴テーブルは自分のデータだけ見える。この方針自体は正しかったのですが、実装が甘かった。

-- 当時のRLS
ALTER TABLE user_answers ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own answers"
  ON user_answers FOR SELECT
  USING (auth.uid() = user_id);

-- INSERT ポリシーを書き忘れた

何が起きたか

SELECTのポリシーだけ書いてINSERTポリシーを設定し忘れたため、ログインユーザーが回答を保存できないバグが発生。

「あれ、保存できない…」→ 焦ってRLSを一時的に無効化 → そのままデプロイ → 全ユーザーの回答履歴が誰からでもSELECTできる状態に。

幸い、ユーザー数が少ない段階で気づきましたが、冷や汗ものでした。

学び

RLSは「全操作（SELECT / INSERT / UPDATE / DELETE）に対して明示的にポリシーを書く」が鉄則。面倒でも1つずつ設定する。そして絶対に「一時的にRLS無効化」をプロダクション環境でやらない。

最終的にたどり着いたスキーマ設計

3回の失敗を経て、現在のスキーマ設計はこうなっています。

-- ===== 問題テーブル（適度に非正規化） =====
CREATE TABLE questions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  year INT NOT NULL,
  question_number INT NOT NULL,
  section TEXT NOT NULL,           -- '権利関係', '法令上の制限' など
  body TEXT NOT NULL,
  choice_1 TEXT NOT NULL,
  choice_2 TEXT NOT NULL,
  choice_3 TEXT NOT NULL,
  choice_4 TEXT NOT NULL,
  correct_choice INT NOT NULL CHECK (correct_choice BETWEEN 1 AND 4),
  explanation TEXT NOT NULL,
  tags TEXT[] DEFAULT '{}',        -- PostgreSQL配列で十分
  created_at TIMESTAMPTZ DEFAULT now(),

  UNIQUE(year, question_number)
);

-- ===== ユーザー回答履歴 =====
CREATE TABLE user_answers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID NOT NULL REFERENCES auth.users(id) ON DELETE CASCADE,
  question_id UUID NOT NULL REFERENCES questions(id) ON DELETE CASCADE,
  selected_choice INT NOT NULL CHECK (selected_choice BETWEEN 1 AND 4),
  is_correct BOOLEAN NOT NULL,
  answered_at TIMESTAMPTZ DEFAULT now()
);

CREATE INDEX idx_user_answers_user ON user_answers(user_id);
CREATE INDEX idx_user_answers_question ON user_answers(question_id);

-- ===== RLS：問題テーブル（全員読み取り可） =====
ALTER TABLE questions ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Anyone can read questions"
  ON questions FOR SELECT
  USING (true);

-- ===== RLS：回答履歴（全操作を明示的に定義） =====
ALTER TABLE user_answers ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can read own answers"
  ON user_answers FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can insert own answers"
  ON user_answers FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own answers"
  ON user_answers FOR UPDATE
  USING (auth.uid() = user_id)
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can delete own answers"
  ON user_answers FOR DELETE
  USING (auth.uid() = user_id);

この設計のポイント

判断 理由
選択肢を choice_1 〜 choice_4 の固定カラムに 宅建は常に4択。JOINもJSONも不要になる
is_correct を回答時に計算して保存 集計クエリが WHERE is_correct = true だけで済む
tags はPostgreSQLのネイティブ配列 JSONBより軽量。ANY(tags) で検索も可能
RLSは4操作すべてに明示ポリシー 書き忘れ防止。テンプレとしてコピペできるようにした

Next.js側からのクエリもシンプルになりました。

// 1問取得（JOINなし、型安全）
const { data: question } = await supabase
  .from('questions')
  .select('*')
  .eq('year', 2024)
  .eq('question_number', 1)
  .single();

// ユーザーの正答率（RLSが効くので自分のデータだけ返る）
const { data: stats } = await supabase
  .from('user_answers')
  .select('is_correct')
  .eq('question_id', questionId);

const correctRate = stats?.filter(s => s.is_correct).length / (stats?.length || 1);

まとめ：個人開発のDB設計で大事だったこと

3回の失敗から得た教訓を整理します。

  1. 正規化は目的ではなく手段。 ドメインが固定的（宅建は4択、年度＋問題番号で一意）なら、カラムを素直に並べた方がシンプル。
  2. JSON列は「検索しないデータ」専用。 クエリや集計の対象になるデータは必ずカラムに切り出す。
  3. RLSは全操作に対して書く。 SELECTだけ書いて満足しない。そして「一時無効化」をプロダクションで絶対にやらない。
  4. 最初から完璧を目指さない。 やり直す前提で、マイグレーションしやすい構造にしておく。Supabaseのマイグレーション機能（supabase db diff）に何度も助けられた。

個人開発だからこそ、設計ミスに気づくのも直すのも自分だけです。だからこそ「恥ずかしいけどやり直す」判断を早くできることが、プロジェクトを完走させる鍵だと感じています。

この設計で運用しているのが、宅建試験のAI対策アプリ takkenai.jp です。1250問以上の過去問をAI解説付きで学習できます。もしこれから宅建を受ける方がいれば、この3回壊して作り直したDBの上で動いているアプリを、ぜひ試してみてください。

2
1
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
2
1

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?