テーブル設計について

前提

とある管理画面を作成するのですが、ハタと「どうテーブル設計すればいいのか？」と悩んでおり、ご助言をいただきたいです。

実現したいこと

一人のユーザーに対し、属性や家計情報を登録します。
属性は、氏名や性別など一般的なものです。
家計情報がネックとなっています。
家計情報は月ごとの収入と支出です。
収入と支出は可変個登録です。
最終的に、ユーザーごとの支出の集計表を出力します。

検討内容

登録するものの個数が固定であれば、ユーザーテーブルに登録するもののカラムを用意して値を入れればいいのですが、可変個登録のため、テーブルは分ける必要があると思っています。

ユーザー
ID
氏名
性別
年齢

収入
ユーザーID
収入名
金額

支出
ユーザーID
支出名
金額

のようにテーブルを作成したとすると、出来上がるデータは

ID	氏名	性別	年齢
1	ユーザーA	男	30
2	ユーザーB	女	27

ユーザーID	収入名	金額
1	月収	300000
1	臨時収入A	20000
2	月収	250000
2	臨時収入A	15000
2	臨時収入B	10000

ユーザーID	支出名	金額
1	家賃	60000
1	光熱費	15000
1	交際費	20000
1	ローン	30000
2	家賃	60000
2	光熱費	15000
2	交際費	20000

のようなものになります。

発生している問題

支出の集計表はユーザーごとに分かれおり、


月収
臨時収入A
収入計
家賃
光熱費
交際費
ローン
支出計

という形です。

実際はもう少し複雑ですが、要点だけに絞るため簡略化しています。

どうSQLを組んだものかと悩んでおり、テーブル構成が妥当かどうかを含め、アドバイスを頂ければと思います。Laravelを使用します。

よろしくお願いします。

m.ts10806

2022/12/13 23:17

あまりLaravelやPHPに特化しない問題と思います。質問タグ調整してください。 https://teratail.com/tags/%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9%E8%A8%AD%E8%A8%88

avantgarden

2022/12/14 00:19

ご指摘ありがとうございます。質問タグを調整しました。

行動規範の内容に同意します

回答3件

収入・支出テーブルは支出をマイナス表示にすれば１つにまとめられそうですね
あとは日付を埋め込んで、可能であれば集計単位である月単位の生成列などを用意しておくと効果的です

投稿2022/12/13 10:27

yambejp

総合スコア114835

avantgarden

2022/12/14 00:22

ありがとうございます。 DB側で集計情報を持つことも検討いたします。

行動規範の内容に同意します

ユーザID:1 のその月の収入を出すなら、
　select sum(金額) from 収入 where ユーザID=1 and 日付=...
ユーザID:1 のその月の支出を出すなら、
　select sum(金額) from 支出 where ユーザID=1 and 日付=...
ユーザID:1 のその月の収支を出すなら、
　↑の 収入 から 支出 を引く
ユーザID:1 のその月の収入を収入名単位で出すなら、
　select sum(金額), 収入名 from 収入 where ユーザID=1 and 日付=... group by 収入名
その月の家賃がゼロでも 家賃: 0 と出したいなら、
　select sum(支出.金額), 支出集計表.支出名 from 支出集計表
　　left join 支出 on 支出.支出名=支出集計表.支出名
   where 支出.ユーザID=1 and 支出.日付=...
 group by 支出集計表.支出名