2014-08-29

データベースのテーブルの列名の設計の方針について

　データベースの列名を考えるときは、いつも迷う。こちらがたてば、あちらがたたずという状況が多い。これが正しいという確信にはいたっていませんが、こんな感じで作成すると、まぁいいんじゃないか的な方針を記述してみます。

行を識別するキーと業務用の主キーのふたつを作成する

　まず本というテーブルを考えます。最初の列は行を識別するためのキーです。次の列は、本を識別するためのキーです。これが業務用の主キーです。また本はタイトルを持つことにします。

　サンプルはMySQLで書きます。

create table book (
  row_id int primary_key auto_increment,
  id varchar(8) not null unique,
  title varchar(30) not null,
);

　注目する点は以下の点です。

行IDには、プリマリー制約をつけて、オートインクリメントする
本のIDには、not null制約とユニーク制約をつける

　実際の行は次のようになります。アンダーバーを引いてあるのが業務上の主キーです。

row_id	id	title
1	00000001	Perl
2	00000002	Ruby
3	00000003	Python

　ここでなぜ、行を識別するキーが必要なのか、疑問に持たれる方もいるでしょう。なぜなら、業務上の主キーである本のIDで、行を一意に識別できるからです。もう少し考えていきましょう。

外部キーを持つ場合

　データベースのテーブルが、外部キーを持つとしましょう。bookテーブルに著者IDを追加します。

create table book (
  row_id int primary_key auto_increment,
  id varchar(8) not null unique,
  title varchar(30) not null,
  author_id varchar(8) not null
);

　著者テーブルを作成します。

create table author (
  row_id int primary_key auto_increment,
  id varchar(8) not null unique,
  name varchar(30) not null
);

　bookテーブルとauthorテーブルは次のようなデータになっています。

book

row_id	id	title	author_id
1	00000001	Perl	00000001
2	00000002	Ruby	00000001
3	00000003	Python	00000002

author

row_id	id	name
1	00000001	木本
2	00000002	田中

　ここまではまだ、行IDが存在しなくても何の問題もありません。行を一意に識別できているからです。次が問題です。

外部キーが複合主キーを持つテーブルを指す場合

　たとえば、会社IDと担当者IDを複合主キーとする担当者テーブルがあったとします。staffテーブルとしましょう。

create table staff (
  row_id int primary_key auto_increment,
  company_id varchar(8) not null
  id varchar(8) not null,
  name varchar(30) not nulltitle,
  unique(company_id, id)
);

　データは次のような感じです。company_idとidが業務上の主キーです。

row_id	company_id	id	name
1	00000001	suzuki	鈴木
2	00000002	yamada	山田

　ここでbookテーブルが、担当を持つ必要があるとします。さてどのように列を作成したらよいでしょうか。外部キーには行を一意に識別できる値を指定しなければなりません。

　すると次のようになります。

create table book (
  row_id int primary_key auto_increment,
  id varchar(8) not null unique,
  title varchar(30) not null,
  author_id varchar(8) not null,
  staff_row_id int not null
);

bookテーブル

　データは次のようになります。

row_id	id	title	author_id	staff_row_id
1	00000001	Perl	00000001	1
2	00000002	Ruby	00000001	2
3	00000003	Python	00000002	2

　外部キーに、行を識別するキーを使えば、複合主キーを持つテーブルの行を、一意に識別できます。

行を識別するキーの欠点

　行を識別するキーの欠点は、自動的にインクリメントする値なので、開発環境と本番環境など、異なる環境ではその値が変わってきます。また、行の識別はできますが、値が意味をもたないので、参照先のテーブルを見ないと、実際に何を指しているのかがわかりません。

　すべての外部キーを行を識別するキーにしてしまうと、データベースのテーブルが見にくくなるので、業務上の主キーがひとつの場合は、外部キーとして、業務上の主キーを使うようにしています。

　けれども、外部キーが複合主キーを持つテーブルを指す場合は、行を一意に識別するためには、行IDを利用するのが一番よいと思います。これをやっておかないと、joinをするときのパフォーマンスが落ちますし、SQLのjoin区の作成が非常にめんどうになります。

まとめ

　まとめると、業務上の主キーはわかりやすくて、使いやすいが、外部キーが、複合主キーを持つテーブルを指す場合は、行を一意に識別するために、行を識別するキーを使うのがよい。

Permalink | コメント(0) | 15:06

PerlならサンプルコードPerl入門