読者です 読者をやめる 読者になる 読者になる

MERYでのお買いものを支えるバックエンドシステム

こんにちは。開発部の平山( @orangevtr) です。今回は私が担当しているMERY ECのバックエンドシステム開発について紹介したいと思います。

MERY ECのバックエンドシステム

f:id:orangevtr:20160803155419j:plain:w720

通常ECサービスを構築する場合、パッケージであったりASPのサービスを利用する機会も多いと思いますが、MERYではフロントエンドシステムのみならずバックエンドシステムも自社で開発しています。

何で自社開発するの?

MERYは当初メディアとしての機能が強く、ショッピングは後発の機能でしたが、一貫して世界観(ブランディング)とユーザー体験を大切にしたいという方針が強くあります。

そのため、メディアとショッピングの機能についてもサービス上の境界線をあえて明確にせず、極力シームレスな体験をユーザーに提供したい、という意図がありました。

また、ショッピングについては単純にユーザーが購入するという体験を提供するだけではなく、ユーザーニーズが強い商品を揃え、メディアとして提案したり、購入後も配送した商品がユーザーの手元にきちんと届き、カスタマーサポートもきっちり行ったりすることで、初めてユーザーに一貫した体験を提供できると考えています。

そういった考えから、バックエンドシステムもパッケージ製品やASPを使わず、自社で開発する意思決定をしています。*1

機能スコープ

MERY ECのバックエンドシステムは、業務全体を考慮すると、以下のような多様なステークホルダーが関与しています。

f:id:orangevtr:20160929190430p:plain:w720

社内

  • マーチャンダイザー(メーカー商品の仕入から販売まで責任を持つ営業担当)
  • カスタマーサポート
  • ストアマネージャー(MERYのECサイトの運用担当者)
  • 分析・ビジネス企画

社外

  • エンドユーザー
  • ブランドメーカー
  • 倉庫業
  • 配送業者

まず取扱商品のマスターを管理する機能があります。MERY ECでは主にアパレル、アクセサリーやバッグなどのファッション商品を取り扱っています。同一商品であってもサイズや色が異なるため、その各パターンについてデータ管理する必要があります。また、商品によって販売形式が買取販売と委託販売と異なってくるので、その点についても考慮して管理します。

次に商品の仕入管理機能があります。MERYではファッションメディアとしていち早く流行を発信するため、展示会などで発表した直後から販売開始したりすることがあります。その一方で、実際の仕入までにはリードタイムが長い場合もあるので、納入してスムーズにユーザーにお届けするには仕入納期を適切に管理することが必要です。仕入れた商品は倉庫に入荷されることになります。

合わせて商品ごとに在庫の管理も必要です。前述の通り、実際に商品が倉庫に存在する前から販売するため、実在庫(商品が実際に倉庫にある数)とは別に受注在庫(受注可能な在庫数)をそれぞれ管理しています。

ユーザーが注文した際の注文データも管理されます。個々の注文にはステータスが付与されており、支払い確認(クレジットカードなどの与信確保)状態であったり、在庫引当で出荷待ちなどのステータスがあります。出荷前のキャンセルや、出荷後の返品・返金管理も合わせて行われます。

注文に在庫が引き当てられた後、倉庫に出荷指示を行うことで出荷が行われます。倉庫は商品を出荷すると、配送業者から配送データが付与されるので、それを以って出荷確定とし、売上計上とユーザーへの出荷連絡を行っています。

この出荷指示から出荷の業務は、必ずしも購入ユーザーだけではなく、受託販売商品やリコール対象などの商品をメーカーにお返しする際なども同様な処理が行われます。そのため、より汎用的な設計が必要になります。

その他にも月次の経理処理のため出荷高や、在庫高の帳票を適宜作成して出力する機能、在庫消化率やファネル分析、LTVの算出などのための分析を行うために分析システムと連携したりする機能があります。

以前のエントリで触れたクーポンを発行したり、対象商品などを管理する機能も備えています。

tech.pero.li

ECバックエンド開発の特徴

業務系管理システムの経験という意味では、これまでも広告配信の管理システムやSNSサイトの管理システムなどを開発してきた経験はありましたが、これらと比較してもECバックエンドシステム開発には特徴的な点がいくつかあります。

特徴

これまでの業務系管理システムの開発の場合、例えば広告配信のシステムであれば、

  • バナー管理
  • 広告配信スケジュール管理
  • 広告配信レポート出力

などの機能を個々に開発していました。他の業務管理システムの類も同様なのですが、ECバックエンドシステムについては、より業務フローに密着している面が強いです。おそらく業務フローを全く知らない人が前述の広告管理システムを見ても個々の機能の想像はつきますが、ECバックエンドシステムの場合は業務フローを理解していないで機能だけを理解することはできないでしょう。

