ERD for an Asset Finance System

[easante]

Hi all,

I need help in designing a database for an equipment finance system I want to implement as a project. This is how the system will work.

1. A client would make an application to a finance company for a loan to buy a machine. The loan will be recovered over a period of time to be mutually agreed upon.

2. The finance company does not keep stock of any machines except those that have been ordered for a client and awaiting delivery and also used machines that have been repossessed for non-payment.

3. The company charges interest and the interest rate can change anytime the central bank changes the lending rate to commercial banks. This can happen in the middle of the month, in which case two rates will apply in the month in question.

4. It is expected that a minimum payment would be made on a monthly basis, but the client can choose to pay more, which will reduce the outstanding capital.

5. In the event of non-payment, the machines can be repossessed and the finance company will determine the value of the machines by some method. If the current value is less than the debt, the cliet will be liable for the shortfall.

6. A customer can have more than one agreement at a time, and agreements can be consolidated into one agreement at any point in time.

7. Different interest rates apply to different clients.

I will be very grateful for any help I will get in this direction.

Thanks
Emmanuel

[timothybard]

This should give you a nudge in the right direction:

Client: ID (PK)
Application: ID (PK), Client ID (FK), Machine, Finance Company
Finance Company: ID (PK)
Loan: ID (PK), Application ID (FK), Approved Loan Amount, Minimum Payment
Machine: ID (PK)
Machine Tracking: ID (PK), Machine ID (FK), Ordered, received, delevered, re-possessed
Interest Rate: ID (PK), Loan ID (FK), Start Date, End Date, Rate
Transactions: ID (PK), Loan ID (FK), Date, Amount
Machine Value: ID (PK), Machine ID (FK), Amount, Date
Agreements: ID (PK)
Agreements/Loans: ID (PK), Agreements ID (FK), Loans ID (FK)

It's nowhere near complete; there's plenty of room for additional information to be recorded, but it should give you a good start.

[easante]

This should give you a nudge in the right direction:

Client: ID (PK)
Application: ID (PK), Client ID (FK), Machine, Finance Company
Finance Company: ID (PK)
Loan: ID (PK), Application ID (FK), Approved Loan Amount, Minimum Payment
Machine: ID (PK)
Machine Tracking: ID (PK), Machine ID (FK), Ordered, received, delevered, re-possessed
Interest Rate: ID (PK), Loan ID (FK), Start Date, End Date, Rate
Transactions: ID (PK), Loan ID (FK), Date, Amount
Machine Value: ID (PK), Machine ID (FK), Amount, Date
Agreements: ID (PK)
Agreements/Loans: ID (PK), Agreements ID (FK), Loans ID (FK)

It's nowhere near complete; there's plenty of room for additional information to be recorded, but it should give you a good start.

Thanks Timothy for the reply.

It looks from the design that the relationship between Application and Loan is 1-to-1; is it correct? Also, is the transaction table going to be the table for recording the payments? I do not see how Agreement and Agreements/Loans tables will be used.

I would have that thought that the Application table should rather be Agreement table.

[timothybard]

You are correct: There is a 1-to-1 relationship between Application and Loan. However, every application does not need a corresponding loan. Applications that have not been processed yet and applications that were rejected will not have a loan associated with them.

The Agreement/Loans table is to track which loans are on each agreement. After a person is approved for a loan, you will create a Loan record. You will then create an Agreement record. Lastly, you would create a record in the Agreements/Loans table to show that the new Loan is a part of the new agreement. To combine two agreements, you would create a third agreement and then add two records to the Agreements/Loans table: the Agreement ID(FK) would be the ID of the new agreement for both records and the Loans ID(FK) would be the ID of the loans that need to be moved to the new Agreement.

Please note that one Loan may appear several times in the Agreements/Loans table. This is to keep a history of the Agreements it used to be on. You may want to add a flag to the Agreements/Loans table to show which record is the "current" record for each Loan.

Another solution is to add an Agreements ID (FK) field to the Loans table. To combine two loans into one agreement, simply enter the same Agreement ID into each Loan. While this method is easier to understand and use, you won't have a history of the Agreements the loan used to be on.

[jaasaria]

much better to design like this that will make more easy to view/track the history of the applicant for all of his payments for the particular loan.

plsss see my attachment..

every payment(FK) to loan(FK)
and every loan(FK) to application(FK)

[timothybard]

jaasaria,

In the design I provided, I had a transaction entity, which would include payments on the loan. The transaction entity has a foreign key to the Loan the transaction applied to.

As far as the loan-to-application relationship, when a person applies for a loan, they are either accepted or rejected. If they are rejected, then they fill out another application and try again. A person usually doesn't get to apply for more than one loan on a single application. Of course, the database can be designed to allow one application to several loans, but I'm not sure many places would allow that.

[jaasaria]

yahh... i think were in the same side. it depends only for the company rules/places.
as what i attach, also agree timothybard replied.

maybe this thread was solved already.

^_^