1

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?

3

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.

  • Thank you for the advice. But the problem that I'm facing is just about a history of loans. The database will only be updated on the first day of the month. Keeping a record for each account on that date. – cap7 Mar 19 '15 at 11:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.