履歴を管理するテーブル構造について

質問

はじめまして。
例えば、
どの商品をどの顧客が買ったかどうかという販売履歴を保存していくとします。
このときのリレーションシップとして、顧客IDと商品コードはずっと現行の顧客テーブルや
商品コードとだぶらないようにずっと整合性をたもっていくものなのでしょうか？
また、もとから正規化をくずすものなのでしょうか？
また、新たに履歴用のキーをそれぞれつけるものなのでしょうか？
データウェアハウスではどのような手法が使用されているのでしょうか。
履歴を保持していく際のテーブル設計の具体的なアドバイスを頂けたら幸いです。
よろしくお願い致します。

回答No.2

msystem · ベストアンサー

>例えば、履歴管理の為の販売履歴テーブルに商品コードの外部キーが存在するとします。
>この場合商品テーブルを現行テーブルと履歴管理用に分割した方がよいのでしょうか？
このあたりは、要件次第だと思います。まず、日常的に読み書きするデータベース（一般にOLT用）と、履歴を管理し分析などに使うデータベース（DWH用）のデータベースを、1つのデータベースとするか、はたまた別のデータベースとするかが、ポイントになると思います。同じデータベースなら、できるだけマスターとなるテーブルは共通のほうが使いやすいでしょうし、別ならば別にしなければどうしようもありません。
>その場合の履歴用商品テーブルについてですが、販売履歴のプライマリーキーはシリアルで、商品テーブルのプライマリーキーは商品コードであったとき、商品コードが永続的に一意でない、流動的に変化するような会社の場合は、商品履歴テーブルは、連番を新たにプライマリーキーにしリレーションシップを取り直す方がよろしいのでしょうか？それとも、もとの現行データベースを構築しなおした方がよいのでしょうか？
うーん、これも難しい問題で、データベースの教科書によると、テーブルのプライマリーキーは永続的に変わらず一意であるものにする、と書かれているものが多いと思います。これを守るのであれば、現行のデータベースを構築しなおしということになります。（履歴テーブルのシリアル値というのも商品のシリアルであれば、PKには不向きであるような気がします。）
ただし、商品マスターであっても、当然メンテナンスされることがあり、徐々に変化することは逃れられないのは事実ですから、いかなる値をPKにするか、また、商品マスター自身に変化の履歴を管理したい場合に、どのように対応すればよいか、という問題は必ず付きまといます。（Oracleでは、一例としてDWH向けのソリューションで紹介していたこともありました）
どちらにしても、PKは一意であることを保証しなければなりませんので、履歴テーブルと商品マスターテーブルの間に、もうひとつテーブルを入れてPKを一意にするなどの修正対応かテーブル設計の見直しなどは、必要になると思います。（コストなどの要件により決まるかもしれませんね）

補足

ご丁寧にありがとうございます。
納得します。
しつこいと思わるかもしれませんが。。、最後に一つ教えてください。

>履歴テーブルと商品マスターテーブルの間に、もうひとつ
>テーブルを入れてPKを一意にするなどの修正対応かテーブ
>ル設計の見直しなどは、必要になると思います。
具体的に、実践ではその中間テーブルのキー構造はどのようなものが多いのでしょうか？
--------------------------------
私の能力で考えられものは以下の通りですが。。。
例えば、商品コードが一意の場合は、商品マスタは
・商品コード（PK)
・有効年月日（PK）
・販売価格
・・・
のようにして、販売価格を検索する際に、その商品の販売日時より大きく一番小さい有効年月日の商品を選択する方法が一つ。
また、商品コード自体が変動する場合は、
・登録順連番（PK)
・有効年月日（PK）
・商品コード
・販売価格
・・・
のようなものが思いつきます。

回答No.5

tosi0000

>この場合ですと、商品の販売価格等の履歴は、商品マスタ
>で管理するのではなく、すべて、販売情報として複製され
>るということでしょうか？
どちらでも良いと思います。
運用で決まります。
単価の変動が全く無いものであれば、商品マスタに入れますし、その都度単価が違っていれば、この履歴ファイルへ入れても良いと思います。
同様に、販売単価の値引き・値上げなどが受注後に発生するようなものであれば、別途にファイルの作成を考慮する必要も出てきます。

>別途に先頭へ識別番号用の項目を追加して、キー情報とし
>て連番を付けて行きます。これをマスタindexファイルと
>します。
>とは具体的にはどういうことですか？
他のファイル（=テーブル）からこの履歴ファイルを参照するための、indexファイルと言う意味合いです。
複雑なシステムになってきますと、多数のファイルから、別の多数のファイルへの読込が発生してきます。そのため、各ファイルに対して、キーとなる項目を決めて関連付けを行えば、整理されたシステムが出来ると言う意味合いです。
単純なシステムであれば、これが逆に複雑に見えて足枷となってしまいます。

運用・環境などで色々と状況は変わってきますので、これが正しいとはなかなか決めることが出来ない分野であろうと思います。
（尚、ファイルはテーブルの意味合いで読んで下さい。）

