Database Design – Best Way to Keep Monthly Records of Loans

database-design

I'm trying to make a database to keep records of the amounts payed on a loan on a monthly basis.

I already have tables for the clients, accounts, products and loans:

create table product(
    id serial not null primary key,
    name varchar(50) not null,
    internal_code int not null
);

create table client(
    id serial not null primary key,
    name varchar(100) not null,
    number bigint not null

);

create table account(
    id serial not null primary key,
    number bigint not null,
    id_cliente int references client(id) not null
);

create table loan(
    id serial not null primary key,
    id_product int references product(id) not null,
    id_account int references account(id) not null,
    date_registry date not null,
    date_due date,
    amount_debt decimal not null
);

Every mouth a new record will be inserted in the loan table with a new amount_debt and a new date_registry (the date the client had payed he loan in that month), does it make sense to have a table with just those records having loan.id as FK on that new table?

Best Answer

Real world financial transactions do not follow simple assumptions such as "the client will make one payment per month".

Make a TRANSACTION table that records money coming in and money going out along with the details of that transaction, such as where the money came from (which client), where the money went to (which loan), and importantly when this happened (the date). You might want to consider adding a transaction type to provide extra information, with values like "loan", "payment", "adjustment" etc.

Related Question