Implementing Loan system on DB2

Question

I need to implement a database design for a loan system.

We have 3 types of loans, and every loan have at least one subtype. For example:

Loan A
1. type A1
2. type A2
Loan B
1. loan B1
2. loan B2

Every subtype has different data that is needed in order to approve the loan. They don't have any common question, however, I need to access all the types from a single table, for some requirements.

What I tried was to create a "LoanType" table, and use an XML field to store the parameters for that particular sub-type. For example, for loan A:

<type name="A1">
    <parameterName>P1</parameterName>
</type>

Although I can access all the tags in the XML based on the name (I'm using DB2 as DBMS, and it has support for XML fields) I don't now how to populate the data from it

Another approach that seems interesting is to use one of this approaches, particularly Class Table Inheritance, but I think that is not a good approach for this.

Any help will be appreciated.

Regards

@ZoharPeled thanks for the reply. However i need to generate an XML file with all the loans for a client. With that approach i have to do multiple queries right? — Eduardo Gamboa Ureña, Jul 07 '15 at 12:39
not necessarily. I don't have any experience with db2 but I guess it supports joins. you should be able to write a single query that will select data from all the loan tables using the main base table and left joins. — Zohar Peled, Jul 07 '15 at 12:45
@ZoharPeled can you put your comment as an answer? The approach and the query worked perfectly for my needs — Eduardo Gamboa Ureña, Jul 08 '15 at 02:40

score 0 · Accepted Answer · answered Jul 08 '15 at 05:27

This is a classic case of table inheritance.
Use one table as the base table for all loans. This table should hold a column that will be the primary key and any columns that are shared between all loans (I'm guessing all loans should at least have some date columns such as create date, loan date etc.). All loans should have a one-to-one relationship with this base table.

To select all loans all you have to do is select from the base table with left joins to all concrete loan tables.

You might want to implement an instead of insert trigger on each concrete loan table that will insert a record to the base table and get it's id, and then insert the other details into the concrete loan table.

You can also create a view for each concrete loan that will select it's data and the base table data so that your database client can be agnostic about the database implementation all together.

Implementing Loan system on DB2

1 Answers1