夫婦で使う家計簿アプリ、あったらいいので作ります。⑥ SQLiteのデータベース設計と夫婦の清算ロジック

家計簿アプリ

前回は、FastAPIを使った支出管理APIの実装方法について紹介しました。

今回は、SQLiteを使ったデータベースの設計と、夫婦独自の清算処理の実装方法について紹介します。

今回の家計簿アプリでは、毎日の支出や収入を記録するだけでなく、カテゴリごとの予算管理や、夫婦間の清算額の計算も行っています。

そのためには、単純に金額を保存するだけでなく、「何に使ったか」「誰が支払ったか」などの情報も管理する必要があります。

そこで、支出やカテゴリ、支払者などの情報を別々のテーブルに分け、必要に応じて組み合わせる仕組みにしました。

今回は、実際のソースコードをもとに、データの保存方法から毎月の清算額を計算するまでの流れを紹介します。


家計簿のデータをどう管理するか

まずは、今回のアプリで使用しているデータベースの構成です。

SQLiteでは、データを「テーブル」と呼ばれる表に分けて管理します。

例えば、支出の記録、カテゴリの設定、毎月の予算などを、それぞれ別のテーブルに保存しています。

今回のアプリで使用する主なテーブルは次のとおりです。

テーブル名保存する情報
transactions支出の履歴
household_incomes収入の履歴
categories支出カテゴリ
subcategories詳細項目
users支払者
payment_methods支払い方法
monthly_budgets月ごとの予算
app_settings清算日やNISAなどの設定
monthly_settlements清算の完了状況

このほかにも、固定費やNISAの積立履歴、オフライン同期などに使用するテーブルがあります。

ただし、すべてを一度に説明すると複雑になるので、今回は家計簿の中心となる支出管理と清算処理に絞ります。

支出データはIDを使って管理する

今回のデータベース設計で特に重要なのが、支出カテゴリと支払者を別々に管理することです。

例えば、妻がスーパーで2,000円の食材を購入した場合を考えます。

家計簿には次の情報を保存します。

項目登録内容
日付2026-10-10
金額2,000円
カテゴリ食費
詳細項目自炊
支払者妻
支払い方法クレジットカード

※説明用のサンプルデータです。

これらの情報は、transactionsテーブルに保存されます。

ただし、実際のデータベースでは、「食費」や「妻」といった名前を直接保存するのではなく、それぞれのIDを使っています。

例えば、カテゴリIDが1なら食費、支払者IDが2なら妻、といった形です。

こうしておけば、あとからカテゴリ名を変更しても、過去の支出データを一つずつ修正する必要がありません。

また、カテゴリや支払者を別々のテーブルで管理することで、同じ情報を何度も保存せずに済みます。

データの整合性も確認する

SQLiteでは、テーブル同士の関係を外部キー(FOREIGN KEY)として設定できます。

今回のtransactionsテーブルにも、カテゴリや支払者などの外部キーを設定しています。

また、database.pyでは、データベースへ接続するときに次の設定を行っています。

connection.execute("PRAGMA foreign_keys = ON")

これにより、存在しない支払者IDなどを参照するデータが保存されることを防ぎます。

第5回で紹介したPydanticやServiceによる入力検証に加えて、データベース側でも整合性を保つ仕組みを取り入れています。


毎月の予算は別のテーブルで管理

家計簿では、カテゴリごとに毎月の予算を設定しています。

例えば、10月の食費を40,000円、11月の食費を45,000円にすることもあります。

そのため、予算はカテゴリの設定とは別に、monthly_budgetsテーブルで管理しています。

実際のdatabase.pyでは、次のように定義しています。

CREATE TABLE IF NOT EXISTS monthly_budgets (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    year_month TEXT NOT NULL,
    category_id INTEGER NOT NULL,
    amount INTEGER NOT NULL DEFAULT 0 CHECK (amount >= 0),
    FOREIGN KEY (category_id) REFERENCES categories(id),
    UNIQUE(year_month, category_id)
);

