テーブルの構成要素
データベースのテーブルがどのような要素から構成されているかを学習する。
テーブルとエンティティ
エンティティ = テーブルと考えてほとんと差し支えない。
成績管理アプリを作る場合を感えると生徒、科目、成績といったエンティティが存在する。
データベースにはそれらのエンティティに対応した生徒テーブル、科目テーブル、成績テーブルを作成することになる。
テーブルの行と列
テーブルは名前の通り表の形式で構成されている。テーブルの行はレコード、列はカラムを言うがそれぞれ表している意味が異なる。
- テーブルの行(レコード)はエンティティの具体的なデータを表す
- テーブルの列(カラム)はエンティティの属性を表す
テーブルの行(レコード)
レコードとはエンティティの具体的なデータである。例で以下のような生徒テーブルのレコードを考えてみる。
| id | name | |
|---|---|---|
| 1 | 山田太郎 | taro@example.com |
| 2 | 鈴木次郎 | jiro@example.com |
idが1である1行目は山田太郎さんという生徒のデータを管理している。idが2である2行目は鈴木次郎さんという生徒のデータである。
このようにレコーそはそのテーブルの表す具体的なデータ(山田太郎さん、鈴木次郎さん)を表している。
テーブルの列(カラム)
カラムとはエンティティの属性である。上記の表の例だと生徒テーブルにはid, name, email (それぞれ識別子、名前、メールアドレスの意味)という3つの属性を持っているということになる。
テーブル同士の関連性
エンティティ間には関係性のある場合がある。「エンティティ = テーブル」と考えて良いので、テーブル同士にも関係性がある場合がある。この関係性がリレーションにあたる。
例えば、生徒を成績の間には関係性がある。生徒は必ず成績を持っており、成績も必ず生徒に紐づいている（Aさんは70点、Bさんは90点など)。このような場合、生徒テーブルを成績テーブルの間にはリレーションがある。
データを識別するための特殊な属性値
属性の中にはキーと呼ばれる特殊なデータが存在する。キーは同じテーブルのレコード同士を識別するためのデータである。多くの場合、idをいう名前のつく属性がキーとなる。
キーの役割
エンティティの属性であるカラムの中にはキーと呼ばれる特殊なデータが存在する。キーの役割はレコードを識別することである。
● キー
テーブルにおけるキーとはレコードを識別するための特別なカラムのことを指す。キーは識別子であるので同じテーブル内の他のレコードとは絶対に被らないように設定する。
キーの種類
キーには以下の2種類がある。
- 主キー
- 外部キー
主キー
主キーはあるテーブルのなかで他のレコードとの区別をつける識別子となるカラムである。そのため、同じ主キーの値を持つレコードがテーブル内に存在してはならない。
以下の生徒テーブルのidカラムが主キーになる。この時、鈴木次郎さんのレコードのidが1であってはならない。
| id | name | |
|---|---|---|
| 1 | 山田太郎 | taro@example.com |
| 2 | 鈴木次郎 | jiro@example.com |
外部キー
外部キーは関連する他のテーブルのレコードの主キーを値として持つカラムである。外部キーは他のテーブルのレコードとの関係性を表すために用いる。
主キーの説明であげた生徒テーブルには2名の生徒がいる。主キーとなるカラムはidであったが。。。
| id | name | |
|---|---|---|
| 1 | 山田太郎 | taro@example.com |
| 2 | 鈴木次郎 | jiro@example.com |
生徒テーブルと関係性を持つテーブルとして成績テーブルがあると仮定する。成績テーブルにはそれぞれの生徒に対応する成績が保存されている。
| id | score | student_id |
|---|---|---|
| 1 | 70 | 2 |
| 2 | 90 | 1 |
成績テーブルのidは主キーです。その他にstudent_idという属性が存在する。成績テーブルでは、このstudent_idは外部キーに当たる。これはその成績をとった生徒のレコードの主キーと対応している。
つまり成績テーブルのidが1であるレコードは生徒テーブルのidが2であるレコードと対応しており、このことから 「鈴木次郎さんは70点である」 ことが分かる。
制約で安全なテーブルを設計する
テーブルのカラムに対して制約をかけることで不正なデータや予期せぬデータが保存されることを防ぐことができる。
制約とは
制約とは特定のデータの保存を許さないためのバリデーションである。例えば同じメールアドレスのユーザーを登録できないようにする、名前のデータが空のユーザーを保存できないようにするといったことができるようになる。
制約の種類
- NOT NULL制約
- 一異性制約
- 主キー制約
- 外部キー制約
この４つの制約の挙動を具体的に確認するために実際に実装してみることにする。そこで学習するためのサンプルアプリを作成する。
● 以下の手順で 「DataBaseDesignSample」という名前のRailsアプリケーションを作成する
1. アプリケーションの作成以下のコマンドを順々に実行する。
$ cd #ホームディレクトリに移動
$ rails _5.0.7.2_ new DataBaseDesignSample -d mysql #mysqlでRailsアプリケーションを作成
$ cd DataBaseDesignSample
$ bundle exec rake db:create #DBの作成
2. userモデルを作成
$ rails g model user
ここから４つの制約を説明しつつ実際に実装してみる。
NOT NULL制約
NOT NULL制約はカラムに設定する制約である。 NOT NULL制約を設定すると、そのカラムの値にはNULL (空の値) を入れることができなくなる。絶対に値があるカラムに対して使う制約である。
● NOT NULL制約
NOT NULL制約はテーブルの属性値にNULL (空の値) が入ることを許さない制約である。例えば、 usersテーブルのnameというカラムに NOT NULL制約を設定すると、 nameが空(nil)レコードは保存できなくなる。
実際にNOT NULL制約の挙動を確認してみる。
usersテーブルにNOT NULL制約を付けたnameカラムを作成する
Railsでは、マイグレーションファイルでカラムを追加するときにnull: falseと記述することでNOT NULL制約を設定することができる。
class CreateUsers < ActiveRecord::Migration
def change
create_table :users do |t|
t.string :name, null: false
t.timestamps null: false
end
end
end
記述ができたらターミナルでマイグレーションを実行
$ bundle exec rake db:migrate
マイグレーションが実行されてusersテーブルが作成されたら、rails cを使い実際の挙動を確認してみる。
$ rails c
irb(main):001:0> User.create(name: "keita") //=> ユーザーが作成される
irb(main):002:0> User.create(name: nil) //=> エラー
このようにNOT NULL制約が設定されたカラムがnilであるとエラーが発生する。
一意性制約
一意性制約はカラムに設定する制約である。一意性とはユニークで他とは違う意味である。一意性制約を設定したカラムには同じ値をできなくなる。例にあげるとAさんのemailが「test@gmail.com」だった場合、他にemailが「test@gmail.com」のレコードを保存できなくなる。
● 一意性制約
一意性制約はテーブル内で重複するデータを禁止する制約である。
emailカラムに対して一意性制約を設定すると同じemailのレコードは保存できなくなる。
実際に一意性制約の挙動を確認してみる
usersテーブルに一意性制約を付けたemailカラムを作成する
emailカラムを作成するためのマイグレーションファイルを作成する。
$ rails g migration AddEmailToUsers email:string
Railsでは、カラムに対してadd_indexメソッドを用いることで一意性制約を付けることができる。
一異性制約
add_index :テーブル名, :カラム名, unique: true
生成されたマイグレーションファイルを以下のように編集してemailカラムに一異性制約を設定する。
class AddEmailToUsers < ActiveRecord::Migration
def change
add_column :users, :email, :string
add_index :users, :email, unique: true
end
end
記述したらターミナルでマイグレーションを実行
$ bundle exec rake db:migrate
実際の挙動をrails cで確認してみる。
$ rails c
irb(main):001:0> User.create(name: "taro", email: "taro@yamada.com") //=> ユーザーが作成される
irb(main):002:0> User.create(name: "yamada", email: "taro@yamada.com") //=> エラー
２回目のUser.createでエラーが起きる。これは１回目のUser.createと2回目のUser.createで同じemailでユーザーを作成していることで、一異性制約に引っかかってしまったためである。
このように一異性制約を設定したカラムの値は、唯一の値でなくてはいけない。
主キー制約
主キー制約とは、レコードが必ず主キーを持っていなくてはいけないことを保証するための制約である。
●主キー制約
主キー制約は、主キーである属性値が必ず存在してかつ重複していないことを保証する制約である。主キーに対してNOT NULL制約と一意性制約を両方設定するのと同義になる。
Railsでテーブルを作成する際、主キー制約は元々実装されている。Railsでは主キーはidカラムとして自動で作成される。つまり、idカラムの値は重複しないようにできている。
外部キー制約
外部キー制約とは、外部キー制約とは、外部キーに対応するレコードが必ず存在することを保証する制約である。例えばstudent_idが3のレコードを保存するためにはstudentsテーブルにidが3のレコードが存在してなくてはならない。
● 外部キー制約
外部キー制約は、外部キーの対応するレコードが必ず存在しなくてはいけないという制約である。外部キーのカラムに値があっても、その値を主キーとして持つ他のテーブルのレコードがなければいけない。
実際に外部キー制約の挙動を確認してみる。
外部キー制約を実装してみる
usersテーブルの外部キーを持つためのscoreテーブルを作成する。このscoresテーブルはユーザーの成績を保存するためのテーブルである。そのため、scoresテーブルのレコードはuser_idという外部キーのカラムを持ち、どのユーザーの得点なのかがわかるようにする。
$ rails g model score
Railsでは、マイグレーションファイルで外部キーとなるカラムを追加するときにforeign_key: trueと記述することで外部キー制約を設定することができる。
では、生成されたマイグレーションファイルを以下のように編集してuserとのアソシエーションに外部キー制約を設定する。
class CreateScores < ActiveRecord::Migration
def change
create_table :scores do |t|
t.string :name
t.integer :score
t.references :user, foreign_key: true
t.timestamps null: false
end
end
end
記述ができたらターミナルでマイグレーションを実行する。
$ bundle exec rake db:migrate
マイグレーションを実行するとscoreテーブルにはuser_idというカラムが作成されている。このuser_idカラムは外部キーであり、外部キー制約が設定されている。
rails cで挙動を確認してみよう。usersテーブルが以下のような状態と仮定して説明する。
| id | name | |
|---|---|---|
| 1 | 山田太郎 | taro@example.com |
| 2 | 鈴木次郎 | jiro@example.com |
$ rails c
irb(main):001:0> Score.create(name: "English", score: 80, user_id: 2) //レコードが生成される
irb(main):002:0> Score.create(name: "Math", score: 90, user_id: 4) //エラー
3行目では、 user_idに4を指定している。しかし、 usersテーブルにはidが4のユーザーは存在しませんから外部キー制約によってエラーが発生する。このように外部キー制約は関連先のテーブルに存在する主キーのみしか外部キーに指定することができない。
インデックスでデータの検索を高速化する
サービスでよく起きるテーブル操作の中でレコードの検索がある。例えばusersテーブル内で検索が頻繁に行われるカラムにインデックスを設定することで検索の高速化を図ることができる。
インデックスとは
インデックスはデータベースの機能の一つで、テーブル内のデータ検索を高速化することができる。インデックスはカラムに対して設定することができ、設定したカラムでの検索が高速になる。
※ インデックスを設定することを、「インデックスを貼る」と言う。
● インデックス
インデックスとはテーブル内のデータの検索を高速にするための仕組みである。インデックスはカラムに対して設定する。インデックスをカラムに設定するとそのカラムで検索をした場合に検索速度が向上する。
インデックスのデメリット
インデックスで速度が上がるからといってすべてのカラムにインデックスを設定してはならない。インデックスには以下の２つのデメリットがある。
- データを保存・更新する速度が遅くなる
- データベースの容量を使う
データを保存・更新する速度が遅くなる
データを保存する際に、設定されているインデックスの数だけ追加でデータを作成する。インデックスを設定するカラムが増えるだけ保存するデータが増え、処理の速度が遅くなる。
データベースの容量を使う
インデックスはそのカラムで検索しやすいための特別なデータを保存するために検索速度が向上する仕組みです。そのため、インデックスを多く設定すればその分、データが必要になり容量が圧迫される。
１つのカラムに対するインデックス
テーブル内の１つのカラムにインデックスを貼る場合は、そのカラムで検索した場合に検索速度が向上する。
インデックスはmigrationファイル内で以下のように記述することで設定することができる。
class AddIndexToテーブル名 < ActiveRecord::Migration
def change
add_index :テーブル名、 :カラム名
end
end
１つのカラムに対するインデックスを設定してみる
DataBaseDesignSampleアプリケーションを使ってインデックスを実践してみる。 scoreテーブルに対してインデックスを貼るためのマイグレーションファイルを作成する。
$ rails g migration AddIndexToScores
記述したらターミナルでマイグレーションを実行する。問題なく実行できたらscoresテーブルのnameカラムに対してインデックスが設定できている。
以下のような検索の場合、検索速度が向上する。
__nameカラムによる検索
Score.where(name: '山田太郎')
複数のカラムに対するインデックス
インデックスは１つのカラムだけではなく、複数のカラムにも設定ができる。例えば、ユーザーを姓と名で検索するシステムを作っていることを想定しよう。SQLは以下のようになる。
姓と名によるユーザー検索
SELECT *
FROM users
WHERE family_name = '山田' AND first_name = '太郎'
このように検索時に２つのカラムを使う場合が多い時に複数カラムに対してインデックスを設定する。
複数のカラムにインデックスを設定するためには、migrationファイル内で以下のように記述する。
class AddIndexToテーブル名 < ActiveRecord::Migration
def change
add_index :テーブル名, [:カラム名, :カラム名]
end
end
複数のカラムに対するインデックスを設定してみる
DataBaseDesignSampleアプリケーションを使ってインデックスの設定を実践してみる。usersテーブルに対してインデックスを貼るためにマイグレーションファイルを作成する。
$ rails g migration AddIndexToUsers
作成したマイグレーションファイルを編集してnameカラムとemailカラムの２つに対してインデックスを貼ることにする。
class AddIndexToUsers
def change
add_index :users, [:name, :email]
end
end
記述ができたらターミナルでマイグレーションを実行する。問題なく実行できたらusersテーブルのnameカラムをemailカラムの２つで検索する場合に対するインデックスが設定できている。
以下のような検索の場合、検索速度が向上する。
nameカラムによる検索
User.where(name: '山田太郎', email: 'taro@mail.com')
※この方法でインデックスを貼るとき、emailカラム単体で検索する場合には検索速度は向上しないので注意すること。
まとめ
- エンティティをテーブルとして定義する
- エンティティの持つ属性をカラムとして定義する
- カラムには主キーを必ず持たせる
- 他のテーブルのレコードと関連がある場合、外部キーという形で他のテーブルとの関係を保存する
- カラムの値には制約をつけてデータの正しさを保証する
- 値が必ず設定されていることを保証する時にはNOT NULL制約を用いる
- 値に重複がないように設定するには一意性制約を用いる
- キーの存在を保証する時には主キー制約、外部キー制約を用いる
- 検索する際に使うカラムにはインデックスを設定する