- - PR -
すべてのテーブルの変更履歴を１つのテーブルで管理すると？？
1
| 投稿者 | 投稿内容 | ||||
|---|---|---|---|---|---|
|
投稿日時: 2005-03-28 17:30
こんにちは。mysql4.1.7です。
社内のあらゆるデータを管理するWEBアプリを作成しています。建設会社で、契約内容の変更、社員の住所の変更、お客様の情報の変更、取引業者の情報の変更等の変更履歴情報を、ひとつのテーブルで管理させるようにしたらどうかと思いまして質問させていただいてます。書類の作成まで自動でさせるため、変更日以前のデータと変更後では作成する情報が変わります。各情報テーブルにほとんど一緒のテーブル構成で変更IDだけ追加した履歴テーブルを作ると、後で管理が煩雑になるかと思い、一元管理できればなと思っています。イメージをうまく表現できるかわかりませんが、以下のように作成しようと思ってます。 ＝＝＝＝テーブル情報を格納するテーブル（table_info）＝＝＝＝ table_id int (PK) table_name char(100) ------------------------------------------------------------ ＝＝＝＝変更履歴を格納するテーブル（henkou_rireki）＝＝＝＝ ID INT auto_increment （PK) table_id int info_id int now_id int 現在の変更ID henkou_day 変更した日（この日の前と後で情報を分ける） ------------------------------------------------------------ ＝＝＝＝各情報テーブル（社員、業者、工事情報等）３０種類ぐらい＝＝＝＝ info_id int henkou_id int （info_idとこの二つで複合キー） 以下は各テーブルによって必要な情報 たとえば社員テーブルなら氏名、住所等 ------------------------------------------------------------ アプリ側のデータベース処理がかなり複雑になりますが、ほしい情報のテーブル名に対応するテーブルidと、その中の何番目の情報かというinfo_id、現在何回目の変更かを示すnow_idで現在の情報はどれなのかを管理し、sqlにて SELECT * FROM 情報テーブル名 WHERE info_id=履歴テーブル.info_id and henkou_id=履歴テーブル.now_id こんな感じで抽出しようと思ってます。 素人なのでどんな問題が予想されるかわかりませんが、変更履歴テーブルにだけかなりの負荷がかかってしまい良くないのかなーというのと、一度どこかで不具合が発生すると修復不能になるかも知れないのかという不安があります。皆様が基準日を設けてその前の情報とその後の情報を明確にしたい場合どんな方法をとっているのかを教えていただきたいのです。 ご教示いただけると幸いです。 [ メッセージ編集済み 編集者: take 編集日時 2005-03-28 17:37 ] [ メッセージ編集済み 編集者: take 編集日時 2005-03-28 17:38 ] [ メッセージ編集済み 編集者: take 編集日時 2005-03-29 08:52 ] | ||||
|
投稿日時: 2005-03-28 18:53
mysql4.1.7さん、こんにちわ。
なぜ変更履歴を残す必要があるのか分かりませんが、「insert/update」された際にSQLを記録するようにすれば、大体の変更点は分かると思います。 （もちろん、複数カラムを対象にしたinsert/update SQLを発行していない場合に限りますが） （で、MySQLについてはよく分からないけど、ひょっとしたら発行したSQLの履歴はMySQL側で取る方法は無いだろうか？とか疑問沸いてきますね） 「なぜ、変更履歴を残す必要があるのか？」をよく考えて、そこの所を直接質問した方がいい答えをもらえると思いますよ （例えば、障害に備えてバックアップを取りたかった等の要望があった場合は、バックアップの為のいいソリューションがありますか？とか（生憎、私は知りませんが（汗））） | ||||
|
投稿日時: 2005-03-29 08:50
冬寂さん、ご返答ありがとうございます。
すみません、なかなかイメージを伝えるのが苦手なもので・・・。たとえば、販売管理システムだったら、周辺の業者との競争が激しくなって、4月から商品の単価を全体的に見直して値下げする場合と似ています。3月までの売り上げの商品単価と4月以降の商品単価を別に考えないと販売管理システムが成り立たないですよね。そんな感じで、例えば当社ですと2年前に○○県○○知事から受注した工事と、今年○○県△△知事から受注した工事では、同じ県の知事ですが氏名が変わっています。また、同じ工事の情報でも、契約の変更が工事途中で発生した場合、変更前の情報を残す必要があります。このように残す情報が多い上ので、後で仕様変更があった場合にどう設計したほうが効率的なのか他の皆様のご意見をお聞きしたかったのです。何とか伝わりますでしょうか？？（＾＾） すみません、自分が最初にした投稿にいろいろテーブルの構成を書いていますが、変更日とかそういうのが抜けているのでさらにわかりにくくなったかと思います。変更日を基準にして、この日より前の情報と後の情報を分けるようにする予定です。（訂正しておきます） 何卒よろしくお願いいたします。 | ||||
|
投稿日時: 2005-03-29 09:34
これは業務要件ですよねぇ？ ならば、テーブル名やSQLを履歴で持ってもしかたないのではないでしょうか？ 実テーブル名とかいうより論理単位（または業務単位）で考えないと、DB管理者が わかれば良いってものでもないでしょう。 マスタやトランザクション側の設計で日付等を追加してInsertOnlyな構造にする方 が一般的でしょうね。でも、あえてそれを一元管理ということであれば更新・追加 する画面等の要素のMAX数分用意し（テキスト型）、日付時刻、Insert／Update区分 操作者、処理名、または画面名といった項目（ヘッダ部と呼んでおきます。）と共に履 歴テーブルのようなものを作っておきましょう。 で、後はどう格納するかはニーズにあわせておけば良いと思います。 １．Insertはヘッダ部のみ、Updateはヘッダ部と変更前の情報（変更後は現テーブル） ２．InsertはInsertしたデータすべて（１Rec）、UpdateはUpdate前・後の２Rec 等のように、お好きな方で。各処理にSQLを発行する部分を追加する必要がありますが、 業務としてこれをエンドユーザーが使用するならこのほうがよろしいかと。 イベント的にはトリガーを使う方法もあるかと思います。 ※DB（システム）管理者がわかればよいと言うことであれば、日付時刻、操作者と 発行SQLを格納しておけばよろしいかと。 （追記）伝票データのようにヘッダテーブルと明細テーブル（複数Rec）の形で保持 しているものは、もう少し考えないといけませんけどね。 [ メッセージ編集済み 編集者: Beatle 編集日時 2005-03-29 09:38 ] | ||||
|
投稿日時: 2005-03-29 12:09
Beatleさんご返答ありがとうございます。
InsertOnlyということは、ほとんど同じ情報を持つレコードが変更した数だけあるってことじゃないですよねー…。実際に開発するのが初めてなものでなかなか理解ができなくて申し訳ないです。やったことないのでどんなテーブル構成のことをおっしゃっているのかイメージがわかないのです。 例えば商品テーブルと業者テーブルが以下のようにあった場合、 商品テーブル +----+----------+---------+ | id | name | tanka | +----+----------+---------+ | 1 | syouhin1 | tanka1 | | 2 | syouhin2 | tanka2 | +----+----------+---------+ 業者テーブル +----+----------+---------+ | id | name | juusyo | +----+----------+---------+ | 1 | gyousya1 | juusyo1 | | 2 | gyousya2 | juusyo2 | +----+----------+---------+ 4月1日からsyouhin1の単価がtanka3に変わり、5月1日にgyousya1の住所がjuusyo3に変わる場合、あとひとつのテーブルはどのようになって、この二つのテーブルもどうなるか知りたいです。どうか素人ですので詳しくご教示いただけると幸いです。 | ||||
|
投稿日時: 2005-03-29 13:14
まずこの部分はよろしくないのでは？ 1テーブル内に、社員の情報も、お客様の情報もなんでもかんでも入れてしまうと レコード長が長くなってしまいますよね。 なんで、 ・社員の情報は社員情報を格納するテーブル ・お客様の情報はお客様情報を格納するテーブル ってデータをグループ化してそれぞれ別テーブルにしたほうがいいのでは？ その上で、社員データのうち変わらない部分(たとえばIDであったり、性別であったり)と 変わる可能性のある部分(住所であったり) に切り分けて別テーブルにして、 変わらない部分に対して履歴を取るようにすれば良いと思いますが。 社員を例に取ったので、社員テーブルについて書けばこんな感じ？ 社員テーブル(PK=ID) +----+------+----------+ | ID | NAME | DATE | +----+------+----------+ | 01 | Nam1 |2005/03/01| | 02 | Nam2 |2005/03/02| +----+------+----------+ 社員詳細テーブル(PK=ID,REV) +----+-----+---------+----------+ | ID | REV | juusyo |DATE | +----+-----+---------+----------+ | 1 | 001 | juusyo1 |2004/04/01| | 1 | 002 | juusyo2 |2005/03/01| | 2 | 001 | juusyo3 |2005/03/02| +----+-----+---------+----------+ たとえばID=01の最新情報が欲しい場合は 社員詳細テーブルのREVが最大値のものをみれば良いですし、 ID=001の人で2004年12月01日時点の情報が知りたい場合は 社員詳細テーブルのDATE＜2004/12/01でREVの最大値のものをみれば良いですし・・・。 こんなんいかがでしょう？ | ||||
|
投稿日時: 2005-03-29 13:47
何通りか持ち方もあるのですが、その一つとして、 ５月１日に単価が改訂された場合、 商品テーブル +----+--------+--------+---------+ | id |YukoDate|name | tanka | +----+--------+--------+---------+ | 1 |19000101|syouhin1| tanka1 | | 1 |20050501|syouhin1| tanka1改| | 2 |19000101|syouhin2| tanka2 | +----+--------+--------+---------+ というように、有効開始日を持たせるという形です。（更新日等はCreateDateとか 別のカラムに持ちます。）PK：id + YukoDate これですと、参照する場合に対象日（システム日付等）とid内でのMax(YukoDate)で の参照となるので、多少複雑になるのと、入力間違い等の為にUpdate処理（または Delete）も必要になる面がありますが、過去データもマスタとの結合でそのときのマ スタ値で表示できるというメリットもあります。データ側にマスタ値を持つ場合もあ りますが、過去にさかのぼって改訂等は別途バッチで変更することになります。 まぁ、どちらが良いかはケースバイケースですけどね。 RealTimeなマスタ情報のみで、その他は単に履歴として扱うのみであれば、YukoDate はSeqNoでも可能です。このときはSeqNoのMAXを参照するようにすれば現在の値が 取得できます。 あと、廃止等は有効／無効フラグなんかを用意すればいいんじゃないでしょうか。 [ メッセージ編集済み 編集者: Beatle 編集日時 2005-03-29 13:51 ] | ||||
|
投稿日時: 2005-03-29 15:29
ご返答ありがとうございます。皆様のご意見から見つめなおして、ちょっと考えて見ます。頭がいまちょっとフリーズしてるようなので、もうちょっと時間がかかりそうです。（ーー）やっぱり変更情報を一元管理するよりは情報テーブルと情報詳細テーブルを設けてやったほうがいいような・・・。うーん・・・。どうしよう。ありがとうございます。ちょっと出直します。
| ||||
1
スキルアップ／キャリアアップ（JOB@IT）
Copyright © ITmedia, Inc. All Rights Reserved.
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。
著作権はアイティメディア株式会社またはその記事の筆者に属します。
当サイトに掲載されている記事や画像などの無断転載を禁止します。