ここで重要なのが、year_monthとcategory_idです。

この2つを組み合わせることで、「2026年10月の食費の予算」という情報を管理できます。

また、UNIQUE(year_month, category_id)を設定しているため、同じ月・同じカテゴリの予算を重複して登録できないようにしています。

カテゴリの種類と、月ごとの予算を別々に管理することで、生活の変化に合わせて柔軟に予算を設定できます。


SQLを使って予算と支出を集計する

次に、保存したデータを使って残り予算を計算します。

例えば、食費の予算が40,000円で、今月12,000円使っていた場合です。

残り予算は、

40,000円 − 12,000円 = 28,000円

となります。

この計算に必要なのが、カテゴリごとの支出合計です。

実際のsettlement_service.pyでは、次のようなSQLを使用しています。

SELECT COALESCE(SUM(amount), 0) AS amount
FROM transactions
WHERE transaction_date >= ?
  AND transaction_date < ?
  AND category_id = ?

このSQLでは、指定した期間とカテゴリに一致する支出金額を合計しています。

SUM(amount)が金額の合計を求める部分です。

また、COALESCE()を使うことで、対象の支出がない場合も0として扱えるようにしています。

日付は月初から翌月初の直前までを対象にすることで、月ごとの支出を集計します。

こうして取得した支出合計を予算から差し引くことで、まとめ画面の残り予算を表示しています。


「誰が支払ったか」も同じデータから集計する

今回のアプリでは、カテゴリ別の集計だけでなく、支払者別の集計も行っています。

第2回で紹介したように、夫婦それぞれが生活費を支払うためです。

そこで、支出を登録するときには必ずuser_idも保存しています。

支払者ごとの金額は、次のSQLで取得できます。

SELECT COALESCE(SUM(amount), 0) AS amount
FROM transactions
WHERE transaction_date >= ?
  AND transaction_date < ?
  AND user_id = ?

先ほどのSQLとの違いは、category_idではなくuser_idで集計していることです。

つまり、

  • category_idで集計:何の予算をいくら使ったか
  • user_idで集計:誰がいくら支払ったか

という2つの集計を、同じ支出データから行っています。

この仕組みによって、予算管理と夫婦間の清算を両立できるようにしました。


夫婦の清算額をPythonで計算する

ここからは、今回の家計簿で特にこだわった清算処理です。

清算の計算は、settlement_service.pyにまとめています。

中心となるのが、calculate_settlement()という関数です。

この関数では、夫婦それぞれの個人費の予算や支出、妻の収入などを取得して、清算額を計算しています。

夫のお小遣いを計算する

夫のお小遣いは、個人費の予算から使った金額を差し引いて計算します。

例えば、

  • お小遣い予算:30,000円
  • 今月の個人費:22,000円

の場合です。

30,000円 − 22,000円 = 8,000円

となり、翌月のお小遣いは8,000円です。

実際のコードでは、次のように計算しています。

husband_settlement_amount = (
    husband_budget - husband_personal_spent
)

非常にシンプルな計算です。

ただし、予算を超えて使った場合は、計算結果がマイナスになります。

例えば、30,000円の予算に対して35,000円使った場合は、−5,000円です。

このように予算オーバーもそのまま計算に反映できるようにしています。

妻の口座に残す金額を計算する

妻の場合は、少し計算が複雑になります。

妻の口座には、個人費の残額に加えて、妻が支払った金額も考慮しておく必要があります。

例えば、次のような場合です。

項目金額
妻のお小遣い予算30,000円
妻個人の買い物12,000円
妻が支払った家族共通費28,000円
妻が支払った合計40,000円

この場合、個人費の残額は18,000円です。

また、妻がクレジットカードで支払った40,000円は、後日引き落とされます。

そのため、妻の口座には、

18,000円 + 40,000円 = 58,000円

を残す計算にしています。

実際のコードはこちらです。

