search

bos21001 / familiasm-api

MIT License
0 stars 0 forks source link

[DOCS] Finances and Groups database tables desing #7

Closed bos21001 closed 1 year ago

bos21001 commented 1 year ago

This is my first good #personal_finances_db_tables_proposal

Should I make another change?

Personal Financial Manager.pdf

Personal Financial Manager
bos21001 commented 1 year ago

During a conversation with Rafa about personal finances, the idea of a family financial manager came up, and it became clear to me that a groups table is essential for this feature.

To simplify financial transactions, whether they are personal or shared with a group (such as family), I propose creating a finances table to store all financial information, including planned and unexpected expenses. This table will contain the essential information for each finance transaction.

To keep a history of each finance transaction, I suggest creating a finance_historys table. This table will record all updates and changes made to planned finances, allowing us to track each period of the finance and keep an accurate record of all financial activity.

Groups will be an optional feature, allowing users to either belong to or own a group. If a group_id is specified in a finance transaction, it will be related to a specific group and can be shared with all the users in that group. To store the list of group users, we'll create a group_users table. This way, we can easily manage group finances and track each user's financial activity within the group.

Bellow are some graphical and PHP Laravel representation:

Personal Financial Manager (2).pdf

image

Database Schema

finances

The finance transaction manual registry of each user (and of its groups)
Field Name Data Type Description Example
id int The finance id 1
user_id int The id (created by) from the user it belongs to 1
group_id int? The id from the group it belongs to 1
name varchar A brief name for the finance Apartment Rent
type varchar The type finance type debt or income debt
value float The value of the finance 1149.20
description varchar A detailed or not description for this finance Rent (1100) + IPTU tax (49.20)
repeats boolean If it repeats or not True
business_day_only boolean It it is business day only or not False
repeat_every int? The number of every repetition 1
repetition_period varchar The period to be repeated month
ends timestamp? The end date 2023-02-25 03:51:54.000
created_at timestamp The creation timestamp 2023-02-25 03:51:54.000
updated_at timestamp The last update timestamp 2023-02-25 03:51:54.000

finance_historys

The finance history update of each finance
Field Name Data Type Description Example
id int The finance history id 1
finance_id int The id from the finance that this history belongs to 1
user_id int The id (created by) from the user that this finance history belongs to 1
value float The updated value of the finance 90.80
date timestamp The actual date of the finance history 2023-02-25 03:51:54.000
action varchar The actual action, if it is awating for user action or it is done with it done or awaiting
description varchar Detailed description if the user wants to add one for the action I've been charged with extra taxes
created_at timestamp The creation timestamp 2023-02-25 03:51:54.000
updated_at timestamp The last update timestamp 2023-02-25 03:51:54.000

groups

The list of each group and its owner (user_id)
Field Name Data Type Description Example
id int The group id 1
user_id int The id from the user that this group belongs to (the owner) 1
name varchar The group name Família da Silva Mendes
created_at timestamp The creation timestamp 2023-02-25 03:51:54.000
updated_at timestamp The last update timestamp 2023-02-25 03:51:54.000

group_users

The relational list of each group's users
Field Name Data Type Description Example
group_id int The id from the group that the user belongs to 1
user_id int The id from the user that belongs to the group from the group_id 1
created_at timestamp The creation timestamp 2023-02-25 03:51:54.000
updated_at timestamp The last update timestamp 2023-02-25 03:51:54.000 
use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateFinanceTables extends Migration
{
    public function up()
    {
        Schema::create('finances', function (Blueprint $table) {
            $table->id();
            $table->integer('user_id');
            $table->integer('group_id')->nullable();
            $table->string('name');
            $table->float('value');
            $table->string('description')->nullable();
            $table->boolean('repeats');
            $table->boolean('business_day_only');
            $table->integer('repeat_every')->nullable();
            $table->string('repetition_period')->nullable();
            $table->timestamp('ends')->nullable();
            $table->timestamps();
        });

        Schema::create('finance_historys', function (Blueprint $table) {
            $table->id();
            $table->integer('finance_id');
            $table->integer('user_id');
            $table->float('value');
            $table->timestamp('date');
            $table->string('action');
            $table->string('description')->nullable();
            $table->timestamps();
        });

        Schema::create('groups', function (Blueprint $table) {
            $table->id();
            $table->integer('user_id');
            $table->string('name');
            $table->integer('description')->nullable();
            $table->timestamps();
        });

        Schema::create('group_users', function (Blueprint $table) {
            $table->integer('group_id');
            $table->integer('user_id');
            $table->timestamps();
        });
    }

    public function down()
    {
        Schema::dropIfExists('finances');
        Schema::dropIfExists('finance_historys');
        Schema::dropIfExists('groups');
        Schema::dropIfExists('group_has_users');
    }
}
use Illuminate\Database\Eloquent\Model;

class Finance extends Model
{
    protected $fillable = [
        'user_id', 'group_id', 'name', 'value', 'description', 'repeats', 'business_day_only', 'repeat_every',
        'repetition_period', 'ends',
    ];

    public function user()
    {
        return $this->belongsTo(User::class);
    }

    public function group()
    {
        return $this->belongsTo(Group::class);
    }

    public function financeHistory()
    {
        return $this->hasMany(FinanceHistory::class);
    }
}

class FinanceHistory extends Model
{
    protected $fillable = [
        'finance_id', 'user_id', 'value', 'date', 'action', 'description',
    ];

    public function finance()
    {
        return $this->belongsTo(Finance::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}

class Group extends Model
{
    protected $fillable = [
        'name', 'user_id', 'type', 'description',
    ];

    public function users()
    {
        return $this->belongsToMany(User::class)->using(GroupUser::class)->withTimestamps();
    }

     public function finances()
    {
        return $this->hasMany(Finance::class);
    }
}

class GroupUser extends Pivot
{
    protected $fillable = [
        'group_id', 'user_id'
    ];

    public function group()
    {
        return $this->belongsTo(Group::class);
    }

    public function user()
    {
        return $this->belongsTo(User::class);
    }
}
bos21001 commented 1 year ago

All columns will be set to nullable. Because all database logic, inserts will be fully managed by the application.

TODO:

refs: e857fa0