また、前者のような管理システムではデジタルデータしか伴いませんが、ECバックエンドシステムでは実際の商品を伴うため、商品が現在どこにどういう状態で存在して、そして今後どう扱われていくかを確実に遷移させていく必要があります。

自社開発における注力ポイント

ECバックエンドシステム自体は典型的なシステムですが、自社開発という条件を活かしたシステム開発を進めるにあたり、以下のような点を考慮しています。

スピードとクオリティのバランス

当然決済データや商品の取扱いにはある程度の品質が求められますし、配送先などの個人情報も流通します。ですが、サービスのローンチから拡大にあたっては、ビジネス開発担当者とコミュニケーションを重ね、必要十分な要件の機能をスピード感を持って提供していく必要があります。

業務フローとの適合

MERYのショッピング機能は、サービス開発としてはまだ途上です。また、ベンチャーならでは、必ずしもスタッフが全員ECバックエンドシステムのユーザーとしてプロフェッショナルではありません。

そのため、適宜最適な業務フローを設計しつつ、システム開発を進めることが重要です。

ユーザー体験への直結

さらに、ユーザーにとっては実際に商品を手にして使用する、ところまでが体験なので、それらを直接システム面からサポートすることが自社開発のメリットだと考えています。具体的には、仕入管理機能を実際のメーカーとの発注に合わせて適切に納期管理することで配送までの遅れ率を減らしたり、キャンセルや返品について独自のデータ分析を行うことが可能になります。

さいごに

今回はMERY ECのバックエンドシステムについてご紹介しました。

ECに限らず、バックエンドシステムはユーザーが直接体験するアプリやWebフロントエンドの開発や、サービス自身のサーバーサイド開発などのような花形の開発に比べて、縁の下の力持ち的な役割は否めません。ですが、前述の通りステークホルダーが多く、特に今回のECバックエンドシステムでは社内だけでもマーチャンダイザー、カスタマーサポート担当、ストアマネージャーなど多様なユーザーがいます。言うなれば非常に近いところにユーザーがいるわけで、業務効率化したり、課題解決していく場面をより近いところで体験できる貴重な場ともいえます。

幸い、MERYのショッピング部門も徐々に規模拡大してきており、これまでの業務フローでは回りきらずに新たに業務フローを設計し直して開発を進めたりすることや、対象のステークホルダーを広げることで業務全体を効率化する必要が出てきており、ビジネス上非常に重要な位置を占めてきています。

ペロリではこういった業務向けシステム開発の経験あるエンジニアもまだまだ不足しています。ぜひご応募お待ちしています!

*1:合わせて、親会社のDeNAにEC部門があり、ビジネス・システム開発ともにノウハウを活用できるという面も当然大きいです

Terraformの抽象度を高くする

Terraform Infrastructure Infrastructure as Code

こんにちは。SKAhackです。

今回はTerraformファイルの抽象度を高くすると嬉しいことを、Segmentが公開しているモジュールを例に紹介してみます。

まずはこちらを見てみてください。

// stackモジュールは基盤となる部分を定義
module "stack" {
  source      = "github.com/segmentio/stack"
  name        = "peroli-service"
  environment = "prod"
  key_name    = "bastion-ssh"
}

このTerraformファイルを定義して terraform plan をすると、VPC, Security Group, IAMロール, DNS, ログの保存場所としてS3, sshするための踏み台サーバーなど基盤に関わる部分全てが出来ることが分かります。この例はsegmentio/stackとしてSegmentが公開しているもので、実際にどのような用途で使われるかは後で少し触れます。

このように抽象度を高めると、サービスを提供するために必要なものだけ定義することができ、内部の設定を気にする必要がなくなります。 また、モジュールにオプションを用意しておくことである程度柔軟に利用できます。

Terraform module

Terraform moduleは、コンポーネントを組み合わせて再利用可能にするための仕組みです。 実際に便利なモジュールがterraform-community-modulesとして公開されていたりします。

このようにTerraformには便利な仕組みがあるのですが、個人的な観測範囲では便利に使われているところを見たことがありませんでした。 インターネット上に公開されているモジュールも、先程挙げたterraform-community-modulesのようにresourceをラップするようなモジュールが多い印象で、 そのくらいの粒度のままモジュールを利用しようと思うと、Terraformファイルが巨大になったり、サービスごとに設定が分散するというのを横目で見ていました。

The Segment AWS Stack

冒頭で紹介したSegmentが公開しているTerraform moduleは、簡単に構造を説明するとresourceをラップしたものを、別のモジュールで利用しているような、モジュールをネストした構造です。ネストと聞くとウッ...となりますが、コードを見ると分かるようにオブジェクト指向的に分割されているだけです。ここでもう少し詳細に見てみます。

冒頭のコードの下に以下を追加すると、1つのサービスが追加されます。具体的にはweatherというサービスがEC2 Container Service(ECS)上で動き、内部的に weather.stack.localでアクセス可能になります。

