先人達から学ぶRailsのテーブル設計 #Ruby

はじめに

Ruby on Rails Advent Calendar 2017 - Qiita の4日目の記事です。

背景

Railsのテーブル設計について、社内で議論することは多いのですが、サービスの要となる部分であるが故、社外にER図を公開するケースは少なく、自分達のサービス開発時以外にテーブル設計を学ぶ機会が少ないです。

目的

OSSで公開されているRailsアプリケーションのソースコードから、テーブル設計に関するデータをまとめることで、テーブル設計時の議論に活かすことを目的とします。
具体的には、「1テーブルが持つ情報量として、どれくらいのカラム数が妥当なんだろう…？」や「テーブル名やカラム名を命名する時にどちらの単語の方が一般的に使われているんだろう…？」といった疑問点の解消を目指します。

方法

OSSで公開されているRailsアプリケーションの見つけ方

AwesomeRailsのApps Made with Railsの項を参照します。各アプリケーションのGitHubリポジトリへのリンクが張られています。今回はこちらのリストに載っているRailsアプリケーションを調査対象としました。

大まかな調査の流れ

具体的なデータの出し方は参考の項で後述します。

Awesome RailsのREADMEからリポジトリ一覧データを保存する
各リポジトリにある db/scheme.rb ファイルをダウンロードする
各 scheme.rb ファイルの内容から、テーブルに関するデータとカラムに関するデータを保存する
保存したデータを分析する

調査結果

調査対象

Awesome Railsに記載があった92リポジトリの内、 scheme.rb ファイルがなかった33リポジトリを除いた59リポジトリを対象としました。

1テーブルが持つべき情報量としてはどれくらいのカラム数が妥当なのか

1テーブルが持つカラム数の値は下記のようになりました。

最小	最大	中央値	平均	分散
1	71	6	7.78	46

ヒストグラムにすると下記のような図となりました。横軸がカラム数で縦軸が該当するテーブル数です。各カラム数のテーブルがどれくらいあるのかを示しています。

71カラムあるテーブルがあったものの、中央値が6、平均値が7.78、約8割以上が10以下に分布していることから、1テーブルが持つべきカラム数は10以下が妥当かと思います。もちろんケースによっては超える場合もあるかと思いますが、10カラムを超えた設計となっている場合、正規化ができないか見直してみるのも良いかもしれません。

ちなみに、71カラムあったテーブルはUserでした。bank_codeが入っていたりと、正規化できそうな要素は多そうです。

テーブル名によく使われている単語トップ100

下記のサービスを使い出現回数の多い100単語をワードクラウド化しました。出現回数が多い単語ほど、文字サイズが大きくなっています。下記のサービスでは、usersとuserのような表記ゆれもuserとしてカウントしてくれるので、表記に関してもこちらのサービスで正規化しました。

WordArt.com - Word Art Creator

定番機能の単語が多くなるので、Favorite、User、Comment、Tag、Post、Itemなどの定番テーブル名が多くなりました。一般的に使われている単語としては、やはりこの100個に入っているものが多いのではないでしょうか。個人的にCourseとAssessmentはあまり使わないので、3番目と6番目だったのが意外でした。

テーブル名に使われている単語と出現回数の一覧

カラム名によく使われている単語トップ100

同様にカラム名に使われている出現回数の多い100単語をワードクラウド化しました。Railsで生成されるidとcreatedとupdatedとatを除いています。

nameやemailといった定番の単語はやはり出現回数が多いです。userが多いのは、userへのアソシエーションが張られるケースが多く、user_idが多く登場したのではないかと思います。titleやcontentやtextと言ったPost系の単語も多いです。fileはattachも入っているので、attached_fileといった使われ方が多そうです。ipやcountが多かったのが意外に思いましたが、signも入っているので、deviseの影響も大きそうです。

カラム名に使われている単語と出現回数の一覧

型別のカラム数

下記の表の通りとなりました。

type	number_of_columns
integer	4029
string	4005
datetime	3103
boolean	1103
text	950
decimal	131
float	127
bigint	74
date	65
datetime_with_timezone	33
jsonb	18
binary	18
inet	17

予想通り、integer、string、datetime、boolean、textが大半を占めていました。decimalの方がfloatより使われていました。

終わりに

今回は、テーブル設計の一般的なデータについてまとめました。テーブル設計の際に参考になれば幸いです。テーブル設計には各業種の知識も必要になるかと思うので、下記のサービスで近い業種のサービスのER図を参考にすると良いかもしれません。

