テーブル設計を後から変更 現場で使える小技のご紹介 株式会社ジーワンシステム 生島 勘富（イクシマ サダヨシ）

Presentation on theme: "テーブル設計を後から変更 現場で使える小技のご紹介 株式会社ジーワンシステム 生島 勘富（イクシマ サダヨシ）"— Presentation transcript:

1 テーブル設計を後から変更 現場で使える小技のご紹介 株式会社ジーワンシステム 生島 勘富（イクシマ サダヨシ）

2 自己紹介 生島勘富(イクシマ サダヨシ)と申します。 実は長い間SQLServerを現場ではメンテでしか触っていません。
基本は受託開発をやっていますが、現在、オリジナルのSaaSを企画中です。 パフォチューなどの仕事があればご紹介ください（笑）。

3 はじめに（今回の概要） データモデリングに理想はありますが、現実は厳しい。
全く新規のシステムは非常に少なく、既存システムに引きずられることがほとんどです。 既存システムに影響を与えずに、理想に近いモデリングをするにはどうしたら良いかというお話です。

4 適応条件 最近多い（？）Big Dataには向きません。
「既存システムが複雑すぎてテーブル構造の変更ができない」というような場合に使います。 例えば、未だに【Filler01】などというフィールドが存在することがありますが、そういうシステムに、サブシステムなどを追加するときに利用すると良いでしょう。 ただし、インデックスなどが倍増する可能性もあります。

5 最初に新規のDBを用意 最初に新規のDBを用意します。 今後、開発するシステムは、新規DBにしか接続しないようにします。
Oracleの場合はスキーマを分ける形でも良いと思います。

6 命名法を検討し直す テーブルの命名法（お勧め） ID_具体名 IDは4桁ぐらいが良いでしょう。
例）M000_Customers 　　M100_Products 　　M110_Products_Info 大分類 中分類 小分類 M マスタ 0～Z T トランザクション W ワーク S システム

7 旧DBにカラムを追加 全面的にサロゲートキーを利用した形に変更します。 まずは、全テーブルに【ID】カラムを追加します。
外部キーに当たるカラムを追加します。 外部キーに当たるカラム名は【テーブルID（_識別子）】とします。 もし、*を使ってINSERTしているものがあれば……、修正しましょう……。

8 旧DBにカラムを追加２ 例）

9 旧DBにトリガーを仕込む http://d.hatena.ne.jp/Sikushima/20111218/1324182140
トリガーを仕込み十分にテストを行ってください。 新ID、新外部キーにインデックスを追加してください。

10 カラムの命名法を検討 カラムの命名法を修正します。
例えば、ないことを祈りますが【Filler01】→ 【Memo01】とすべきかも知れません。 このようなカラム名の問題を、あるべき命名法に変換する一覧表をエクセルなどで作成します。

11 新DBにビューを作ります。 カラム変換表から新DBにビューを作ります。
CREATE VIEW [新テーブル名] AS 　SELECT 　　　[旧カラム名] AS [新カラム名] 　…… 　FROM [旧DB].[dbo].[旧テーブル名] 以降は新DBで新しい名前で開発が可能になります。 新DBで開発するときは、FROM句ではサロゲートキーで、WHERE句ではナチュラルキーか、サロゲートキーを利用します。

12 正規化のやり直し データを横に持っているなどの問題も、現実には多い問題です。
この場合はVIEWではなく、複数に分けた実テーブルを命名法に応じて作ります。 更に、旧システム（旧DB）でデータの更新があった際、新システム（新DB）に自動更新されるようにトリガーを作成します。 新システムでも更新が必要な場合は、連鎖・ロックに十分に配慮しながらトリガーを作成します。 ※ ディレイ更新も検討

13 旧DBの破棄 サブシステムを追加するにあたり、このようにDBのデータ構造を修正しておけば、次の大規模なリプレースのときに簡単にデータ移行ができます。 SELECT * INTO [新サーバ].[新新DB].[dbo].[新テーブル] FROM [新テーブル] など （ここまで横着をする必要はありませんが……） インデックスとトリガーは手作業で移行する必要があります。

14 まとめ 未だに【Filler01】などというカラムが残っているということは、20年近く前の設計思想を引き継いでいるシステムがあるということです。 その状態で、モデリングもヘッタクレもない。 20年近く前の遺物が残っているということは、手が付けられない状態であったのでしょうが、やりようによっては、既存システムを残したままで、サロゲートキーの適応まで可能です。 現実には、これすらもコストが許されないことが多いのですけれど、チャレンジする価値は高いです。

15 おまけ 弊社では昔から、漢字のテーブル（ビュー）名、カラム名に直したビューを顧客用に提供することが多いです。
更新権限を与えない場合もありますし、更新権限を与えて、マスターメンテ画面を作らない場合もあります。 また、【M000_Customers】というテーブル名に対し、【M000】というシノニムを設定することもあります。 工数を削減する効果は結構ありますので、規約が許せばご利用ください。

16 ご清聴ありがとうございました。 株式会社ジーワンシステム 　　　　　　生島　勘富