はじめに
RDBを中核とする事務系システムでは、RDBの論理設計がシステムの「品質」「開発・保守・運用時の生産性」を大きく左右する。RDBの論理設計というとモデリングが重視されるが、モデリング以外にも重要なポイントがある。RDBの論理設計において、モデリング以外で注意すべきポイントを何回かに分けて説明してみたい。
1回目はRDBでファイル（PDFや画像等のファイル）をどのように扱うべきか？を記してみたい。最初に断っておくが、ファイルというと「BLOB列でファイル内容を管理すべきか？」という議論があるが、この点については言及しない。
ファイルは属性を管理するテーブル、属性以外の情報を管理するテーブルの2種類で管理する。
属性を管理するテーブル
- 属性を管理するテーブルは、次のようなイメージで定義する。
| カ ラ ム | データ型 | 属 性 | 内容 |
|---|---|---|---|
| ID | Number(8) | 主キー | サロゲートキー |
| SHA-1 | Char(40) | Not Null | SHA-1ハッシュ値（16進数） |
| MD5 | Char(32) | Not Null | MD5ハッシュ値（16進数） |
| サイズ | Number(10) | Not Null | ファイルサイズ(バイト数) |
| リンク数 | Number(8) | Not Null | 「属性以外の情報を管理するテーブル」の登録件数 |
| MIME | Varchar(255) | Not Null | 登録時にサーバー側で取得したMIME。 ※ブラウザからファイルアップロード時、ブラウザからもMIMEは送信されてくるが、ブラウザから送信されてきたMIMEではなく、サーバー側でファイル内容をチェックしてMIMEを取得する。 |
| 拡張子 | Varchar(10) | Not Null | MIMEから推測される拡張子 |
| ファイル概要 | Number(2) | Not Null | ファイルの概要。例えば 1:テキストファイル 2:PDFやエクセル等の事務系ファイル 3:画像ファイル 4:音声ファイル 5:動画ファイル 11:プログラム（ソースやスクリプト等のテキストファイル） 12:プログラム（exeやdll等のバイナリファイル） 99:アーカイブファイル 等のように使用 |
| ファイル | BLOB | Not Null | ファイル内容をBLOBで管理する場合のみ定義する。 |
| ： (様々な属性) |
： (属性に応じたデータ型) |
システムで扱うファイルの種類に応じた属性情報。 例えば画像を扱うのであれば解像度、 音声を扱うのであれば再生時間等を必要に応じて定義。 |
- SHA-1、MD5、サイズの3列で複合ユニークキーを定義する。
- 何らかのファイルを登録しようとする際、登録しようとするファイルのSHA-1、MD5、サイズの組み合わせで「属性を管理するテーブル」にレコードが登録されているかどうかをチェックする。存在しない場合は「属性を管理するテーブル」にInsertし、存在する場合はUpdate（リンク数のインクリメント）を行う。このことにより内容が同じファイルの重複登録を防ぐ。
- 「拡張子」「ファイル概要」は「MIME」から特定できる情報であるため、正規化の観点からは不要な情報ではあるが、おそらく定義したほうが使い勝手が良いと思われるため、定義している。
- ファイルをサーバー上のファイル名で管理する場合、ファイル名は「ID.拡張子」とする。保存ディレクトリはDBで管理すべきではない。（特定ディレクトリにファイルが集中することを避けて分散したい場合、例えばSHA-1の先頭1桁でサブディレクトリを作成し、その配下に保存する等の方式を検討する。）
属性以外の情報を管理するテーブル
- 属性以外の情報を管理するテーブルは、次のようなイメージで定義する。
| カ ラ ム | データ型 | 属 性 | 内容 |
|---|---|---|---|
| 業務系要件に応じた主キー | それに応じたデータ型 | 主キー | 以下の例ではサロゲートキーとしている |
| ID | Number(8) | 外部キー | 属性を管理するテーブルのID。 属性を管理するテーブルを親テーブルとする外部キーを定義する。 |
| 論理ファイル名 | Varchar(255) | Not Null | 利用者が目にするファイル名 |
| ： (要件に応じた様々な情報) |
： (情報に応じたデータ型) |
業務系要件に応じた様々な情報。 例えば「ファイルのコメント情報」「ファイルの著作権情報」等を必要に応じて定義。 |
実装イメージ
これらのテーブルを用いた実装は次のイメージで行う。例として情報処理推進機構(IPA)が公開している「安全なウェブサイトの作り方」というPDFを、Aさんが「安全なウェブサイトの作り方.pdf」という名前で登録した後に、Bさんが「安全なウェブサイトの作り方(改訂第７版).pdf」という名前で登録したと仮定し、どのように処理するかを見てみる。
Aさんが「安全なウェブサイトの作り方.pdf」を登録した直後の「属性を管理するテーブル」主要項目（Insertされる）
| カラム | 設定内容 |
|---|---|
| ID | 51 |
| SHA-1 | 7593c9741f96e5ee5c88f68abfdf8d0e68493d37 |
| MD5 | 4cf49d7afe124c7e8e160a18e9453eff |
| サイズ | 3855672 |
| リンク数 | 1 |
| MIME | application/pdf; charset=binary |
| 拡張子 |
- Aさんが「安全なウェブサイトの作り方.pdf」を登録した直後の「属性以外の情報を管理するテーブル」主要項目（Insertされる）
| カラム | 設定内容 |
|---|---|
| 業務系要件に応じた主キー | 200 |
| ID | 51 |
| 論理ファイル名 | 安全なウェブサイトの作り方.pdf |
- Bさんが「安全なウェブサイトの作り方.pdf」を登録した直後の「属性を管理するテーブル」主要項目（Updateされる。リンク数をインクリメントするだけ。）
| カラム | 設定内容 |
|---|---|
| ID | 51 |
| リンク数 | 2 |
- Bさんが「安全なウェブサイトの作り方.pdf」を登録した直後の「属性以外の情報を管理するテーブル」主要項目（Insertされる）
| カラム | 設定内容 |
|---|---|
| 業務系要件に応じた主キー | 201 |
| ID | 51 |
| 論理ファイル名 | 安全なウェブサイトの作り方(改訂第７版).pdf |
最後に
この方式はハッシュの理解を前提に内容が同じファイルの重複登録を防ぐことを最大の目標としたものである。
- ハッシュ値とは文字列やファイル等の「指紋やDNA」に相当する情報である。
- ハッシュ値は固定長となる。（SHA-1は16進数で40桁、MD5は16進数で32桁の固定長となる。）
- まれに衝突することがある。例にあげたPDFのSHA-1は 7593c9741f96e5ee5c88f68abfdf8d0e68493d37 であるが、これ以外のファイル（PDFとは限らない）であっても、同じ値となる可能性がある。但し、仮にSHA-1が別ファイルと同じ値になっても、MD5とファイルサイズも同じ値になる可能性は無い、というのが、この方式の大前提である。
- ハッシュにはSHA-1、MD5以外の高精度な方式（衝突する可能性がより低い方式）も存在するが、SHA-1、MD5を選択したのは「代表的な方式であり、例えばPHPではライブラリの追加インストール等を行わずにsha1_file()やmd5_file()で使用できる」からである。（ちなみにPHPではhash_algos()でプラットフォーム上で使用可能なハッシュ方式が確認できる。）
- ハッシュの説明を行ったので参考までに。一般的なセキュリティ環境下でネット上からダウンロードしたエクセル等のオフィス文書をエクセル等で開くと、保護モード（閲覧のみで編集が行えないモード）で表示されるが、編集を有効にする（保護モードを無効にする）と、ダウンロードしたエクセル等のオフィス文書のプロパティ情報が更新され、ハッシュ値も保護モード時とは異なるものになる。
- ついでにもう一つ。パスワードの暗号化においてもハッシュは良く用いられるが、この場合「暗号化するパスワード」に「何らかの文字列（ソルトと呼ばれる）」を連結して暗号化することが望まれる。ソルトも固定文字列ではなく、ユーザーごとに異なり、初期登録後に変更されない情報（ユーザーIDや生年月日、入会年月日等）をもとに生成する方式が望ましい。（現在はSHA-1よりも推奨される方式があるので、プラットフォーム上で使用可能なハッシュ方式のなかで最善の方式を選択すべきなのは言うまでもない。）
以上最後まで目を通して頂き、ありがとうございました。平成の終わりに10連休という長期休暇が得られたため、Qiita デビューに挑戦しました。