wife_settlement_amount = (
    wife_budget
    - wife_personal_spent
    + wife_paid_total
)

ここでのwife_paid_totalは、妻が支払ったすべてのカテゴリの支出合計です。

今回の例では、個人費12,000円と共通費28,000円の両方が含まれます。

なお、アプリの計算では支払い方法を問わず、記録された支払額を使用しています。クレジットカードの引き落としに必要な金額だけを抽出しているわけではありません。

妻から夫への振込額を計算する

最後に、妻の収入からNISAの積立分と、妻の口座に残す金額を差し引きます。

例えば、

  • 妻の収入:300,000円
  • NISAの積立分:50,000円
  • 妻の口座に残す金額:58,000円

の場合です。

300,000円 − 50,000円 − 58,000円 = 192,000円

となり、夫への振込額は192,000円です。

実際のコードでは、次のように計算しています。

wife_transfer_to_husband = (
    wife_income_total
    - (nisa_wife_monthly_amount + wife_settlement_amount)
    if wife_income_registered
    else None
)

ここで使用するnisa_wife_monthly_amountは、設定画面で登録した妻の月額積立設定です。

また、妻の収入がまだ登録されていない場合は、振込額を計算せずNoneを返すようにしています。

これにより、収入未登録の状態で誤った振込額を表示しないようにしています。


清算が完了したかどうかも記録する

毎月の清算では、金額を計算するだけでなく、実際に清算を終えたかどうかも管理しています。

そこで、monthly_settlementsというテーブルを用意しました。

このテーブルには、対象の年月と清算完了日時を保存しています。

CREATE TABLE IF NOT EXISTS monthly_settlements (
    year_month TEXT PRIMARY KEY,
    completed_at TEXT NOT NULL
        DEFAULT (datetime('now', 'localtime'))
);

例えば、10月分の清算を完了すると、「2026-10」という年月と完了日時が記録されます。

また、清算日は設定から変更できるようにしています。

計算上の清算予定日は対象月の翌月に設定され、清算日を過ぎても未完了の場合は、アプリで確認できる仕組みです。

なお、清算完了として保存しているのは完了状態であり、計算結果を固定して保存しているわけではありません。

清算額自体は、支出や予算などの最新データから計算しています。


計算処理を独立させた理由

今回のアプリでは、データベースへの接続処理をdatabase.py、夫婦間の清算計算をsettlement_service.pyに分けています。

さらに、まとめ画面で必要なデータを返すAPIはrouters/summary.pyで管理しています。

処理の流れは次のようになっています。

まとめ画面
    ↓
FastAPI(summary.py)
    ↓
清算計算(settlement_service.py)
    ↓
SQLite(database.py)
    ↓
計算結果を画面に表示

こうして役割を分けておくことで、例えば清算方法を変更したいときには、計算処理を中心に修正できます。

また、画面の表示方法を変更するときも、データベースの構造まで変更する必要がありません。

お金の管理ルールを、画面の処理とは別に管理できることが、今回の設計で特に意識した部分です。


まとめ

今回は、SQLiteを使った家計簿のデータベース設計と、夫婦独自の清算処理について紹介しました。

今回の実装で意識したのは、次の3つです。

  • 支出・カテゴリ・支払者などのデータを別々のテーブルで管理する
  • 同じ支出データから予算管理と支払者別の集計を行う
  • 清算ルールを独立したPythonの関数にまとめる

特に、支出カテゴリと支払者を分けて記録することで、自分たちの生活に合った清算処理を実現できました。

また、データの構造と計算処理を分けておくことで、生活スタイルが変わった場合にも修正しやすくなっています。

アプリで何を管理したいのかを先に整理し、それに合わせてデータベースを設計すること。

今回の開発では、これがとても重要だと感じました。

次の記事では、PWAとIndexedDBを使って、通信できないときでも支出を記録し、オンライン復帰後にサーバーへ同期する仕組みについて紹介します。


関連記事

コメント

タイトルとURLをコピーしました