module "weather" {
  source         = "github.com/segmentio/stack//service"
  name           = "weather"
  image          = "peroli/weather"
  port           = 3000
  container_port = 3000
  dns_name       = "weather"

  environment     = "${module.stack.environment}"
  cluster         = "${module.stack.cluster}"
  zone_id         = "${module.stack.zone_id}"
  iam_role        = "${module.stack.iam_role}"
  security_groups = "${module.stack.internal_elb}"
  subnet_ids      = "${module.stack.internal_subnets}"
  log_bucket      = "${module.stack.log_bucket_id}"
}

さらにサービスモジュールを覗いてみると、moduleの中でELB用のモジュールを使っていたりします。

module "elb" {
  source = "../elb"

  name            = "${module.task.name}"
  port            = "${var.port}"
  environment     = "${var.environment}"
  subnet_ids      = "${var.subnet_ids}"
  security_groups = "${var.security_groups}"
  dns_name        = "${coalesce(var.dns_name, module.task.name)}"
  healthcheck     = "${var.healthcheck}"
  protocol        = "${var.protocol}"
  zone_id         = "${var.zone_id}"
  log_bucket      = "${var.log_bucket}"
}

このように1つの基盤となるモジュール、1つのサービスとなるモジュールのような粒度でもモジュールを書くことで、メンテナンスしやすく再利用可能なモジュールになっています。 もちろん、組織ごとに必要な設定は変わってくるので大きめの粒度のモジュールは必要に応じてカスタマイズするか、1から書くことになるかと思います。

ところで、Segmentが公開しているこのモジュールが何をするための物かというと、blog記事 The Segment AWS Stack にあるように、 サービスのほとんどをECS上で動かしており、その基盤の作成・サービスの追加や削除などインフラ構築のために作られています。 そして、その過程で出来たものを公開してくれています。記事に

It’s like a mini-Heroku that you host yourself. No magic, just AWS.

とあるように、ミドルウェアなど使っておらずAWSが提供しているサービスだけで完結しています。 なので、ECS上で何かをしたい方は、このモジュールに少し手を加えるだけで実際に動く環境が出来上がります。

ペロリでは、社内の開発者向けに気軽に使えるPaaSのようなものが欲しかったので参考にしています。 自由度も高く、設定もブラックボックス化しないので、今のところ良さそうな感じがしています。

さいごに

今回紹介したSegmentの記事はECSについて調べていたところ偶然見つけたのですが、個人的には知らないTerraformの書き方だ...!!と衝撃を受けました。 もしかしたら常識なのかもしれないですが、紹介した内容は見たことがなかったので紹介しました。ペロリ社内でもTerraformの死体が転がっているので、きれいに書き直そうとしています。

すこしまとめると・・・

Terraform moduleは適切な粒度で分割し、大きな粒度のモジュールがそれらを使うように書くことで、組織内で変更の必要がない部分は隠蔽することが出来ます。また、実際に使うTerraformファイルでは大きな粒度のモジュールだけ使うようにすることで、1つの基盤、1つのサービスのような粒度でインフラの構築を考えることが出来るようになります。

naruhodo。と思った方は参考にしてみてください。

Rails だって硬いデータベース設計をしたい!そんなあなたに贈る Tips 4 選

Ruby on Rails 設計 Back-End DB

こんにちは、ペロリのサーバサイドエンジニアの @a_suenami です。

今回は Ruby on Rails アプリケーションにおけるデータベース設計についてちょっとご紹介したいと思います。

データベース設計してますか?

みなさん、データベース(以下、DB)設計していますか?Scaffold したときにできた migration ファイルをそのまま使ったりしてませんよね?

Ruby on Rails (以下、Rails)は CoC(Convention over Configuration: 設定より規約)を強く提唱しているフレームワークであり、それによって得られる恩恵も大きい反面、かなり強めに設計の自由度を束縛されるという特徴もあります。特に DB 設計においては ActiveRecord を常に意識していないとアプリケーション実装において多くの屍を出してしまうことになります。

その結果として「Rails の人たちは DB 設計をしない」「あいつらは DB というものをわかっていない」と言われることもありちょっと悔しいので、いくつか僕が普段心がけていることやちょっとしたテクニックを紹介してみようと思います。

紙面の都合上、本当に基本的なことと本当によく遭遇するケースにしか触れられませんが、細かいテクニックはもっとたくさんありますし、Rails を使っているからといって堅牢な DB 設計を諦める必要はないんだということがわかってもらえると幸いです。

(ペロリでは主な DBMS として MySQL を利用しているため、多くが Rails + MySQL での Tips になります。あらかじめご了承ください。)

Tips 0. まずは基本のキ

