テーブル構成に関する補足

 正規化はデータの性質に応じて理論的にテーブルを構成していくためのとても重要な手法ですが、既存のテーブルを正規化していくだけではちょっと実現しないような、思いつきにくい構成もあります。典型的なものについてここで補足します。

履歴管理

 社員名簿というものをよく見かけると思います。テーブルに記録した場合の例は次のようなものです。

社員名簿テーブル
社員番号氏名生年月日所属先
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

異動テーブル
社員番号異動日所属先
12006/03/15営業一課
12010/04/01広報課
21999/05/01研究開発センター
22004/04/01広報課
22010/07/01商品企画課
32011/10/01研究開発センター
42008/07/01秘書課
42012/07/01広報課
52014/04/01商品企画課

 なお、この例での異動テーブルの主キーは{社員番号,異動日}となりますが、テーブルの例示をなるべく簡便にするためであり、実際は辞令IDといった列を設けて主キーとする方が一般的です。
 さて、異動履歴としてはこれでいいのですが、現在の状況を把握することが難しくなっています。それでは、最初のテーブルのように現在の所属を表すテーブルと異動履歴を表すテーブルはそれぞれ別に保持していく必要があるのでしょうか。
 しかし、そもそも現在の所属状況というのは異動が繰り返された結果ですので、異動履歴をもとに抽出できるのではないか、と考えるのが自然です。そこで少し考えて見ますと、異動テーブルをもとに
 「それぞれの社員の直近の(最新の)異動を抽出する」
ということができれば、現在の配属状況を表せることがわかります。

 実際に異動テーブルからそのようなクエリを作成することができます。右がそのデザインの例です。


 データシートビューです。このクエリと異動テーブルを結合することで上記の社員名簿テーブルと同じようなクエリが作成できます。


 さらに発展させますと、異動テーブルにおいて
 「それぞれの職員の、指定日以前における直近の(最新の)異動を抽出する」
ということができれば、指定日時点での配属状況を表せることがわかります。

 これも実際にクエリとして作成することができます。2011/4/1時点における直近の異動を抽出するデザインの例です。


 データシートビューです。これと異動テーブルを結合することで2011/4/1時点の配属状況を表すクエリが作成できます。

 なお、このようなデータ抽出のアイデアがないと、任意の時点の名簿を抽出するために毎日テーブルに(社員の数だけ)新しい行を追加するという面倒なことにもなりかねません。工夫が必要というわけです。

削除フラグ

 通常、不要となった行(レコード)は削除されますが、本当に削除してしまうと都合が悪い場合には、削除するかわりにその行にある値を記録して「削除したことにする」ということがあります。
 例としては次のようなものが挙げられます。

商品テーブル
商品コード商品名単価削除フラグ
1コーヒー130
2りんごジュース130
3メロンソーダ1101
4炭酸水110
5緑茶130
6おしるこ1201

販売明細テーブル
販売ID商品コード数量
1120
1210
245
265
3120
32100
3330

 ある商品の取扱いをやめたときは、商品テーブルにある当該商品の行を削除すればよさそうですが、そうすると(販売実績を記録している)販売明細テーブルの内容とつじつまが合わなくなります。販売明細テーブルにも商品コードが記録されているためです。そこで、商品テーブルの行を削除するのではなく、「削除フラグ」列に「1」という値を記録するようにしています(Yes/No型もよく用いられます)。
 値が記録されて削除扱いとなった商品については、販売担当者が扱うフォーム上では表示されないようにするなどして使用できないようにします。一方、販売実績の集計の際は削除扱いの有無に関わらず集計することにより正確な結果が得られるというわけです。

 ところで、実際に列を削除する代わりに削除フラグという列を用いて記録する、という考え方が常に好ましいとは限りません。例えば削除フラグ列を設けて運用している場合であっても、誤操作によってありもしない商品情報を記録してしまったような場合にそのような商品情報を残しておく必要はなく、本当に行を削除してしまった方が良いのであって、データを削除すべきあらゆる場合に「削除フラグに値を記録する」という方法で対応する必要性があるとはいえません。
 また、そもそも削除フラグを設ける意味合いが上記のように明確なのであれば、むしろ次のようなテーブルにしておいて「販売終了日」列を削除フラグのように扱う方が論理的と考えられます。

商品テーブル
商品コード商品名単価販売開始日販売終了日
1コーヒー1302003/6/12
2りんごジュース1302008/10/1
3メロンソーダ1102005/6/12010/11/28
4炭酸水1102007/6/11
5緑茶1302001/5/1
6おしるこ1202002/9/12014/4/15

 削除フラグは一つの技法ではありますが、議論を呼びやすいものの一つでもあります。用いるにしても、なぜ実際にレコードを削除しないのか、削除しないなら何らかの形で利用され得るのか、利用され得るのであれば削除フラグではなく(上記「販売終了日」のように)極力論理的な表現を用いて誤解のないようにしておくべきではないのかといった点をきちんと整理しておくべきです。実際にレコードを削除してしまうと取り返しがつかないのではないかという不安はわからなくもありませんが、曖昧な理由で用いると混乱を招きますので注意が必要です。

