railsでポイントサービス(送る、受取る、貯蓄）のデータベース設計について悩んでいます。

Question

###前提・実現したいこと
ruby(rails)で単一の独自ポイントを扱う簡易版銀行みたいなものをつくってみたいと考えています。
データベース設計をやってみたのですが、
「この設計でいいのか」わからず悩んでいます。

イメージとして、こんな感じです。
・ユーザーは一つだけしか口座を持てない。
・ユーザー同士でポイントを支払ったり（送ったり）、受け取ったりすることができる。
・ユーザーは今までの取引記録を確認できる。
取引記録には以下の項目が記録されている。
入出金番号、日付、取引内容（取引相手の名前）、支払金（出金）、受取金（入金）、残高

このイメージだとどうゆうデータベース設計になるのか
教えて頂ければ幸いです。
よろしくお願いいたします。

###試しに考えたＤＢ設計
素人なりに以下のような設計を考えてみました。

ユーザーテーブル（マスタ系）
・ユーザーid（主キー）
・ユーザー名
・パスワード

口座テーブル（マスタ系）
・口座番号（主キー）
・ユーザーid（ＦＫ）
・残高

取引関係テーブル（イベント系？）
・取引関係id
・支払人口座番号(FK?)
・受取人口座番号(FK?)

入出金テーブル（イベント系）
・入出金番号（主キー）
・年月日時刻
・入金額
・出金額
・残高
・支払人口座番号(FK)
・受取人口座番号(FK)

関連性
ユーザーと口座は、1:1
口座と取引関係は、1:多
取引関係と入出金は、1:多
口座と入出金は、1:多

Answer 1

1. 口座テーブル（マスタ系）に残高を持つのであれば、入出金テーブル（イベント系）には残高はいらない気がしますがどうでしょうか。何か意図がありますでしょうか。

2. 入出金テーブル（イベント系）の入金額と出勤額はどちらにも値が入る想定ですか？口座の履歴ログなので、入金で一回、出金で一回の取引とした方が良いと思いました。

3.入出金テーブル（イベント系）の残高には入金側、出金側のどちらの金額が入るか一見してわかりません。ある時点の残高を知りたい場合は、ある時点からある時点までの口座テーブルの残高から入出金テーブルの取引額の合計を足せばOKですのでなくて良さそうな気がします。支払人口座番号と受取人口座番号は取引テーブルが持ってるので、これらも入出金テーブルからは削ります。

入金カラムと出金カラムは取引額カラムなどとして、AさんがBさんに対して出金する場合

|口座|取引額|取引ID|内容|日時
|:--|:--:|--:|
|Aさん口座ID|-1000|A1000|出金|yyyy-MM-DD hh:mm:ss
|Bさん口座ID|+1000|A1000|入金|yyyy-MM-DD hh:mm:ss

|取引ID|出金口座|入金口座|取引額|日時
|:--|:--:|--:|
|A1000|Aさん口座ID|Bさん口座ID|1000|yyyy-MM-DD hh:mm:ss

としてはどうでしょうか。
(取引額は入出金テーブルと、取引履歴テーブルの両方に入れてあります。)

Comments

[zendendo]

回答して頂きありがとうございます。

１と２のアドバイスについてですが、
イベント系のテーブルはどう設定したらいいか理解が浅くて
「通帳の項目をそのまま入出金テーブルのカラムにすればいいのでは？」
「そうすれば、通帳の様な記録ができるのではないか」
という発想で、
残高を入れたり（そうすれば、通帳みたくその都度の残高がわかるのではないか）、
入金額と出金額を入れたり（通帳みたく片方に値が入って、もう片方は何も入らない）
していました。

アドバイス通りにやってみたいと思います。

また蒸し返し的質問で申し訳ないですが、
最終的なエンティティと関連性については、
以下の通りで大丈夫ということでよろしいでしょうか？

ユーザーテーブル（マスタ系）
・ユーザーid（主キー）
・ユーザー名
・パスワード

口座テーブル（マスタ系）
・口座番号（主キー）
・ユーザーid（ＦＫ）
・残高

取引テーブル（イベント系）
・取引id
・出金口座id
・入金口座id
・取引額
・日時

入出金テーブル（イベント系）
・入出金番号（主キー）
・取引id(FK)
・取引額
・内容（出金か入金か）
・日時

関連性
ユーザーと口座は、1:1
口座と取引は、1:多
取引と入出金は、1:多
口座と入出金は、1:多

[mtdsnsk]

はい、大丈夫だと思います。

railsのActiverecordモデルに従うと全てのカラムに主キー(id)とupdated_atとcreated_atが追加される形になると思いますので、それらと重複するものは排除して良いでしょう。

また、サービスにどのような機能を追加するかでテーブル定義は結構変わるので、実際に想定される機能を作りながら試行錯誤をしていくことになると思います。

あくまで自分ならこう作るだろうという想定なので、実際に動かしてみると思った機能を実現できないor実現するための実装コストが高い、SQLのパフォーマンスが悪いなど、いろいろな課題が出てくると思います。そういった試行錯誤がまさにDB設計なので、楽しんでいただければと思います！

[zendendo]

ＤＢ設計って想像以上に奥が深いものなのですね・・・。
これからどんどん試行錯誤していこうかと思います。
mtdsnskさん
どうもありがとうございました！

[mtdsnsk]

ユーザーモデルや入出金、チャットシステムなどに関しては、ある程度知見が集約されています。

ユーザーモデルならdeviseというgemが有名で、実際に導入して内部でどういうデータ持ってるかを学ぶと良い勉強になります。

github上などでmigrationなどのキーワードで検索すると他人のdb設計も垣間見れてよいです

[zendendo]

さっそく見てみましたが、githubってすごいですね。
本などの教科書的ではない他者の生々しいdb設計を見れて、とても刺激的です。