まずは基本中の基本として、NOT NULL 制約、ユニーク制約、データ型指定、外部キー制約について紹介します。本当に基本中の基本なので知っている人はどうぞ読み飛ばしてください。

これらの制約やデータ型の定義は基本的なことではありますが、データの整合性をアプリケーション任せにせず、DB 自身が何を保証しているかを示すものであり、後述する各 Tips を構成する重要な要素となるため、しっかりとおさえておきたいところです。

NOT NULL 制約

さすがに知らない人はいないであろう NOT NULL 制約は ActiveRecord::Migration を用いたマイグレーションファイルでは次のように記述します。

create_table :members do |t|
  t.string :name, null: false
end

NULL の概念が存在しないリレーショナルデータモデルを採用している RDB においてこの制約はあるのが当たり前のものであり、ひとつのテーブルに 4 つも 5 つも NULL を許可するカラムができてしまった場合は設計がイケてない可能性を疑ったほうがよいでしょう。

ユニーク制約

テーブル内での一意性を保証するための制約としてユニーク制約があります。これはインデックスに対して unique オプションを指定することで実現できます。

create_table :members do |t|
  t.string :name,  null: false
  t.string :email, null: false
end

add_index :members, :email, unique: true

これで email カラムにユニーク制約が設定されました。カラムの指定を配列にすることによって複合ユニークキーも設定できます。

limit オプションによるデータ型の指定

多くの DBMS には多様なデータ型があり、 MySQL も例外ではありません。 同じ整数型でも許容できるバイト数によって TINYINT, SMALLINT, INT, BIGINT などがあり、よく見られる文字列型である VARCHAR もその最大長を指定することができます。 こういったデータ型は limit オプションを指定することによって実現できます。

create_table :members do |t|
  t.string  :name,          null: false
  t.string  :email,         null: false            #=> varchar(255) (it’s default)
  t.string  :phone_number,  null: false, limit: 11 #=> varchar(11)
  t.integer :sign_in_count, null: false            #=> int (it’s default)
  t.integer :sex,           null: false, limit: 1  #=> tinyint
end

データ型やその最大長はシステムが小規模のうちは特に意識しなくても実害はないでしょうし、実際ペロリでもまだデフォルトのデータ長(intvarchar(255)など)が使われることが多いですが、カラムの最大長が必要以上に大きいと行サイズの肥大化に繋がりメモリに乗りにくくなりますし、逆に大きな数字や長い文字列が登録される可能性がある場合は BIGINT や 255 文字以上の VARCHAR 、あるいは MEDIUMTEXT や LONGTEXT の使用を検討する必要があるでしょう。

外部キー制約

古いバージョンの Rails では外部キー制約の設定がマイグレーションファイルで行えず、外部 Gem を使う必要がありましたが、Rails 4.2 からはデフォルトで外部キー制約を設定できるようになりました。

create_table :member_profile_images do |t|
  t.integer :member_id, null: false
  t.string  :image_url, null: false
end

add_index :member_profile_images, :member_id

add_foreign_key :member_profile_images, :members

簡単ですね! 上記のように参照元と参照先のテーブル名を指定するだけで外部キー制約が作成され、制約名は fk_rails_xxxxxxxxxx のような形でランダムにつけられます。また、name オプションを渡すことで明示的に制約名を指定することも可能です。

Tips 1. サブタイプの設計

オブジェクト指向プログラミングにおける継承のようなことを DB のテーブル設計において実現したいことがしばしばあります。

例えば、よくある例ですが、以下のように会員が個人会員と法人会員に分かれる場合を考えてみます。共通の属性に加えて個人会員は性別を、法人会員は担当者名を必要とします。

f:id:a_suenami:20160903204923p:plain

マーチンファウラー氏の有名な著書「エンタープライズアプリケーションアーキテクチャパターン」(以下、PoEAA)ではこれを実現する方法として単一テーブル継承、具象テーブル継承、クラステーブル継承という 3 つの方法が紹介されていますが、Rails を使ったことある人であれば真っ先に思い浮かぶのは単一テーブル継承でしょう。

エンタープライズ アプリケーションアーキテクチャパターン (Object Oriented SELECTION)

エンタープライズ アプリケーションアーキテクチャパターン (Object Oriented SELECTION)

