Accessのテーブル設計｜正規化の基礎と実務での落としどころ

結論：正規化すればデータの重複が減り、修正ミスが起きにくくなる

Accessのテーブル設計で正規化が不十分だと、同じ情報を複数の場所に保持することになり、1か所を直したときに別の場所を直し忘れて矛盾が生まれます。第1〜第3正規形を順に適用することで、テーブルの保守性は大きく上がります。ただし正規化は「やりすぎ」も問題で、小規模な業務データベースでは第3正規形まで完全に適用しなくていい場面も多々あります。この記事では正規化の各ステップを具体的な表と手順で示し、Accessで使うリレーションシップと参照整合性の設定まで一通りまとめます。

Excelの1枚表をそのままAccessに持ち込むと何が起きるか

Excelからの移行でよく見るのが、1枚のシートをほぼそのままAccessのテーブルにしたケースです。たとえば受注管理なら、次のような構造になりがちです。

この設計には3つの問題があります。山田商事の住所が変わったときに「受注ID: 1001」と「1002」の2か所を直さなければならない（冗長性）。商品が3つになった瞬間に列が足りない（繰り返し項目）。商品2が未使用のレコードが増えてNULLだらけになり、検索条件や制約が複雑になる。Excelなら対処できるこれらの問題が、リレーショナルデータベースでは設計の欠陥として残り続けます。

第1〜第3正規形の定義と、Accessでの適用手順

Microsoftの公式ドキュメント（データベース正規化の説明 | Microsoft Learn）は正規化を「データの冗長性と一貫性のない依存関係を排除してデータベースを整理するプロセス」と定義しています。第1〜第3正規形（1NF・2NF・3NF）が実務での基本です。

第1正規形（1NF）：繰り返し項目をなくす

この例で対処が必要な1NFの主な条件は「1つのフィールドには1つの値しか入れない」「繰り返し項目（商品1、商品2…）を列として持たない」の2点です（公式定義はこれに加えて「関連データの各セットを主キーで識別する」も含みます）。先ほどの表を1NFに変換すると、「受注明細」テーブルを別に切り出します。

受注明細テーブルの「受注ID」は、受注テーブルの主キーを参照する外部キーです。商品がいくつあっても行を追加するだけで対応できます。

第2正規形（2NF）：主キーの一部にしか依存しない列を分ける

2NFは典型的には複合候補キーを持つテーブルで問題になり、非キー属性が候補キーの一部だけに依存する「部分関数従属」を排除します。「受注ID＋商品ID」が複合キーのとき、「商品名」の標準単価は「商品ID」だけで決まるにもかかわらず、同じテーブルに置かれていると商品が受注されるたびに繰り返しが発生します。

対処は商品名と標準単価を商品マスタテーブルへ切り出すことです。受注明細テーブルには「商品ID（外部キー）」「数量」「受注時単価」を残します（受注時単価は値引きや価格改定によって変わりうるため、当社では明細行にスナップショットとして保持する設計を採用しています）。実務向けに単純化すると、複合キーの一部だけで決まる属性を別テーブルへ分離する、というのが2NFの本質です（Microsoft公式ドキュメントの定義も同じ原則に基づいています）。

第3正規形（3NF）：非キー属性がキーを介さずに別の属性に依存していたら分ける

1NF後の受注テーブルに「顧客ID」を追加すると、顧客住所は「受注ID → 顧客ID → 顧客住所」という依存の連鎖で受注IDに推移的に関数従属します。これが「推移的関数従属」です。顧客住所を受注テーブルに置き続けると、顧客が引っ越したときにその顧客の全受注行の住所を直さなければならなくなります。なお配送先住所を受注時点で固定したい場合は、受注ヘッダーにスナップショット列を持つか、受注配送先テーブルを別に設ける設計が考えられます（当社でもこうした方針でテーブル設計することがあります）。

解決策は顧客テーブルを独立させることです。

住所変更は顧客テーブルの1行を直すだけで済みます。Microsoftの公式ドキュメントでも正規化による住所情報の一元化が例示されています。なお3NFより高度な正規形（Boyce–Codd正規形、第4・第5正規形など）も理論上は存在します。Microsoftの公式ドキュメントは第4・第5正規形について「実用的な設計ではほとんど考慮されない」と明記しており、これらを含む高度な正規化が実務で必要になる場面は多くありません。

AccessのリレーションシップウィンドウとAccessの参照整合性

テーブルを分割したら、Accessの「リレーションシップ」機能でテーブル間の関係を定義します。「データベースツール」タブ→「リレーションシップ」を開き、主キーのフィールドを外部キーのフィールドへドラッグするだけで1対多のリレーションシップが作れます（テーブル間のリレーションシップの定義 | Microsoft Learn）。

ここで「参照整合性を設定する」にチェックを入れると、次の3つのルールが自動で適用されます。

