Feedforce Developer Blog

フィードフォース開発者ブログ

請求関連テーブルのスキーマ変更をした話

RDB データベース 運用 運用事例

以前に アプリケーションを停止させずにRDBのスキーマ変更する話 を書きました。

developer.feedforce.jp

今日は、その実践編というか、実例として EC Booster というサービスで請求関連テーブルのスキーマを変更した話をしようと思います。

はじまりのテーブル

元々、 EC Booster の請求を管理するテーブルは、このような形でした。

create_table "monthly_charges", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.uuid "shop_id", null: false
  t.integer "year", null: false
  t.integer "month", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "fee", null: false
  t.integer "ad_spend", null: false
  t.integer "vat", null: false
  t.integer "vat_percentage", default: 10, null: false
  t.string "vat_decimal", default: "floor", null: false
  t.index ["shop_id", "year", "month"], name: "index_monthly_charge_on_shop_id_and_year_and_month", unique: true
end

以降、スキーマについては ActiveRecord の Migration の記法で書いています。
また、今回の主題に関わらないカラムは除いています。

この一つだけで、すべての請求が管理できていました。
それは、EC Booster では長いこと「Google ショッピング広告の運用」だけをやっていたからです。
EC Booster のサービス利用料金は、お客様の広告出稿費のうち 30% をいただく形でした。

それだけであれば、テーブル一つだけで事足ります。
もちろん、この時点で正規化をしておいても良かったのだと思うのですが、この時点ではまだあるべき構造が見えていませんでした。

なので EC Booster では長いこと、この monthly_charges というテーブル一つだけで請求の情報に対応していました。

課題：増えてゆく広告媒体と課金形態

ですが EC Booster では、お客様の様々なニーズに応えるべく「Google DSA」や「Google P-MAX」と出稿できる広告の種類を増やしてきました。
それだけでなく、広告出稿を伴わずに Google Merchant Center の無料リスティングを活用できる「エントリープラン」や、出稿費によらず固定額をいただく形の「プロプラン」など、課金の形態も様々に増えてきました。

それらの複雑な料金体系を一つのテーブルで表現するためには、たくさんのカラムが必要になり、無理があるでしょう。
また、それぞれの料金を請求書の明細項目として表示するために、個々に明細項目を組み立てており、それによるコードの肥大化も課題になっていました。

create_invoice_item(
  name: "Google ショッピング広告 広告費 #{monthly_charge.year}年#{monthly_charge.month}月分",
  price: monthly_charge.ad_spend,
)
create_invoice_item(
  name: "Google ショッピング広告 手数料 #{monthly_charge.year}年#{monthly_charge.month}月分",
  price: monthly_charge.fee,
)

こういった明細項目作成のコードが、広告媒体や料金プランごとに並んでいる状態でした。

それらを解決するために、思い切ってテーブルのスキーマ変更とともなうリファクタリングに着手することにしました。

その場しのぎ

とはいえ、最初に「Google DSA」という広告媒体を増やした際には、手っ取り早く対応するために monthly_charges テーブルにカラムを増やすことを選択しました。

create_table "monthly_charges", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.uuid "shop_id", null: false
  t.integer "year", null: false
  t.integer "month", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "fee", null: false
  t.integer "ad_spend", null: false
  t.integer "vat", null: false
  t.integer "vat_percentage", default: 10, null: false
  t.string "vat_decimal", default: "floor", null: false
  t.integer "google_dsa_ad_spend", default: 0, null: false
  t.integer "google_dsa_fee", default: 0, null: false
  t.index ["shop_id", "year", "month"], name: "index_monthly_charge_on_shop_id_and_year_and_month", unique: true
end

その場しのぎであると、後からならいくらでも言えますが、当時は当時でいくつかの理由からこの方法を取ることを選択しました。
あえての技術的負債と言っても良いかと思います。
実際、請求関連テーブルにはリファクタリングが必要だという課題も当時から持ち上がっていました。

ですが、 Google ショッピング広告 と Google DSA だけが請求の対象の間は、万が一の事故のリスクとリファクタリングの必要性を天秤にかけ、長らくそのままになっていました。

課題を解決するとき

さて、EC Booster の変革は、エントリープランの登場でした。
広告出稿は行わずに、 Google Merchant Center の管理と無料リスティングに対して利用料金をいただくというプランです。
これまでの広告出稿費用をベースにしたものとは、全然違う料金形態を持ちます。
この時点で、再度 monthly_charges テーブルにカラムを追加する案もありました。

