スキーマを作る

1998/7/26

スキーマを作る

「従業員データベース」を研究事例として、実際にデータベースを設計する方法と、そのための SQL を解説します。SQL にはデータを検索/追加/削除/更新する(*1)だけでなく、ここで紹介するようにテーブルを作成したり設定したりする(*2)機能も持っています。しかし最近は Access をはじめ、SQL を自分で書かずとも GUI によってこれらの作業を完結できるアプリケーションが提供されています。そのため、こういった SQL は必ずしも覚える必要はないかもしれません。しかし難しいものではないので、いざと言うときに小回りを利かせるためにも、是非覚えておいてください。

(*1) こういった SQL をデータ操作 SQL 言語（SQL-DDL）と言います
(*2) こういった SQL をデータ定義 SQL 言語（SQL-DML）と言います

スキーマとは

RDBMS では、データを入れる前に、まず、データベースの構造を決めなければなりません。これは主にテーブルを作る作業になりますが、その他にも、テーブル間の関係の設定や、効率の良い検索のためのインデクス（索引）の設定など、いくつかの作業があります。

こういった設定、つまりデータを入れるためのうつわのことを、スキーマと言います。

テーブルを設計する

従業員データベース（データベース名：EMPLOYEE）に必要なテーブルを作ります。従業員データベースで管理するデータは、社員番号(id)、部署(section)、氏名(name)とします。

おそらく最初に考えるのは以下のようなテーブル(EmpTbl)でしょう。

EMPLOYEE

EmpTbl

id	section	name
101	開発部	宮前竜也
102	営業部	鈴木陽二朗
103	企画部	畠山めぐみ
104	開発部	菊地直人

正規化

上で考えたテーブルでは、部署を文字列で管理しているため、データ効率が良くないだけでなく、例えば部署名が変更になった場合に、全社員のレコードを矛盾なく修正しなければならないなど、管理上も望ましくありません。そこで以下のように、部署を別のテーブルで管理します。

EMPLOYEE

EmpTbl

id	sec_id	emp_name
101	3	宮前竜也
102	1	鈴木陽二朗
103	2	畠山めぐみ
104	3	菊地直人

SecTbl

id	sec_name
1	営業部
2	企画部
3	開発部

EmpTbl テーブルと、SecTbl テーブルは、それぞれの sec_id フィールドと id フィールドによって関係（リレーション）を持っています。このようにデータを複数のテーブルに分割して持たせ、キーによって結び付けて管理するのが、リレーショナルデータベースの特徴です。

複雑になったようにしか見えないかもしれませんが、管理上はこのほうが効率的で柔軟性が高いことがわかると思います。部署の詳細なデータ管理するように拡張することも簡単にできます。元のテーブル形式のままでは、見にくいだけですが、先に述べたように、SQL を使って自由な形でデータを取り出すことができます。

このように、重複するデータを別のテーブルに追い出すことを、正規化と言います。複雑なデータベースでは、何度か正規化を繰り返し、最適な形にして行きます。

SQL でテーブルを作る

テーブルを作るには CREATE TABLE 文を使用します。構文は以下の通りです。なお、ここでは適当に改行を入れてありますが、改行は自由ですので、必ずしも入れる必要はありません。

CREATE TABLE テーブル名 (
フィールド名 型
[, フィールド名 型] .....
)

今回設計したテーブルを作るには、以下のようにします。

CREATE TABLE EmpTbl (
id          integer,
sec_id      integer,
emp_name    varchar(20)
)

CREATE TABLE SecTbl (
id          integer,
sec_name    varchar(20)
)

型については、SQL/92 などの規格で一応定義されているものの、DBMS 製品によって様々なので、詳しい解説は省きます。ここでは数値型として integer、可変長文字列型として varchar を使用しています。

DBMS によっては、CREATE TABLE 時に各フィールドの設定を指定できます。例えば、デフォルト値や NULL 値（空の値）の禁止、重複の禁止、プライマリキーの設定などです。これらは実装されていない DBMS もあるので、説明は省略します。

インデクスを張る

テーブルができたら、検索速度を上げるためにインデクス（索引）を設定します。インデクスは、なくても動作に支障はありませんが、これを正しく設定することで、場合によっては数十倍〜数百倍も検索速度がアップします。従って、なくて動かないというものではありませんが、これを正しく設定することはスキーマ設計において極めて重要だと言えます。

インデクスとは、紙の本でのインデクス（索引）とまさに同じ目的をもつものです。与えられたキーワードからインデクスを参考にレコード位置を（ハッシュや B 木といったアルゴリズムを使用して）算出し、高速に目的のレコードを取り出します。

インデクスは、テーブルの任意のフィールドに対して設定できます。通常プライマリキーや、検索キーとして使用するフィールドに対して設定します。しかし片っ端から設定すれば良いというものではありません。なぜならインデクスを設定すると検索は速くなりますが、更新、追加についてはインデクスの更新のぶん遅くなるからです。さらに、インデクスはそれなりのディスク容量を消費します。従って無意味なインデクス設定は避けなければなりません。インデクスの設定はパフォーマンスに大きく影響するので、テーブル間のリレーションや、検索についてよく考え設計する必要があります。

インデクスを設定するには CREATE INDEX 文を使用します。構文は以下の通りです。

CREATE INDEX インデクス名
ON テーブル名 (
フィールド名
[, フィールド名] .....
)

今回の従業員データベースでは、プライマリキーである EmpTbl テーブルの id フィールドと、SecTbl テーブルの id フィールドにインデクスを設定します。もし従業員の氏名による検索を頻繁に行い、検索パフォーマンスを上げる必要があるのなら、emp_name フィールドにも設定するとよいでしょう。

CREATE INDEX EmpIdx ON EmpTbl (id)

CREATE INDEX SecIdx ON SecTbl (id)

戻る

スキーマを作る