回答No.4

tosi0000

こんにちは。

履歴ファイルとして、以下のようなテーブルを作成すればよいと思いますが、如何でしょうか？

顧客販売実績履歴ファイル：（仮名）

顧客コード：キー１
日付：キー２
時間：キー３
商品コード：キーなし
販売情報１：キーなし
販売情報２：：キーなし
　・
　・

あとは必要に応じてindexを作成すれば良いとおもいます。
正規化をくずすと言われる部分が、理解できないですが、
私でしたら、こんな感じのファイルを作ると思います。

もしも、システム的に大きいものであれば、別途に先頭へ識別番号用の項目を追加して、キー情報として連番を付けて行きます。これをマスタindexファイルとします。
その他のindexファイルは、用途に合わせて、複数作成します。

以上ですが、もっと上手に作る人もいると思います。

お礼

そうだったのですね。
有難う御座います。

補足

ご回答ありがとうございます。
この場合ですと、商品の販売価格等の履歴は、商品マスタで管理するのではなく、すべて、販売情報として複製されるということでしょうか？
>別途に先頭へ識別番号用の項目を追加して、キー情報とし
>て連番を付けて行きます。これをマスタindexファイルと
>します。
とは具体的にはどういうことですか？

話は変わりますが、データマイニングのアソシエーション手法（アマゾン.comのこの本を買った人は他のお客さまも購入しています等）の具体的なSQL、プログラムロジックをお知りでしたら概要でいいので、どなたかお知りではないでしょうか？

お待ちしております。

回答No.3

msystem

>私の能力で考えられものは以下の通りですが。。。
>例えば、商品コードが一意の場合は、商品マスタは
>・商品コード（PK)
>・有効年月日（PK）
>・販売価格
>・・・
>のようにして、販売価格を検索する際に、その商品の販売>日時より大きく一番小さい有効年月日の商品を選択する方>法が一つ。
>また、商品コード自体が変動する場合は、
>・登録順連番（PK)
>・有効年月日（PK）
>・商品コード
>・販売価格
>・・・

私の勝手な意見として書きますね。（最適ではないということ）

販売履歴テーブル
・履歴ID（PK)
・販売日
・販売商品ID（FK)
・数量
・単価
・・・

商品マスターテーブル
・商品ID（PK)　　　snake103さんの言う登録順連番
・販売価格
・名称
・・・
PKは何かの複合主キーでも構いません。

商品マスタ変換テーブル
・販売商品ID（PK)　　　販売履歴テーブルとリレーションがある
・商品ID（FK)
・商品コード
・使用開始年月日
（・使用終了年月日）
・・・
「使用終了年月日」に関しては、難しいところです。冗長といえば冗長です。
これにより、同じ商品に対し商品コードが代わっても履歴を管理できます。また、販売価格に関しても、変換テーブルに入れたほうが良いという意見もあります。

「このような形にするときに、効率的な検索ができる仕組みがあります」とOracleの方がおっしゃってました。

お礼

大変参考になりました。
販売商品ID（PK)がみそなんですね。
またの機会がありましたら、お願いします。

回答No.1

msystem

顧客IDと商品コードの整合性ですが、これは要件によると思います。たとえば、A社とB社があり、それぞれの名前が変更になったとき、変更前の社名の履歴が必要かどうか、また、2社が合併した場合はどうするか？商品についても同じだと思います。また、正規化を崩すのは、やはりパフォーマンスや集計のためにどうしても必要である場合のみでよいのではないでしょうか？ある集計をしたいときに、DBが2時間ほど集計にかかるので、日中は無理だとすれば、前日までの値で夜のうちに集計をする、または、正規化を崩して集計時間を短くし、日中に集計をできるようにする。これは、譲れるのか譲れないかの要件だと思います。（私ならできるだけ正規化は崩しません）
履歴用のキーは、DWHでは一般に大きなファクト表に対し、小さいマスター表がいくつもぶら下がっているのが、普通だと思いますので、この場合、私には履歴テーブルがファクト表になると思います。ということは、履歴テーブルに独立したPKがあるほうが良いと思われます。

補足

ご回答有難うございます。
補足させてください。
>履歴テーブルに独立したPKがあるほうが良いと思われます。
例えば、履歴管理の為の販売履歴テーブルに商品コードの外部キーが存在するとします。
この場合商品テーブルを現行テーブルと履歴管理用に分割した方がよいのでしょうか？その場合の履歴用商品テーブルについてですが、
販売履歴のプライマリーキーはシリアルで、商品テーブルのプライマリーキーは商品コードであったとき、商品コードが永続的に一意でない、流動的に変化するような会社の場合は、商品履歴テーブルは、連番を新たにプライマリーキーにしリレーションシップを取り直す方がよろしいのでしょうか？それとも、もとの現行データベースを構築しなおした方がよいのでしょうか？
宜しくお願い致します。