単一テーブル継承(Single Table Inheritance、以下 STI

STI はその名の通り、サブタイプも含めたすべてのクラスに含まれる属性とどのサブタイプかを示す識別子を単一テーブルに持たせ、アプリケーション側でその識別子をもとにして別クラスのインスタンスを生成するという方法です。Rails では識別子として type という名前のカラムが使われます。

テーブル定義は以下のようになります。

create_table :members do |t|
  t.string  :email,        null: false
  t.string  :name,         null: false
  t.string  :phone_number, null: false
  t.string  :type,         null: false
  t.integer :sex
  t.string  :contact_name
end

あとはアプリケーション側で PersonalMemberCorporateMember というクラスを作れば Rails がうまくハンドリングして個人会員と法人会員を別クラスとして扱うことができます。

# app/models/member.rb
class Member < ActiveRecord::Base
end

# app/models/personal_member.rb
class PersonalMember < Member
end

# app/models/corporate_member.rb
class CorporateMember < Member
end

PersonalMember.create(email: 'foo@example.com', name: 'bar', phone_number: '09000000000')
#=> INSERT INTO `members` (`email`, `name`, `phone_number`, `type`) VALUES ('foo@example.com', 'bar', '09000000000', 'PersonalMember')
Member.find_by(id: 1)
#=> SELECT  `members`.* FROM `members` WHERE `members`.`id` = 1 LIMIT 1
PersonalMember.find_by(id: 1)
#=> SELECT  `members`.* FROM `members` WHERE `members`.`type` IN ('PersonalMember') AND `members`.`id` = 1 LIMIT 1
CorporateMember.find_by(id: 1)
#=> SELECT  `members`.* FROM `members` WHERE `members`.`type` IN ('CorporateMember') AND `members`.`id` = 1 LIMIT 1
#=> nil

STI のデメリット

一見便利そうに見える STI ですがもちろんデメリットもあります。むしろシステムが大規模になった際にはデメリットのほうが目立つので僕は基本的に避けるべきだと考えています。

  • 各サブタイプの属性には NOT NULL 制約がつけられない
  • その結果、DB のテーブル定義だけからどの属性がどのサブタイプにとって必須なのかがわからない
    • 必須属性かどうかがアプリケーション任せになる
  • アプリケーションのバグによって不整合データが入ってしまう可能性がある

3 つ目に関しては Rails アプリケーションからしか参照・更新されない限りは大きな影響はないかと思いますが、直接 SQL を実行するマニュアルオペレーションがある場合や他のプログラミング言語フレームワークで実装されたアプリケーションからも参照・更新される場合はかなり致命的になりえます。

ちょっと豆知識: RailsSTI サポートによる弊害の回避

Rails では type というカラムを STI に使うため、登録時に該当するサブクラスがない場合に以下のような ActiveRecord::SubclassNotFound を投げます。

Member.create(type: 'personal') # 'personal' is not class name.
#=> ActiveRecord::SubclassNotFound: Invalid single-table inheritance type: personal is not a subclass of Member

type というカラム名STI を目的としなくてもよく使う名前であるにも関わらず、デフォルトでこのような挙動になっているせいでまるでこれを予約語かのように思ってしまいがちです。そのため、多くの Rails エンジニアが kindmember_type など別の名前でそのレコードの種類を表すカラムを作っているように思いますが、このようなよく使う名前をフレームワークにおさえられてしまうのは設計上かなりのつらさがあります。

でも安心してください。これは実は変更できるのです。

class Member < ActiveRecord::Base
  self.inheritance_column = :member_type
end

存在しないカラム名を指定すれば STI を無効化できます。

class Member < ActiveRecord::Base
  self.inheritance_column = :_type_disabled
end

より説明的になるように _type_disabled という文字列を使いましたが、カラムとして存在しない任意の文字列であれば何でも問題ありません。

クラステーブル継承

STI のデメリットを解消し DB の機能を有効に利用できる方法としてクラステーブル継承があります。PoEAA では具象テーブル継承という方法も紹介されていますが、これは抽象クラスを定義できずダックタイピングを型付けの原則とする Ruby においてはあまりメリットがないため、このエントリでの説明は省きます。

先の例をクラステーブル継承を用いて設計すると以下のようになります。

create_table :members do |t|
  t.string :email,        null: false
  t.string :name,         null: false
  t.string :phone_number, null: false
end

create_table :personal_members, id: 'INT NOT NULL PRIMARY KEY' do |t|
  t.integer :sex, null: false
end

create_table :corporate_members, id: 'INT NOT NULL PRIMARY KEY' do |t|
  t.string :contact_name, null: false
end

add_foreign_key :personal_members,  :members, column: :id
add_foreign_key :corporate_members, :members, column: :id

STI では NULL を許容してしまっていた sex contact_name に NOT NULL 制約をつけることができました。

また、ここでは id: 'INT NOT NULL PRIMARY KEY' というオプションを指定していますが、これによって主キーである id カラムが AUTO_INCREMENT ではなくなります(Rails5 では id: :integer でよいようです)。サブタイプの id は主キーであると同時にスーパータイプを参照する外部キーでもあり、その採番はスーパータイプである members テーブルで行われるからです。

もちろん AUTO_INCREMENT の id とスーパータイプを参照するユニークな外部キー member_id という設計でも問題はありません。その場合は以下のようなテーブル定義になります。

create_table :members do |t|
  t.string :email,        null: false
  t.string :name,         null: false
  t.string :phone_number, null: false
end

create_table :personal_members do |t|
  t.integer :member_id, null: false
  t.integer :sex,       null: false
end

create_table :corporate_members do |t|
  t.integer :member_id,    null: false
  t.string  :contact_name, null: false
end

add_index :personal_members,  :member_id, unique: true
add_index :corporate_members, :member_id, unique: true

add_foreign_key :personal_members,  :members
add_foreign_key :corporate_members, :members

ただし、すべてのテーブルに AUTO_INCREMENT カラムを作って必要のない採番体系を増やすことはアプリケーションの実装を複雑にし、ときに混乱を招くため、ここではスーパータイプと共有の主キーを使っています。「SQL アンチパターン」という書籍でもすべてのテーブルに考えなしに ID カラムをを作ることを「ID リクワイアド(とりあえずID)」というアンチパターンとして紹介しています。

SQLアンチパターン

SQLアンチパターン

設定より規約を重視する Rails において ActiveRecord 継承クラスは id という名前のカラムを多くの場面で要求しますし、それでレコードが特定できることを前提としていますが、それはカラム名と一意性が満たされていれば問題なく、AUTO_INCREMENT であるかどうかまでは求められないため、この設計でも Rails で扱う上で問題はありません。

特定のサブタイプに対する外部キー制約

クラステーブル継承を用いるメリットのひとつとして、特定のサブタイプを外部キー参照できることが挙げられます。

例えば上記の例で、法人会員だけ請求や決済の履歴を残さなければならないという要求があるとします。この場合、以下のようにサブタイプに対する外部キー制約をつけることによって実現できます。

create_table :member_transaction_logs do |t|
  t.integer :member_id,        null: false
  t.integer :transaction_type, null: false
  t.text    :detail,           null: false
end

add_foreign_key :member_transaction_logs, :corporate_members, column: :member_id

STI の場合は 1 テーブルしかありませんから members テーブルを参照するしかありません。そのため、個人会員にもこの履歴を登録されることが可能になってしまい、データ不整合の可能性を残してしまっています。

Tips 2. 外部キー制約を用いたカラム値の制限

あるカラムに特定の値以外入れたくないケースがあります。フラグやステータスのようなカラムは多くの場合そうでしょう。こういう場合、MySQL では ENUM 型を使って実現できますし、他の DBMS では CHECK 制約によって実現できます。

-- MySQL
CREATE TABLE `members` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` enum('personal','corporate') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4

-- PostgreSQL
CREATE TABLE members (
    id integer NOT NULL PRIMARY KEY,
    type character varying(16) NOT NULL CHECK (type in ('personal', 'corporate'))
);

ただし、このような設計は先述した「SQLアンチパターン」という書籍において「31 フレーバー」というアンチパターンとして紹介されています。また、このテーブル定義を Railsマイグレーションファイルで実現しようとすると標準の機能では実現できず外部 Gem を利用する必要があります。

これに対する解決策は SQL アンチパターンでも紹介されていますが、マスターとなるテーブルを作成し外部キー制約を用いることです。

create_table :members do |t|
  t.integer :member_type_id, null: false
end

create_table :member_types do |t|
  t.string :type, null: false, limit: 16 # 'personal' or 'corporate'
end

add_index :member_types, :type, unique: true

add_index :members, :member_type_id

add_foreign_key :members, :member_types

先に紹介した add_foreign_keyprimary_key というオプションを指定することで参照先のカラムを id 以外にすることができます。これは primary_key という名前ではありますがユニーク制約がついているカラムであればいずれのカラムでも指定できるため、以下のような指定も可能です。

create_table :members do |t|
  t.string :member_type, null: false, limit: 16
end

add_index :members, :member_type

add_foreign_key :members, :member_types, column: :member_type, primary_key: :type

フラグやステータスに意味を持たせるのは通常アプリケーションなのでここで作成したマスターテーブル自体をアプリケーションから利用することはないと思いますが、データ自体を説明的にするという意味では整数値ではなく文字列のほうがよいかもしれませんし、次のように説明用のためのカラムを用意してもよいかもしれません。

create_table :member_types do |t|
  t.string :type,        null: false, limit: 16 # 'personal' or 'corporate'
  t.text   :description, null: false
end

いずれにしても、これによって新たな状態やフラグ値の追加を ALTER ではなくレコードの INSERT で実現できることになります。

Tips 3. テーブル分割による相互参照の実現

例えば会員制のシステムにおいて、会員は複数のプロフィール画像を登録することができ、そのうちのひとつが必ずデフォルトの画像になるとします。デフォルト画像を複数許容することなく必ずひとつであることを DB で保証しようとすると会員からプロフィール画像に対する外部参照キーを作成する必要があります。

おそらく次のような設計になるでしょう。

create_table :members do |t|
  t.integer :default_profile_image_id, null: false
end

create_table :member_profile_images do |t|
  t.integer :member_id, null: false
  t.string  :image_url, null: false
end

add_index :member_profile_images, :member_id
add_foreign_key :member_profile_images, :members

add_index :members, :default_profile_image_id
add_foreign_key :members, :member_profile_images, column: :default_profile_image_id

ただし、これでは membersmember_profile_images が相互に参照しており、会員登録ができません。members に INSERT するためには有効な member_profile_images のレコードが存在していなければなりませんが、そのためには member_id がすでに採番されていなければならないからです。テーブルとシーケンスが別のデータベースオブジェクトでなく INSERT したタイミングでしか採番ができない MySQL のつらいところです。

これをこのテーブル構造のままどうにかしようとするとどちらかのカラムの NOT NULL 制約か外部キー制約を諦める必要があります。しかしもうひとつ、以下のようにテーブルを分割して対応することもできます。

create_table :members do |t|
end

create_table :member_default_profile_images, id: :integer do |t|
  t.integer :member_profile_image_id, null: false
end

create_table :member_profile_images do |t|
  t.integer :member_id, null: false
  t.string  :image_url, null: false
end

add_index :member_profile_images, :member_id

add_foreign_key :member_profile_images, :members

add_index :member_default_profile_images, :member_profile_image_id

add_foreign_key :member_default_profile_images, :members, column: :id
add_foreign_key :member_default_profile_images, :member_profile_images

このように membersmember_default_profile_images にテーブル分割することによって

  1. members へ INSERT(member_id が採番される)
  2. member_profile_images へ INSERT(member_profile_image_id が採番される)
  3. member_default_profile_images へ INSERT

という手順により会員登録を行うことが可能になります。

ただし、この設計は「会員は任意の数のプロフィール画像を登録できる」「そのうちのひとつをデフォルト画像として設定することができる」ということしか表現できておらず、もともとの「プロフィール画像のうちのひとつが【必ず】デフォルト画像になる」という要件がアプリケーション任せになっています。

もともとの設計でも NOT NULL 制約か外部キー制約を諦めなければならなかったのですからそれに比べればマシになっていると捉えてよいでしょうし、むしろ捉え方によってはデフォルト画像の有無はアプリケーションの実装次第で変更できることになるためその部分の拡張性を得たということにもなりますが、DB レベルで何が保証されていて、アプリケーションが何を意識しないといけないのかという点については常に考えていく癖をつけていく必要があります。

ちょっと豆知識: 遅延制約

実はここで生じた問題は DBMS によっては*1別の解決方法もあります。それは遅延制約といって、制約の評価をトランザクションのコミット時まで遅らせる機能を利用することです。

例えば、PostgreSQL では NOT NULL および CHECK 制約は即座に評価されますがその他の制約は遅延させることができ、以下のようにしてこれを実現できます*2

CREATE TABLE members (
  id int NOT NULL PRIMARY KEY,
  default_profile_image_id int NOT NULL
);
CREATE INDEX index_members_on_default_profile_image_id ON members (default_profile_image_id);

CREATE TABLE member_profile_images (
  id int NOT NULL PRIMARY KEY,
  member_id int NOT NULL,
  image_url varchar(255) NOT NULL
);
CREATE INDEX index_member_profile_images_on_member_id ON member_profile_images (member_id);

-- 外部キー制約
ALTER TABLE members ADD CONSTRAINT fk_members_default_profile_image_id FOREIGN KEY (default_profile_image_id) REFERENCES member_profile_images (id) INITIALLY DEFERRED;
ALTER TABLE member_profile_images ADD CONSTRAINT fk_member_profile_iamges_member_id FOREIGN KEY (member_id) REFERENCES members (id);

members の外部キー制約定義のみ末尾に INITIALLY DEFERRED という記述がありますが、これによってこの制約は遅延制約となりトランザクションコミット時まで評価されないため、先に members にデータを登録して members.id を採番することによって相互参照することが可能になります。

こういった便利な機能も(使えるのであれば)どんどん使っていきたいですね。

Tips 4. ビューの活用

前の例のようなテーブル分割によって必ずといって生じるのがビューを使いたいという要求です。ERB とか Haml のことじゃありませんよ?DB のビューですね。例えばこういう感じです。

CREATE VIEW `members_view` AS
SELECT
  `members`.`id`,
  `member_default_profile_images`.`member_profile_image_id`
FROM
  `members`
INNER JOIN
  `member_default_profile_images`
ON
  `members`.`id` = `member_default_profile_images`.`id`

Rails でこのようなビューを扱うことは実は難しくなく、ActiveRecord を継承したクラスでテーブル(ビュー)名を明示的に指定すれば実現できます。

class Member < ActiveRecord::Base
  self.table_name = :members_view
end

ただし、当然ですが挿入や更新が可能なビューでない限り、このクラスの createupdate メソッドは使えません。更新系のクエリは実テーブルとマッピングされた別の ActiveRecord 継承クラスが必要になるでしょう。

ビューの管理

DB のビューが Rails でも使えることはわかりましたが、マイグレーションスキーマ管理についてどうするのがいいでしょうか。これは難しい問題です。

ActiveRecord::Migration でビューの管理をできるようにする Gem もいくつか公開されていますが、デファクトスタンダードと呼べるものは(おそらく)なく、そういった Gem を使わずにこれらを実現するためには execute を使うしかありません。

execute <<CREATE_VIEW
CREATE VIEW `members_view` AS
SELECT
  `members`.`id`,
  `member_default_profile_images`.`member_profile_image_id`
FROM
  `members`
INNER JOIN
  `member_default_profile_images`
ON
  `members`.`id` = `member_default_profile_images`.`id`
CREATE_VIEW

また、この場合は rake db:schema:dump の結果にもビューがあらわれませんので、スキーマ管理を RubyDSL ではなく SQL にする必要があります。

config.active_record.schema_format = :sql

このあたりの管理方法については今後もよりよい方法を模索していきたいと考えています。

その他

ペロリで現在使っていないため今回は紹介できなかったのですが、DBMS には他にも

  • マテリアライズド・ビュー
    • MySQL にはないです😿
  • ストアドプロシージャ
  • トリガー

などの便利な機能が多くあります。

ストアドプロシージャやトリガーについては使っている開発現場とそうでない現場が大きく分かれると聞きますし、取り扱いが難しいものはある*3かと思いますが、OLTP(Online Transaction Processing: オンライントランザクション処理)でなく分析用途やマニュアルオペレーションの効率化であれば導入しやすいと思いますし、そういったところから利用を検討してみてもよいかもしれません。

ポエム(という名のまとめ)

一般にデータの寿命はアプリケーションコードより長いです。アプリケーションは何度も修正され、ときには違うプログラミング言語でフルリニューアルされることもありますが、DB は最初に設計されたときのまま運用され続けるということはザラにあります。

だからこそ DB は、ビジネスの展望やその中でのアプリケーションの役割を考慮した上で慎重に設計されるべきです。YAGNI 原則は我々のような市場の移り変わりが速い業界において美徳でありますが、この原則を口実にして行き当たりばったりな設計をしているケースも少なくないと僕は感じており、こと DB 設計においてそれはビジネスにおいて最重要と言っても過言ではないであろうデータをどんどん腐らせていく結果になりえます。

よく設計された DB スキーマはそれを見ただけである程度のビジネスルールやアプリケーションが前提とする諸条件がわかるものです。テーブルやカラム、インデックスにコメントなどなくてもです。アプリケーションコードにおいてはコメントが少なくコードそのものが説明的であることがしばしば美徳とされますが、DB 設計においてはこういったことがあまり叫ばれないことを僕は常々不思議に思っています。

もちろんドキュメントが必要ないと言うつもりはまったくありませんが、自分たちのビジネスを表現する手段として自然言語で書かれたメンテナンスされるかどうかもわからないドキュメントだけに頼るのではなく、DB のテーブル名、カラム名やデータ型、インデックスや各種の制約、ビュー、マテリアライズドビューとそのリフレッシュ周期、場合によってはストアドプロシージャやトリガーなどを用い、DB にもっとビジネスを語らせることが重要だと考えています。

DB はアプリケーションがビジネスルールを正しく守っているかどうかの最終防衛ラインです。僕個人は、DB は事実を記録するべきであり、その解釈はアプリケーションに任せるほうがチームやビジネスのアジリティを向上できるのではないかと思っていますし、そういう意味では DB が必要以上にビジネスロジックに対して出しゃばることは避けるべきですが、その業界やその会社において普遍的なルールや前提を見極め、ずっともしくは長期間にわたって変わらないであろうルールは DB に守らせるべきです。

DB の設計がチームで採用しているアプリケーションフレームワークや ORM にある程度の制約を課されるのはしかたない側面もありますが、その中においても DBMS が持つパワフルな機能を最大限生かす方法は必ずありますし、それこそが僕たちのエンジニアとしての腕の見せ所でもあります。今回は Rails + MySQL におけるいくつかの事例を紹介しましたが、これをきっかけにして、堅牢で高性能で、そして何より雄弁にビジネスを語る、そんな DB スキーマがひとつでも多く世に生み出されることを願っています。

*1:というか、MySQL 以外はだいたいできるのでは…

*2:ペロリでは PostgreSQL で稼働しているシステムは社内システムも含めて(たぶん)ありません。個人的に好きなだけです。😿

*3:製品ごとに方言が激しい、テストがしにくい、など

© peroli, Inc.