正規化はデータの性質に応じて理論的にテーブルを構成していくためのとても重要な手法ですが、既存のテーブルを正規化していくだけではちょっと実現しないような、思いつきにくい構成もあります。典型的なものについてここで補足します。
履歴管理
社員名簿というものをよく見かけると思います。テーブルに記録した場合の例は次のようなものです。
| 社員番号 | 氏名 | 生年月日 | 所属先 |
|---|---|---|---|
| 1 | 根本 良一 | 1984/01/23 | 広報課 |
| 2 | 久本 美佐江 | 1978/10/11 | 商品企画課 |
| 3 | 鈴木 龍平 | 1991/05/08 | 研究開発センター |
| 4 | 村山 誠 | 1980/12/12 | 広報課 |
| 5 | 滝本 颯太 | 1992/07/04 | 商品企画課 |
これを所属先順に並べ替えるなどして実用的な名簿として利用できます。
しかし、このようなテーブルですと、職員の異動があるたびに所属先が上書きされ、それまで所属していた担当がどこだったのかという情報が失われてしまいます。人事担当などの立場からは各社員の異動履歴を把握するために過去に所属していた担当も含めて記録することが必要となりますが、このようなニーズに対応したテーブルの例として次のようなものが挙げられます。
| 社員番号 | 氏名 | 生年月日 |
|---|---|---|
| 1 | 根本 良一 | 1984/01/23 |
| 2 | 久本 美佐江 | 1978/10/11 |
| 3 | 鈴木 龍平 | 1991/05/08 |
| 4 | 村山 誠 | 1980/12/12 |
| 5 | 滝本 颯太 | 1992/07/04 |
| 社員番号 | 異動日 | 所属先 |
|---|---|---|
| 1 | 2006/03/15 | 営業一課 |
| 1 | 2010/04/01 | 広報課 |
| 2 | 1999/05/01 | 研究開発センター |
| 2 | 2004/04/01 | 広報課 |
| 2 | 2010/07/01 | 商品企画課 |
| 3 | 2011/10/01 | 研究開発センター |
| 4 | 2008/07/01 | 秘書課 |
| 4 | 2012/07/01 | 広報課 |
| 5 | 2014/04/01 | 商品企画課 |
なお、この例での異動テーブルの主キーは{社員番号,異動日}となりますが、テーブルの例示をなるべく簡便にするためであり、実際は辞令IDといった列を設けて主キーとする方が一般的です。
さて、異動履歴としてはこれでいいのですが、現在の状況を把握することが難しくなっています。それでは、最初のテーブルのように現在の所属を表すテーブルと異動履歴を表すテーブルはそれぞれ別に保持していく必要があるのでしょうか。
しかし、そもそも現在の所属状況というのは異動が繰り返された結果ですので、異動履歴をもとに抽出できるのではないか、と考えるのが自然です。そこで少し考えて見ますと、異動テーブルをもとに
「それぞれの社員の直近の（最新の）異動を抽出する」
ということができれば、現在の配属状況を表せることがわかります。
実際に異動テーブルからそのようなクエリを作成することができます。右がそのデザインの例です。
データシートビューです。このクエリと異動テーブルを結合することで上記の社員名簿テーブルと同じようなクエリが作成できます。
さらに発展させますと、異動テーブルにおいて
「それぞれの職員の、指定日以前における直近の（最新の）異動を抽出する」
ということができれば、指定日時点での配属状況を表せることがわかります。
これも実際にクエリとして作成することができます。2011/4/1時点における直近の異動を抽出するデザインの例です。
データシートビューです。これと異動テーブルを結合することで2011/4/1時点の配属状況を表すクエリが作成できます。
このような設計とデータ抽出のアイデアがないと、任意の時点の名簿を抽出するために毎日テーブルに（社員の数だけ）新しい行を追加するという面倒なことにもなりかねません。工夫が必要というわけです。
なお、データ抽出の結果までを含めて別記事にまとめました。テーブルのつくりが上記とは若干異なりますが参考まで。
削除フラグ
通常、不要となった行（レコード）は削除されますが、本当に削除してしまうと都合が悪い場合には、削除するかわりにその行にある値を記録して「削除したことにする」ということがあります。
例としては次のようなものが挙げられます。
| 商品コード | 商品名 | 単価 | 削除フラグ |
|---|---|---|---|
| 1 | コーヒー | 130 | |
| 2 | りんごジュース | 130 | |
| 3 | メロンソーダ | 110 | 1 |
| 4 | 炭酸水 | 110 | |
| 5 | 緑茶 | 130 | |
| 6 | おしるこ | 120 | 1 |
| 販売ID | 商品コード | 数量 |
|---|---|---|
| 1 | 1 | 20 |
| 1 | 2 | 10 |
| 2 | 4 | 5 |
| 2 | 6 | 5 |
| 3 | 1 | 20 |
| 3 | 2 | 100 |
| 3 | 3 | 30 |
ある商品の取扱いをやめたときは、商品テーブルにある当該商品の行を削除すればよさそうですが、そうすると（販売実績を記録している）販売明細テーブルの内容とつじつまが合わなくなります。販売明細テーブルにも商品コードが記録されているためです。そこで、商品テーブルの行を削除するのではなく、「削除フラグ」列に「1」という値を記録するようにしています（Yes/No型もよく用いられます）。
値が記録されて削除扱いとなった商品については、販売担当者が扱うフォーム上では表示されないようにするなどして使用できないようにします。一方、販売実績の集計の際は削除扱いの有無に関わらず集計することにより正確な結果が得られるというわけです。
ところで、実際に列を削除する代わりに削除フラグという列を用いて記録する、という考え方が常に好ましいとは限りません。例えば削除フラグ列を設けて運用している場合であっても、誤操作によってありもしない商品情報を記録してしまったような場合にそのような商品情報を残しておく必要はなく、本当に行を削除してしまった方が良いのであって、データを削除すべきあらゆる場合に「削除フラグに値を記録する」という方法で対応する必要性があるとはいえません。
また、そもそも削除フラグを設ける意味合いが上記のように明確なのであれば、むしろ次のようなテーブルにしておいて「販売終了日」列を削除フラグのように扱う方が論理的と考えられます。
| 商品コード | 商品名 | 単価 | 販売開始日 | 販売終了日 |
|---|---|---|---|---|
| 1 | コーヒー | 130 | 2003/6/12 | |
| 2 | りんごジュース | 130 | 2008/10/1 | |
| 3 | メロンソーダ | 110 | 2005/6/1 | 2010/11/28 |
| 4 | 炭酸水 | 110 | 2007/6/11 | |
| 5 | 緑茶 | 130 | 2001/5/1 | |
| 6 | おしるこ | 120 | 2002/9/1 | 2014/4/15 |
削除フラグは一つの技法ではありますが、議論を呼びやすいものの一つでもあります。用いるにしても、なぜ実際にレコードを削除しないのか、削除しないなら何らかの形で利用され得るのか、利用され得るのであれば削除フラグではなく（上記「販売終了日」のように）極力論理的な表現を用いて誤解のないようにしておくべきではないのかといった点をきちんと整理しておくべきです。実際にレコードを削除してしまうと取り返しがつかないのではないかという不安はわからなくもありませんが、曖昧な理由で用いると混乱を招きますので注意が必要です。
スーパータイプ・サブタイプ
個人事業主と法人事業主のように、一定程度共通する部分を持ちながら異なる側面があるものを記録していく場合があります。次の例はこうした情報を1つのテーブルに収めようとしたものです。
| 会員コード | 名称 | 所在地 | 業種 | 個人法人区分 | 資本金額 | 居住地 |
|---|---|---|---|---|---|---|
| 1 | 山形屋商店 | 新町１−６−２ | 食料品店 | 個人 | 新町１−６−２ | |
| 2 | （株）タケダマート新町店 | 新町１−６−４ | スーパー | 法人 | 30,000,000 | |
| 3 | フラワーよしだ | 新町１−７−２ | 生花店 | 個人 | 大町３−２ | |
| 4 | 杉本薬局 | 中町２−２−１ | 調剤薬局 | 個人 | 中町２−２−１ | |
| 5 | 第百六十七銀行駅前支店 | 中町２−２−３ | 金融機関 | 法人 | 18,000,000,000 |
さて、資本金額という列については法人事業主の場合のみ値が記録されるようになっており、個人事業主については記録されません。一方で居住地という列は個人事業主の場合のみ値が記録されるようになっています。特に珍しい例でもないですし不都合でもないように思われますが、例えば個人会員において資本金額というのは全く関係のない無用な列であり、それがテーブルに含まれているというのは厳しく見れば適切でない面があるように思われます。また、Access（に限らずデータベースの世界）ではNullは厄介な存在であり、極力生じさせるべきではないものと考えられています。こうした理由により、テーブルを次のように分割することがあります。
| 会員コード | 名称 | 所在地 | 業種 | 個人法人区分 |
|---|---|---|---|---|
| 1 | 山形屋商店 | 新町１−６−２ | 食料品店 | 個人 |
| 2 | （株）タケダマート新町店 | 新町１−６−４ | スーパー | 法人 |
| 3 | フラワーよしだ | 新町１−７−２ | 生花店 | 個人 |
| 4 | 杉本薬局 | 中町２−２−１ | 調剤薬局 | 個人 |
| 5 | 第百六十七銀行駅前支店 | 中町２−２−３ | 金融機関 | 法人 |
| 会員コード | 資本金額 |
|---|---|
| 2 | 30,000,000 |
| 5 | 18,000,000,000 |
| 会員コード | 居住地 |
|---|---|
| 1 | 新町１−６−２ |
| 3 | 大町３−２ |
| 4 | 中町２−２−１ |
法人事業主専用の列、個人事業主専用の列についてそれぞれ別のテーブルに分けることにより、無関係な列の存在を解消し、Nullの発生も防止してします。
ここで、会員とは法人会員と個人会員を包含するものであり、このような存在をスーパータイプと呼びます。また、会員の分類である法人会員、個人会員をそれぞれサブタイプと呼びます。このような例としては自動車（スーパータイプ）と新車・中古車（サブタイプ）、学校（スーパータイプ）と私立校・公立校（サブタイプ）、事業拠点（スーパータイプ）と工場・営業所（サブタイプ）といった例が考えられます。ただし、スーパータイプ・サブタイプというのはあくまでこれらの例のような概念であり、これに沿ってテーブルを分割するかどうかは現実の処理のあり方に過ぎません。分割していないもとのテーブルにも会員とスーパータイプと法人会員・個人会員というサブタイプが包含されているともいえます。
なお、テーブルを分割している例では2つテーブルを増やして済んでいますが、理屈の上ではサブタイプの種類の数だけテーブルが増えることが考えられます。結果的に結合の手間がかかるなど扱いが複雑になりますので、通常は分割しない場合が多いです（紹介しておいて何ですが）。ただしスーパータイプ、サブタイプと言う概念自体はよく現れるものますので覚えておいて損はありません。
ちなみに、法人個人区分のような列は必須というわけではありませんが、実用上は設けられることが多いです。
自己参照
外部キーは通常、他のテーブルにある主キーを参照するものとなりますが、これが同一テーブル内の主キーを参照するような構造となる場合があります。
例としては次のようなテーブルが挙げられます。ある商品の後継となる商品を「後継商品コード」として表していますが、これが参照しているのは同じテーブルにある商品コードにほかなりません。
| 商品コード | 商品名 | 商品分類 | 後継商品コード |
|---|---|---|---|
| D01102A | ND2000 | ミラーレス一眼カメラ | D01208N |
| D01108K | ND5S | ミラーレス一眼カメラ | D01382K |
| D01208N | ND3000 | ミラーレス一眼カメラ | |
| D01382K | ND6 | ミラーレス一眼カメラ | |
| D03402C | FX710 | コンパクトデジタルカメラ | D03591X |
| D03533D | FX2000 | コンパクトデジタルカメラ | |
| D03591X | FX750 | コンパクトデジタルカメラ | D03620X |
| D03620X | FX800 | コンパクトデジタルカメラ |
つくりはシンプルですが、ある商品の次の代の商品だけでなく、2代後、3代後というように何代後の商品までもたどっていけるのが興味深いところです。ただし、このように表すことが出来るのは、1つの商品に対する後継商品が1つに限られる場合のみです。一般には1つの商品に複数の後継商品があったり、1つの商品が複数の商品の後継商品となる場合もあります。そこまで考慮した場合は1つのテーブルに納まらず、次のような形になります。
| 商品コード | 商品名 | 商品分類 |
|---|---|---|
| D01102A | ND2000 | ミラーレス一眼カメラ |
| D01108K | ND5S | ミラーレス一眼カメラ |
| D01208N | ND3000 | ミラーレス一眼カメラ |
| D01382K | ND6 | ミラーレス一眼カメラ |
| D03402C | FX710 | コンパクトデジタルカメラ |
| D03533D | FX2000 | コンパクトデジタルカメラ |
| D03591X | FX750 | コンパクトデジタルカメラ |
| D03620X | FX800 | コンパクトデジタルカメラ |
| 先代商品コード | 後継商品コード |
|---|---|
| D01102A | D01208N |
| D01108K | D01382K |
| D03402C | D03591X |
| D03591X | D03620X |