ERD PORN

参考

具体的なデータの出し方

今回使ったGemは下記の通りです。

Gemfile

source "https://rubygems.org/"

gem 'activerecord'
gem 'mysql2'
gem 'pry'
gem 'pry-byebug'
gem 'rake'
gem 'sinatra'
gem 'sinatra-activerecord'

ActiveRecordを使い、Repository、Table、Column、TableWord、ColumnWordのモデルとマイグレーションを定義します。

Awesome RailsのリポジトリからREADME.mdファイルを取得します。

01_get_awesome_rails_readme.rb

require 'open3'

Open3.capture3('wget -O files/01_awesome_rails_readme.md https://raw.githubusercontent.com/ekremkaraca/awesome-rails/master/README.md')

RepositoryのデータをDBに保存します。

02_create_repository_data.rb

require "active_record"
require "yaml"
require "./app/models/repository"

config = YAML.load_file('./config/database.yml')
ActiveRecord::Base.establish_connection(config["development"])

is_app_list = false

begin
  File.open('files/01_awesome_rails_readme.md') do |file|
    file.each_line do |line|
      is_app_list = true if line.include?("### Apps Made with Rails")
      is_app_list = false if line.include?("### Tutorials")
      Repository.find_or_create_by!(name: line.match(%r{https://github.com/(.+?)\)})[1]) if is_app_list && line.include?("https://github.com")
    end
  end
rescue SystemCallError => e
  puts %Q(class=[#{e.class}] message=[#{e.message}])
rescue IOError => e
  puts %Q(class=[#{e.class}] message=[#{e.message}])
end

各リポジトリのscheme.rbファイルを保存します。

03_get_scheme_files.rb

require "active_record"
require "yaml"
require 'open3'
require "./app/models/repository"

config = YAML.load_file('./config/database.yml')
ActiveRecord::Base.establish_connection(config["development"])

Repository.all.each do |repository|
  filename = repository.name.match(%r{/(.*)})[1]
  Open3.capture3("wget -O files/#{filename}.rb https://raw.githubusercontent.com/#{repository.name}/master/db/schema.rb")
end

scheme.rbの内容を解析して、Table、ColumnのデータをDBに保存します。

04_create_scheme_data.rb

require "active_record"
require "yaml"
require 'open3'
require "./app/models/column"
require "./app/models/repository"
require "./app/models/table"

config = YAML.load_file('./config/database.yml')
ActiveRecord::Base.establish_connection(config["development"])

is_table = false
table = nil

begin
  Repository.all.each do |repository|
    filename = repository.name.match(%r{/(.*)})[1]
    File.open("files/#{filename}.rb") do |file|
      file.each_line do |line|
        is_table = true if line.include?("create_table")
        is_table = false if line.include?("end")
        next unless is_table
        if line.include?("create_table")
          table = Table.find_or_create_by!(name: line.match(/create_table "(.+?)"/)[1], repository: repository)
        end 
        if line.include?("t.") && !line.include?("t.index")
          Column.find_or_create_by!(
            name: line.match(/"(.+?)"/)[1],
            data_type: line.match(/t\.(.+?)\s/)[1],
            table: table
          )
        end
      end
    end
  end
rescue SystemCallError => e
  puts %Q(class=[#{e.class}] message=[#{e.message}])
rescue IOError => e
  puts %Q(class=[#{e.class}] message=[#{e.message}])
end

テーブル名、カラム名を単語に分割して、DBに保存します。

05_create_word_data.rb

require "active_record"
require "yaml"
require 'open3'
require "./app/models/column"
require "./app/models/column_word"
require "./app/models/repository"
require "./app/models/table"
require "./app/models/table_word"

config = YAML.load_file('./config/database.yml')
ActiveRecord::Base.establish_connection(config["development"])

Table.all.each do |table|
  table.name.split("_").each do |word|
    TableWord.find_or_create_by!(name: word, table: table)
  end
end

Column.all.each do |column|
  column.name.split("_").each do |word|
    ColumnWord.find_or_create_by!(name: word, column: column)
  end
end

あとは、DBに保存したデータを元に、SQLを書いて、データを出しました。
今回使用したスクリプトとデータと結果はGitHubに公開していますので、こちらのデータを使って何か分析する際にはご自由にお使いください。

kikunantoka/awesome-rails-scheme