スーパータイプ・サブタイプ

 個人事業主と法人事業主のように、一定程度共通する部分を持ちながら異なる側面があるものを記録していく場合があります。次の例はこうした情報を1つのテーブルに収めようとしたものです。

商業会員テーブル
会員コード名称所在地業種個人法人区分資本金額居住地
1山形屋商店新町1−6−2食料品店個人新町1−6−2
2(株)タケダマート新町店新町1−6−4スーパー法人30,000,000
3フラワーよしだ新町1−7−2生花店個人大町3−2
4杉本薬局中町2−2−1調剤薬局個人中町2−2−1
5第百六十七銀行駅前支店中町2−2−3金融機関法人18,000,000,000

さて、資本金額という列については法人事業主の場合のみ値が記録されるようになっており、個人事業主については記録されません。一方で居住地という列は個人事業主の場合のみ値が記録されるようになっています。特に珍しい例でもないですし不都合でもないように思われますが、例えば個人会員において資本金額というのは全く関係のない無用な列であり、それがテーブルに含まれているというのは厳しく見れば適切でない面があるように思われます。また、Access(に限らずデータベースの世界)ではNullは厄介な存在であり、極力生じさせるべきではないものと考えられています。こうした理由により、テーブルを次のように分割することがあります。

会員テーブル
会員コード名称所在地業種個人法人区分
1山形屋商店新町1−6−2食料品店個人
2(株)タケダマート新町店新町1−6−4スーパー法人
3フラワーよしだ新町1−7−2生花店個人
4杉本薬局中町2−2−1調剤薬局個人
5第百六十七銀行駅前支店中町2−2−3金融機関法人

法人会員テーブル
会員コード資本金額
230,000,000
518,000,000,000

個人会員テーブル
会員コード居住地
1新町1−6−2
3大町3−2
4中町2−2−1

法人事業主専用の列、個人事業主専用の列についてそれぞれ別のテーブルに分けることにより、無関係な列の存在を解消し、Nullの発生も防止してします。
 ここで、会員とは法人会員と個人会員を包含するものであり、このような存在をスーパータイプと呼びます。また、会員の分類である法人会員、個人会員をそれぞれサブタイプと呼びます。このような例としては自動車(スーパータイプ)と新車・中古車(サブタイプ)、学校(スーパータイプ)と私立校・公立校(サブタイプ)、事業拠点(スーパータイプ)と工場・営業所(サブタイプ)といった例が考えられます。ただし、スーパータイプ・サブタイプというのはあくまでこれらの例のような概念であり、これに沿ってテーブルを分割するかどうかは現実の処理のあり方に過ぎません。分割していないもとのテーブルにも会員とスーパータイプと法人会員・個人会員というサブタイプが包含されているともいえます。
 なお、テーブルを分割している例では2つテーブルを増やして済んでいますが、理屈の上ではサブタイプの種類の数だけテーブルが増えることが考えられます。結果的に結合の手間がかかるなど扱いが複雑になりますので、通常は分割しない場合が多いです(紹介しておいて何ですが)。ただしスーパータイプ、サブタイプと言う概念自体はよく現れるものますので覚えておいて損はありません。
 ちなみに、法人個人区分のような列は必須というわけではありませんが、実用上は設けられることが多いです。

自己参照

 外部キーは通常、他のテーブルにある主キーを参照するものとなりますが、これが同一テーブル内の主キーを参照するような構造となる場合があります。
 例としては次のようなテーブルが挙げられます。ある商品の後継となる商品を「後継商品コード」として表していますが、これが参照しているのは同じテーブルにある商品コードにほかなりません。

商品テーブル
商品コード商品名商品分類後継商品コード
D01102AND2000ミラーレス一眼カメラD01208N
D01108KND5Sミラーレス一眼カメラD01382K
D01208NND3000ミラーレス一眼カメラ
D01382KND6ミラーレス一眼カメラ
D03402CFX710コンパクトデジタルカメラD03591X
D03533DFX2000コンパクトデジタルカメラ
D03591XFX750コンパクトデジタルカメラD03620X
D03620XFX800コンパクトデジタルカメラ

 つくりはシンプルですが、ある商品の次の代の商品だけでなく、2代後、3代後というように何代後の商品までもたどっていけるのが興味深いところです。ただし、このように表すことが出来るのは、1つの商品に対する後継商品が1つに限られる場合のみです。一般には1つの商品に複数の後継商品があったり、1つの商品が複数の商品の後継商品となる場合もあります。そこまで考慮した場合は1つのテーブルに納まらず、次のような形になります。

商品テーブル
商品コード商品名商品分類
D01102AND2000ミラーレス一眼カメラ
D01108KND5Sミラーレス一眼カメラ
D01208NND3000ミラーレス一眼カメラ
D01382KND6ミラーレス一眼カメラ
D03402CFX710コンパクトデジタルカメラ
D03533DFX2000コンパクトデジタルカメラ
D03591XFX750コンパクトデジタルカメラ
D03620XFX800コンパクトデジタルカメラ

後継商品管理テーブル
先代商品コード後継商品コード
D01102AD01208N
D01108KD01382K
D03402CD03591X
D03591XD03620X