が、Google ショッピング広告利用料金、Google DSA 利用料金に続いて、エントリープラン利用料金で 3 つ目の料金形態です。
リファクタリングでも同じことを 3 回書いたら共通化、と言われる通り、スキーマの見直しをするには良いタイミングだったのだと思います。

新しいテーブルの追加

明細行が全て 1 つのテーブルにあることが、この時の monthly_charges の問題でした。
そのために見通しが悪く、新しい料金形態が追加されるたびにカラムが増え、テーブルが肥大化することになります。

というわけで手始めに、 monthly_charges に記録されている明細行を別のテーブルに切り出すことにしました。
各明細行テーブルは共通化せずに、それぞれの用途毎に別のテーブルで表現しています。

create_table "monthly_charges", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.uuid "shop_id", null: false
  t.integer "year", null: false
  t.integer "month", null: false
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.integer "fee", null: false
  t.integer "ad_spend", null: false
  t.integer "vat", null: false
  t.integer "vat_percentage", default: 10, null: false
  t.string "vat_decimal", default: "floor", null: false
  t.integer "google_dsa_ad_spend", default: 0, null: false
  t.integer "google_dsa_fee", default: 0, null: false
  t.index ["shop_id", "year", "month"], name: "index_monthly_charge_on_shop_id_and_year_and_month", unique: true
end


create_table "monthly_charges_entry_plan_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "days", null: false
  t.integer "unit_price", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_entry_plan_items_on_monthly_charge_id", unique: true
end

create_table "monthly_charges_google_dsa_fee_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_dsa_fee_items_unique", unique: true
end

create_table "monthly_charges_google_dsa_spend_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_dsa_spend_items_unique", unique: true
end

create_table "monthly_charges_google_shopping_fee_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_shopping_fee_items_unique", unique: true
end

create_table "monthly_charges_google_shopping_spend_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_shopping_spend_items_unique", unique: true
end

この時点ではまだテーブルの追加だけで、コードは古いスキーマ（monthly_charges のカラム）を参照するようになっています。

新しいテーブルに過去の情報をコピーする

テーブルを追加した後、請求額確定の際には元々の monthly_charges テーブルのカラムと新しい明細行テーブルの両方に値を書き込むよう、コードを変更しました。
そして、この状態で、過去の monthly_charges テーブルの明細行の値を新しい明細行テーブルにコピーする作業を行いました。

こうすることで、古いスキーマでも新しいスキーマでも同じ情報を参照できる状態になりました。

新しい明細行テーブルを参照する

いよいよ、コード上で新しい明細行テーブルを参照するように変更します。
この変更によって、 monthly_charges の古い明細行カラムはただ更新されるだけで参照されないカラムになりました。

これで monthly_charges の明細行カラムはいつでも削除できるようになりました。
とはいえ念の為、カラム削除は月一回の請求処理を待ってから、と決めました。

新しいスキーマの良いところ

新しい明細テーブルは、全て label と price というカラムを持つこととしました。
label は明細項目名、 price はその料金です。

これまで個々に組み立てたり指定したりしていた明細項目名でしたが、明細テーブルのレコードが label と price を必ず持っていることで、請求書を作成する際の明細行の処理が共通の繰り返し処理で書けるようになりました。

monthly_charge.each_items do |item|
  create_invoice_item(
    label: item.label,
    price: item.price,
  )
end

結果的にコードもすっきりとコンパクトになりました。

さらに明細行が追加される

その後も「Google P-MAX」という広告媒体の追加や、「プロプラン」という料金プランの追加と、大きな変更がありました。
それらも新しいスキーマで柔軟に対応できるようになりました。

create_table "monthly_charges_google_pmax_fee_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_pmax_fee_items_unique", unique: true
end

create_table "monthly_charges_google_pmax_spend_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_google_pmax_spend_items_unique", unique: true
end

create_table "monthly_charges_pro_plan_items", id: :uuid, default: -> { "gen_random_uuid()" }, force: :cascade do |t|
  t.datetime "created_at", null: false
  t.datetime "updated_at", null: false
  t.uuid "monthly_charge_id", null: false
  t.integer "days", null: false
  t.integer "unit_price", null: false
  t.integer "price", null: false
  t.string "label", null: false
  t.index ["monthly_charge_id"], name: "index_monthly_charges_pro_plan_items_on_monthly_charge_id", unique: true
end

新しい明細行を明細テーブルとして追加し、それらを繰り返し処理の中に含めるようにするだけで、新しい明細行への対応が完了するようになりました。
そのため、新しい明細の料金計算という重要な事柄に集中して開発を進められています。

▲