• 存在しない顧客IDを受注テーブルに入力できなくなる（孤立レコードの防止）
• 受注が残っている顧客を顧客テーブルから削除できなくなる
• 受注が残っている顧客IDは変更できなくなる（整合性の保護）

3点目の「主キーの変更制限」は参照整合性の基本ルールです。顧客IDを変えたい場合は、別途「フィールドの連鎖更新（Cascade Update Related Fields）」にチェックを入れた場合のみ、外部キーが自動更新されます。同様に「カスケード削除」を有効にすると、顧客テーブルから1件削除したとき、関連する受注レコードも一括削除されます。業務によっては意図しないデータ消去につながるため、カスケード削除は慎重に判断してください。参照整合性を設定しない場合、Accessはリレーションシップを参考表示として描きますが、データの整合性チェックは行いません。

なお、AccessはリレーションシップウィンドウでJOINの種類（内部結合・左外部結合・右外部結合）も設定できます。クエリのデフォルトは内部結合で、一致するレコードだけを返します。

正規化しすぎると何が起きるか

Microsoftの公式ドキュメント自身が「第3正規形への完全準拠は必ずしも実用的ではない」と明記しています。都道府県や郵便番号まで別テーブルに切り出すと、JOIN（結合）の数が増えてクエリが複雑になり、AccessのACE（JET）エンジンでは遅さの原因になることがあります。

当社が移行支援の現場で判断材料の一つにしているのは「頻繁に変わるデータかどうか」です。更新頻度だけでなく、関数従属や整合性制約の要件も踏まえる必要がありますが、実務的には「よく変わる属性は独立したマスタに切り出す」が分かりやすい指針です。顧客の現在住所を一元管理したい場合は顧客マスタへ、商品の標準単価を商品マスタで管理したい場合は商品マスタへ切り出す価値があります。クエリの遅さが気になる場合はAccessクエリ高速化の技術も参照してください。

Accessデータベースファイル（.accdb/.mdb）にはシステムオブジェクト分を除く最大2GBという上限があります。この上限など根本的な限界についてはAccessのJET/ACEエンジンとはで解説しています。2GBへの到達速度はテーブル数よりも実データ・索引・一時領域の総容量に左右されますが、正規化によって重複が減ればファイルサイズ自体も小さく保ちやすくなります。

当社の支援事例では、移行を検討するきっかけとして「複数人で使うと遅い・壊れる」が挙がることが多く、その一因はテーブル設計よりもACEエンジンのファイル共有型アーキテクチャにあることが多いと感じています。複数人での同時利用の問題と、テーブルだけSQL Serverへ移すアップサイジングも合わせて読むと全体像がつかみやすくなります。

お使いのAccessが移行できる状態かどうかを確かめたい場合は、無料の解析可否チェックから現状をお送りください。構造を拝見して、どこから手をつけるかをご案内します。

よくある質問

Q. 主キーはオートナンバー型にしなければなりませんか。

必須ではありません。Access上の制約としては「一意（重複なし）」かつ「NULL不可」です。設計上は、必要最小限の属性で構成される候補キーの中から主キーを選ぶのが原則です。業務コード（社員番号、製品コードなど）が一意に決まるならそれを使えます。オートナンバー型は一意な代理キーを自動採番できますが、欠番のない連番を保証するものではなく、レコード削除や挿入の失敗で番号が飛ぶことがあります。

Q. 参照整合性を後から設定しようとしたら「設定できない」と言われました。

既存データに不整合（外部キー側に、主キー側に存在しない値）がある場合、Accessは参照整合性の設定を拒否します。まず既存データの不整合を修正してから設定してください。クエリで「受注テーブルに存在するが顧客テーブルには対応する顧客IDがないレコード」を抽出して確認するのが早道です。

Q. 正規化の前に「テーブルは何個作ればよいか」がわかりません。

まず業務で扱う「もの」を列挙します（顧客・受注・商品・担当者など）。それぞれが独立して存在できるなら別テーブルにします。受注明細のように「受注がなければ存在しない」ものは、受注テーブルに外部キーで紐づける子テーブルになります。最初から完璧な設計でなくても、後からテーブルを追加して外部キーで結べるのがリレーショナルデータベースの強みです。

Q. 正規化したら帳票の出力が面倒になりました。

正規化によってテーブルが分かれると、帳票用のクエリでJOINが必要になります。これはAccess標準のクエリデザインで設定できます。JOINを重ねるほどクエリが遅くなる場合、当社ではテーブル作成クエリや追加クエリで作業用テーブルを一時的に生成して中間結果を物理的に保持する設計を採用することがあります（Accessの保存クエリはクエリ定義を保存するものであり、結果行は保持しません。永続化にはテーブル作成クエリ、追加クエリ、VBAでの書き込みなどが必要です）。正規化とパフォーマンスのバランスは設計上のトレードオフで、一律に「分ければ分けるほどよい」とはなりません。