概要
| この記事の結論 テーブルを作る前に、1行が何を表すかと主キーを決めます。そのうえで、繰り返し項目を行へ分け、キーの一部だけに依存する項目、キー以外の項目に依存する項目を順に別表へ移します。最後に参照整合性と移行前後の件数・金額を確認します。 |
この記事でわかること
- 1行の意味を固定
- 正規形は依存関係で判断
- 履歴の意味を失わない
- キーと参照整合性を確認
この記事の主な内容
- 正規化の前に1行の意味とキーを決める
- 第1正規形で繰り返し項目をなくす
- 第2正規形でキーの一部への依存を分ける
- 第3正規形でキー以外への依存を分ける
- 取引時点の単価と履歴を残す
- Accessでテーブルとリレーションを作る
- 既存Excelから移行する確認とまとめ
- よくある質問
想定読了時間：約10分～15分
正規化の前に1行の意味とキーを決める
正規化とは、項目同士の関係に沿ってデータを整理し、同じ事実の食い違いを減らす設計手順です。単に表を細かく分割することではありません。まず「この表の1行は何を表すか」と「その行をどう特定するか」を決めてから、分割する項目を判断します。
例えば受注管理では、受注日や顧客は注文全体の情報です。一方、商品、数量、販売単価は注文の各明細に属します。この違いを曖昧にしたまま一枚の表へ詰め込むと、顧客名の直し忘れや、商品数が増えるたびに列を追加する問題が起きます。
最初に集めるもの
既存のExcelだけでなく、注文書、請求書、検索画面、月次集計の見本を集めます。保存したい項目を列挙したうえで、いつ値が決まるか、後から変更してよいか、誰が入力するかを確認します。正規化だけでは、業務に必要な項目の漏れまでは発見できません。
| 設計前の質問 | 受注管理での回答例 | 決める理由 |
|---|---|---|
| 1行は何を表すか | 受注明細なら注文内の1行 | 商品数と受注件数を混同しない |
| 行を特定する値は何か | 受注IDと行番号の組合せ | 同じ商品を複数行扱える |
| 何から値が決まるか | 受注IDから受注日が決まる | 分割の判断基準になる |
| 過去の状態を残すか | 販売単価は取引時点で固定 | 現在価格で過去売上を変えない |
主キーは、テーブルの行を重複なく識別する項目です。複数項目を組み合わせたものを複合キーと呼びます。「この値が決まれば、別の値も一つに決まる」という関係を関数従属といい、正規化ではこの関係を確認します。参考1
第1正規形で繰り返し項目をなくす
第1正規形では、一つの項目に複数の値を詰め込まず、商品1・商品2のような繰り返し列を整理します。Excelのセル結合を解除するだけでは十分ではありません。業務上、一つずつ検索・集計したい対象を独立した値として扱える形にします。
商品1と商品2を行へ展開する
説明用に、受注1001に商品Aが2個、商品Bが1個あるとします。商品1、数量1、商品2、数量2という列構成では、商品が三つになるたびに列と処理を増やす必要があります。明細を行へ並べれば、商品が増えてもレコードを追加するだけです。
| 受注ID | 行番号 | 商品コード | 数量 |
|---|---|---|---|
| 1001 | 1 | A | 2 |
| 1001 | 2 | B | 1 |
| 1002 | 1 | A | 5 |
ここでの1行は「受注内の明細1行」です。受注1001が二つの行に現れるのは正常で、重複だから削除する対象ではありません。行を識別するには、受注IDだけでなく行番号も必要です。表の見た目の繰り返しと、不要な二重登録を区別してください。
分ける単位は利用目的で判断する
電話番号を一つの文字列として持つことは、通常の連絡先管理では自然です。一方、複数の電話番号を一つのセルへカンマ区切りで入れ、番号ごとに検索したいなら別行や子テーブルを検討します。何でも文字単位に分解するわけではありません。
Accessには複数値フィールドなどの機能もありますが、今回のように他システムとの連携やSQLでの扱いやすさを重視する基本設計では、明示的な関連テーブルに分ける方がデータの関係を確認しやすくなります。
第2正規形でキーの一部への依存を分ける
第2正規形は、第1正規形を満たしたうえで、キー以外の項目が複合キーの一部だけに依存する状態をなくしたものです。「同じ値が何度も出たから分ける」という説明だけでは、どこへ分けるべきかを判断できません。
受注IDだけで決まる情報を移す
明細を受注IDと行番号で識別している場合、受注日と顧客IDは行番号がなくても受注IDだけで決まります。このような項目を明細へ繰り返し持つと、一つの注文の受注日を変更するために、全明細の値をそろえる必要が出てきます。
| 分ける先 | 1行の意味 | 主な項目 |
|---|---|---|
| T_受注 | 注文1件 | 受注ID、受注日、顧客ID |
| T_受注明細 | 注文内の明細1行 | 受注ID、行番号、商品ID、数量、販売単価 |
例えば受注1001の受注日を変えるときは、T_受注の1行を変更します。T_受注明細には受注IDを持たせ、必要なときにクエリで受注日を取り出します。明細が10行あっても、同じ受注日の情報を10回書き換える必要はありません。
オートナンバーだけで設計を終えない
明細IDというオートナンバーを追加しても、受注IDと行番号の組合せが業務上重複してよいことにはなりません。主キーとして明細IDを使う場合も、受注IDと行番号には複合の一意インデックスを設け、同じ注文内の同じ行番号を二重登録できないようにします。
厳密な正規形の判定は、選んだ主キーだけでなく候補キーも考慮します。候補キーとは、その行を一意に識別できる最小の項目の組合せです。自動採番のIDを追加すれば他の依存関係を調べなくてよい、とは考えないでください。
第3正規形でキー以外への依存を分ける
第3正規形では、第2正規形を満たしたうえで、キー以外の項目を経由して決まる情報を整理します。例えば受注IDから顧客IDが決まり、顧客IDから現在の顧客名や住所が決まるなら、顧客の基本情報を顧客マスターへ分けるのが基本です。
顧客情報と商品情報を独立させる
| テーブル | 主キー | 保管する基本情報 |
|---|---|---|
| M_顧客 | 顧客ID | 顧客名、現在住所など |
| M_商品 | 商品ID | 商品コード、商品名、現在の標準単価など |
| T_受注 | 受注ID | 受注日、顧客ID |
| T_受注明細 | 明細ID | 受注ID、行番号、商品ID、数量、販売単価 |
T_受注明細に現在の商品名を繰り返し保存している場合は、商品IDから商品名を参照する構成へ整理します。同じ商品名が何行あるかではなく、「商品IDが決まれば商品名が決まる」という関係に注目します。名称が同じ別商品を、勝手に一つへ統合してはいけません。
商品カテゴリにカテゴリ名、担当部署、説明などの属性があるなら、カテゴリマスターを独立させる設計が考えられます。ただし、同じ短い文字が繰り返されるという理由だけで、すべてを別表へ分ける必要があるとは限りません。管理する対象と依存関係を確認します。
入力画面まで分割する必要はない
テーブルが四つに分かれても、利用者に四つの表を順番に開かせる必要はありません。入力はフォームでまとめ、顧客や商品は選択式にします。表示や帳票の都合で欲しい一枚の表は、結合クエリで作るのが基本です。
Accessの教科書でテーブル設計とデータ移行を相談する
ここまでの手順を試しても解決しない場合や、既存業務への影響を判断しにくい場合は、対象画面・現在の設定・再現手順を整理してご相談ください。Accessの教科書のお問い合わせ窓口から、状況に合う対応方法を相談できます。
取引時点の単価と履歴を残す
現在の商品価格と、過去の取引で合意した販売単価は、意味の違うデータです。商品マスターだけに単価を置き、過去の売上を常に現在価格から計算すると、価格改定のたびに過去の金額まで変わってしまいます。取引単価は明細側へ確定値として残します。
価格変更で変わってはいけないもの
例えば受注1001の明細1は販売単価500円、数量2個で契約したとします。後日、商品の標準単価を550円へ変えても、この明細の販売単価500円は保持します。同じ「単価」という名前でも、どの時点の何を表すかが違うため、単なる重複ではありません。
| 項目 | 保存先の例 | 更新するタイミング |
|---|---|---|
| 現在の標準単価 | M_商品 | 価格改定時 |
| 取引の販売単価 | T_受注明細 | 受注確定時。変更は業務ルールで管理 |
| 現在住所 | M_顧客 | 顧客情報の更新時 |
| 出荷時の送付先 | T_受注や出荷テーブル | 出荷情報の確定時 |
送付先も同様です。顧客の現在住所を参照するだけでよいのか、過去の納品書を当時の住所で再印刷する必要があるのかを決めます。後者なら、出荷時点の住所を取引情報として保存するなどの方法が必要です。
計算結果を保存するかどうか
数量×販売単価のように再計算できる明細金額は、通常はクエリで求められます。ただし、確定済み請求金額や税額は、端数処理、税率、値引き、締め処理などの条件を伴います。すべての計算結果を無条件に削除すると、確定時の金額を再現できなくなることがあります。
保存する場合は、入力から計算する値なのか、確定後に変えない値なのかを明確にします。数量だけ変更して確定金額が古いまま残る状態を避けるため、再計算のタイミングと確定後の訂正手順も決めておきます。
Accessでテーブルとリレーションを作る
論理的な分割が決まったら、Accessでデータ型・主キー・一意制約・参照整合性を設定します。参照整合性とは、存在しない顧客IDを受注へ登録するなど、関連先のないデータを防ぐ仕組みです。クエリ画面で線を引くだけでは同じ制約にはなりません。参考2
データ型を対応させる
| フィールド例 | データ型の例 | 設定の注意 |
|---|---|---|
| 顧客ID、商品ID、受注ID、明細ID | オートナンバー型 | 各マスター・親表の主キー |
| 明細側の受注IDや商品ID | 数値型の長整数型 | 通常の長整数オートナンバーを参照する外部キー |
| 商品コード、電話番号 | 短いテキスト | 先頭の0や記号を保持 |
| 受注日 | 日付/時刻型 | 文字列として保存しない |
| 販売単価 | 通貨型 | 必要な精度と金額範囲を確認 |
| 数量 | 数値型 | 整数のみか小数も使うかでサイズを決定 |
外部キーとは、関連する別テーブルのキーを保存する項目です。明細側の商品IDまでオートナンバーにすると、選んだ商品のIDを保存する役割と合いません。既存のキーがテキスト型や別の数値型なら、その型とサイズに合わせて設計します。
リレーションを設定する手順
1. 検証用データベースに各テーブルを作り、それぞれの主キーを設定します。
2. T_受注明細で［インデックス］を開き、受注IDと行番号の複合インデックスを作ります。同じインデックス名の組として2行を設定し、一意を「はい」にします。
3. ［データベースツール］→［リレーションシップ］で必要なテーブルを追加します。
4. M_顧客の顧客IDとT_受注の顧客IDなど、親キーと外部キーを結びます。
5. データを確認したうえで参照整合性を設定し、一対多の関係になったか確認します。
6. T_受注からT_受注明細、M_商品からT_受注明細も同様に確認します。
一対多は、顧客1件に複数の受注がある、といった関係です。連鎖削除を有効にすると親の削除が関連レコードへ波及するため、受注や請求の履歴で安易に設定しないでください。削除せず利用停止にする運用が合う場合もあります。
結合して結果を確認する選択クエリ
次のSQLは、T_受注とT_受注明細を受注IDで結び、明細金額を計算する例です。クエリのSQLビューに貼り付ける場合は、実際のテーブル名とフィールド名を合わせます。データを更新する処理ではありません。
SELECT H.[受注ID], H.[受注日], D.[行番号],
D.[商品ID], D.[数量], D.[販売単価],
D.[数量] * D.[販売単価] AS [明細金額]
FROM [T_受注] AS H INNER JOIN [T_受注明細] AS D
ON H.[受注ID] = D.[受注ID];
この例では数量と販売単価を必須入力とする想定です。未確定値をNullで持つ業務では、Nullを勝手に0へ置き換えて金額を確定しないようにします。表示上の空欄と、業務上のゼロは同じ意味ではありません。
既存Excelから移行する確認とまとめ
既存データの移行では、バックアップを残し、いったん取込用の一時テーブルへ読み込んでから整理します。元の行を直接削除して分割すると、誤った名寄せや変換に気付いても比較できなくなります。旧コードと新IDの対応表を残すことが重要です。
名前が同じでも同じ相手とは限らない
同名企業の別拠点、改称前後の名称、余分な空白、全角半角の差などを確認します。名称だけで自動的に同一と判断せず、取引先コードなど信頼できる識別情報を優先します。判断できないデータは例外一覧へ分け、元データを残して解決します。
| 照合するもの | 期待する結果 | ずれたときに調べる点 |
|---|---|---|
| 受注件数 | 受注IDの種類数が一致 | 明細行数と受注件数の混同 |
| 明細件数 | 展開後の正しい明細数と一致 | 重複取込、空の繰り返し列 |
| 数量・金額 | 同じ条件で合計が一致 | 単価の時点、Null、丸め処理 |
| 未対応ID | 業務上必要な参照がすべて成立 | 未登録マスター、型の違い |
説明例では、受注1001に500円×2個と800円×1個、受注1002に450円×5個があるため、受注2件、明細3件、合計4,050円になります。移行後もこの関係が保たれるかを確認します。テーブルを分けて件数が変わったときは、何の件数を数えているかを先にそろえます。
正規化は、1行の意味とキーを決め、第1・第2・第3正規形の順に依存関係を整理する手順です。そのうえで、取引時点の情報を残す要件を満たし、Access側の制約と移行前後の照合を行います。構造が整っていても必要な履歴が消えていれば、業務に合う設計とはいえません。
よくある質問
同じ値が繰り返されるかどうかだけでは判断できません。受注IDが複数の明細へ現れることは、注文と明細を結ぶために必要な繰り返しです。一方、顧客IDから決まる現在の顧客名を受注ごとに持つと、変更時に食い違いが起こりやすくなります。どのキーから値が決まるかを確認し、同じ事実を重ねて保存している部分を整理します。取引時点の単価や住所は現在値と意味が違うため、履歴として必要かどうかを先に検討してください。
オートナンバーは行を識別するために便利ですが、それだけで項目の依存関係が整理されるわけではありません。例えば明細IDを追加しても、受注日が受注IDに依存するという関係や、受注IDと行番号の二重登録を防ぐ必要は残ります。候補キーや業務上の一意性も含めて設計を確認してください。主キーを明細IDにする場合は、受注IDと行番号へ一意インデックスを設けるなど、識別と二重登録防止の両方を扱うことが大切です。
一般的な業務データでは第3正規形までを基本に検討すると、更新時の不整合を減らしやすくなります。ただし、単語が同じだからという理由だけで小さな表を大量に作るものではありません。各項目の意味と依存関係を確認し、更新ルールや履歴の要件に照らして判断します。性能や確定帳票の再現性などの理由で重複を持たせる場合は、正しい値をどこで管理し、いつ更新するかを明記します。分割数の多さを品質の基準にはしません。
数量と販売単価から常に同じ条件で求められる明細金額は、クエリで計算する方法が基本です。一方、確定済み請求額や税額には、端数処理、値引き、税率、締め時点などの条件が関係します。当時の金額を再現する要件があれば、計算条件や確定値を保存する設計も必要です。何でも削除するのではなく、再計算値と確定値を区別してください。保存した金額と数量が食い違わないよう、更新のタイミングと訂正方法も決めておきます。
受注IDと行番号で明細を識別する設計なら、同じ商品を複数行へ登録できます。例えば納期、加工条件、単価が違う同一商品を分けて扱えます。受注IDと商品IDを複合主キーにすると、同じ受注に同じ商品IDを二度登録できないため、その制約が業務に合うか確認してください。明細IDを主キーにする場合でも、受注IDと行番号の組合せは一意にしておくと、同じ行番号の二重登録を防げます。キーは実際の取引単位から決めます。
テーブルが分かれても、入力画面まで別々にする必要はありません。受注情報を入力する親フォームに、明細を入力するサブフォームを配置し、顧客や商品はプルダウンから選ぶ構成にできます。画面では名称を見せながら、テーブルにはIDを保存します。利用者に内部のテーブル構成を意識させず、入力の重複を減らすことが可能です。フォームを作る前にリレーションと保存先を決め、注文を切り替えたときに正しい明細だけが表示されるか確認します。
元ファイルをそのまま書き換えるより、バックアップと取込用コピーを作ってから分割する方が確認しやすくなります。氏名や会社名だけで名寄せすると、別の相手を同一として統合する危険があります。旧コードと新IDの対応を残し、判断できない行を例外一覧へ分けてください。移行後は受注件数、明細件数、数量や金額、未対応IDを照合します。元の表と新しい表では1行の意味が変わるため、同じ種類の件数を比較することが重要です。
関連記事
Accessの教科書でテーブル設計とデータ移行を相談する
ここまでの手順を試しても解決しない場合や、既存業務への影響を判断しにくい場合は、対象画面・現在の設定・再現手順を整理してご相談ください。Accessの教科書のお問い合わせ窓口から、状況に合う対応方法を相談できます。
参考情報
参考1 Microsoft データベース設計の基本
https://support.microsoft.com/en-us/access/database-design-basics
参考2 Microsoft テーブルのリレーションシップ
https://support.microsoft.com/en-us/access/guide-to-table-relationships
参考3 Microsoft データベース正規化の基本
https://learn.microsoft.com/en-us/troubleshoot/microsoft-365-apps/access/database-normalization-description