Hey all, not sure how to fully explain this. I am currently tracking loans with an excel spreadsheet, client info(first name, last name), loan info (orig amount, interest rate, payment), etc..

It is getting a little too much to maintain. I am looking into converting it to a database but can't seem to get my head around the setting up the structure.

The loans are amortized over different periods of months (60,120,360 etc) I need to run fairly simple reports - interest rates below 8%, loan balances, payments above $1,000.00, loan with terms shorter than 60 months etc, loans paid 25% of original amount etc...

Currently in the spreadsheet each customer has their own worksheet, so I will have to create tables:
Customers, Accounts, Loans, Transactions, etc..

One area I am struggling with is the separation of entities, trying to remember the rule KISS (keep it short and simple), should the loan table contain all the information, or should interest rates, payments, etc.. be in separate tables? I have looked at a database used in the automotive industry and had them separated? (due dates, int rate, loan amount, etc., not sure how that worked?

Another area that I am getting stuck is the amortization which is a calculation, always thought you shouldn't store calculated fields. Can or should that be calculated and stored into a table for each customer? Currently I amortize the loan and insert the table into the spreadsheet and manually check off payments made to date.

Any direction or ideas would be appreciated -

Hi,

You should start my creating the initial entities like customer, loan, payment, listing all the data that you need to store for each and going through normalization. When you get to 3rd Normal Form you should have a good model that you can implement. If you're not familiar with database normalization you should take some time getting familiar with it as it is the second step in database design after requirement gathering.

I can see that loan amortization is gonna be complex calculation. Are you planning on developing a front end application or will you be querying straight from the database? If you had a front end you could do that calculation there otherwise you probably will have to have user defined functions.