データベースで変更の多いテーブルの設計

質問

データベースを設計しています。

あるテーブルに関して、項目（列）が50個くらいあるのですが、これらのうち20個くらいの列が頻繁に変更があり、データの履歴が必要になるため、テーブルを再設計しようと考えています。

現在は変更があるたびにテーブル全体を別テーブルにコピーして、寄せています。

このような場合、何かよい設計方法はあるでしょうか？

回答No.8

noname#182251 · ベストアンサー

要望が今一つはっきりしませんが(^^;
以下のような設計ではいかが？

薬品テーブル：
　　　　　　　　コード（プライマリーキー）
　　　　　　　　名称

　　　　　　　　製造会社
　　　　　　　　新規登録日時

値段履歴テーブル（データが変更される項目を固定的なフィールドとして持つ）
　　　　　　　　ID（プライマリーキー）
　　　　　　　　コード（薬品テーブルの）
　　　　　　　　値段（仮に値段が履歴記録対照であるとして）
　　　　　　　　変更日時

XX履歴テーブル（データが変更される項目を固定的なフィールドとして持つ）
　　　　　　　　ID（プライマリーキー）
　　　　　　　　コード（薬品テーブルの）
　　　　　　　　XX
　　　　　　　　変更日時

・履歴を記録したいフィールド分、履歴テーブルを作る
・変更のタイミングが似通っているものがあれば、グループ化する
・仮に５０の履歴テーブルを作成して、リレーションを取った場合、パフォーマンスがどのようになるかは、私にとって未知の領域です。

お礼

ありがとうございます。

回答No.9

CHRONOS_0

システムの全体像が不明のまま話を進めても
無駄が多いだけのような

差し支えなければ50のフィールド全てを
ここにアップしてみては

お礼

そうですね。確かに曖昧なままでは無駄が多いですね。

ただ、全部のフィールドを載せるのはちょっと無理なので、これ以上、回答が無理だとすればそれでもしょうがないかもしれません。

説明力の無さを痛感します

回答No.7

noname#182251

以下のような設計ではいかが？

薬品テーブル：
　　　　　　　　コード（プライマリーキー）
　　　　　　　　名称

　　　　　　　　製造会社
　　　　　　　　新規登録日時

履歴テーブル（データが変更される項目を固定的なフィールドとして持つ）
　　　　　　　　ID（プライマリーキー）
　　　　　　　　コード（薬品テーブルの）
　　　　　　　　値段（仮に値段が履歴記録対照であるとして）
　　　　　　　　XX（その他履歴を記録するフィールド）

これで、通常一覧する際にはリレーションでデータを抽出できるし、ある製品に関し、履歴を参照することも、簡単に出来るでしょう。

お礼

ありがとうございます。

履歴テーブルに薬品テーブルの全フィールドを持つとすると、薬品テーブルと同じ構造に更新日付が付いた形になりますかね。そうすると3番目の回答と同じになりますね。

問題は特定の項目が変更されるわけではなく、キー以外の全項目に変更の可能性があることなんです。

回答No.6

CHRONOS_0

>50個って多いですかね。
回答したつもりだったのにはじかれたのかな・・・

テーブルの中身が分からないことには多いかどうかは断定できません
一般論としてはリレーショナルデータベースでは
フィールド数の少ないテーブルが数多く出来るという傾向があります
テーブルはいくつあるのでしょう
これひとつだけということなら先ず間違いなく問題ありでしょうね

補足で出されたテーブルはフィールド名が書かれている範囲では問題ありません

問題があるかどうかは全てのフィールド名を見ないと分からないですね
ただ、お困りの様子からはテーブル構造に問題ありカナという気はします

お礼

ありがとうございます。
そうですよね。やはり全体を見ないとなんともいえないものですよね。
非常にあいまいな質問で申し訳ありません。

回答No.5

skikichi

失礼しました。修正です！

誤：
ちなみに冗長性を考慮されるのでしたら、変更のあった項目だけを「更新履歴テーブル」にもたせたらいかがでしょうか？

正：
ちなみに冗長性を考慮されるのでしたら、変更のあった項目の前歴のみを「更新履歴テーブル」にもたせたらいかがでしょうか？
※したがって、もちろん最新の内容は薬品テーブルに上書きされています。

回答No.4

skikichi

あらっ！Accessではなかったですか(^^;

４万件、３年に一回の履歴更新でしたら、まったく問題ないです。

ちなみに冗長性を考慮されるのでしたら、変更のあった項目だけを「更新履歴テーブル」にもたせたらいかがでしょうか？

更新履歴テーブル：
　　コード
　　変更項目名
　　変更内容
　　変更登録日時

お礼

ありがとうございます。

冗長性を考慮するとこれがよさそうです。
ただ、SQLで対象日付より前の値を取得してひとつのテーブルとして行を取得するのが大変そうですね。

他にこれはというのがあればいいのですが。

回答No.3

skikichi

項目数に関しては、別に議論してください。

アルゴリズムはこんな感じです。

２つテーブルを作成してください。
たとえば・・・
薬品テーブル：
　　　　　　　　コード
　　　　　　　　名称
　　　　　　　　値段
　　　　　　　　製造会社
　　　　　　　　新規登録日時

変更履歴テーブル：
　　　　　　　　コード
　　　　　　　　名称
　　　　　　　　値段
　　　　　　　　製造会社
　　　　　　　　履歴登録日時

履歴登録用のフォームを作成

１．初期表示
薬品テーブルを単票表示させ、コードをキーにしたサブフォームに変更履歴テーブルを表示させる）
※同時に履歴登録用の非連結フィールド（名称、値段、製造会社）にも薬品テーブルと同じ内容を表示（コピー）させる。

２．履歴登録
非連結フィールドに必要な変更を加え、履歴登録ボタンをクリックすると、追加クエリーにより非連結フィールドの内容を変更履歴テーブルに追加する。
この時、コード、履歴登録日時（date）も追加するのを忘れずに。

３．最後にフォームを自動リフレッシュすれば、更新履歴テーブルに追加された内容が表示され確認できます。

お礼

ありがとうございます。

補足

まったく同じ履歴用のテーブルを持つと、複数の項目に対して別の日時で更新が行われた場合はかなり冗長なデータを持つ気がしますが、案外手っ取り早くていいかもしれません。

ちなみにレコード数は4万件くらいです。

書かれている作成の仕方はACCESSっぽいですが、実際やるのはACCESSではないです。DBはSQLAnyWhereです。

回答No.2

CHRONOS_0

頻繁に変更のあるようなものを列(フィールド)としているテーブル設計に問題があるのでは

アップされた情報からだけでは断定はできませんが
フィールド数の50個もかなり多いみたいですし
きちんと正規化が出来ていないテーブルの匂いがしますね

フィールドじゃなくレコードになるように設計を変えれば
履歴情報もテーブルに残せるように出来ます

出された情報からいえるのはここまでですかね

お礼

ありがとうございます。
たしかにそうですね。

50個って多いですかね。

補足

更なる回答を期待して補足します。

テーブル【薬品】
-----------------------------
コード |
名称 |
カナ |
効能 |
値段 |
形 |
会社 |
：
：
-----------------------------
こんな感じで50個くらい項目があって、通常は登録時のデータのままなのですが、たまに（3年に1回とか）どこかの項目に変更が変更されたりするため、全ての項目に関して更新履歴を持ちたいです。

回答No.1

anmochi

フィールドとして、更新日や更新履歴IDなどを主キーとして追加すればよいのでは。

つまり、データの変更があれば、更新日が現在日時のレコードを追加、または更新履歴IDをインクリメントして追加、などの動作に変更する。

お礼

ありがとうございます。