Mortgage Calculator Database

SQL Server Compact

In the last post I gave a basic overview of my reasons and goals for Mortgage Calculator.

I love Microsoft. In particular I love how much time and effort they spend making the developer's life easier.

When I do anything outside my current job I default to .NET and C#.

While there are many capable database technologies out there, I was most certainly going to settle on something Redmond has implemented.

For Mortgage Calculator, I wanted something simple, light and capable for the database back end.

Aside from SQL Server, MS has Access.

SQL Server itself comes in a variety different flavors. The 2 most applicable being:

• SQL Server Express
• SQL Server Compact

MS Access is out, because, well it's Access. Also, I was keen on using LINQ to SQL.

SQL Server Express is more then capable. However, in order to use Express in your application you need to embed the SQL Server Express install into your install. This might be acceptable but I would just prefer to avoid it.

SQL Server Compact is file based. The beauty of which is that installing it to a user's machine involves adding the .sdf file and a few dlls for communicating with the file.

Database Table(s)

Because the concept of Mortgage Calculator is rather simple the information that needs to be stored is also simple.

Remember from the first post we want to keep track of the following values:

• Principal
• Interest
• Extra Payments
• Balance
• Home Value
• Tax
• Insurance
• Association Dues
• Maintenance
• Total Monthly Cost
• Equity
• Loan To Value
• Rental Income
• Cash Flow
• Tax Appreciation
• Insurance Appreciation
• Association Dues Appreciation
• Maintenance Appreciation
• Rent Appreciation
• Home Value Appreciation

Again, due to the simplicity of the app it is assumed there is only one user per install (potential future improvement). All we are left with is individual Mortgages that need to be amortized. Therefore we have 1 table.

Lets create it.

Numeric to Decimal

Because float and double values are not exact (Greg Dolley has a good write up on the Double vs Decimal issue) and it's accepted practice, we are going to be typing our money and interest rates in decimal in C#.

Decimal's counter part in SQL Server Compact is numeric.

Notice we have 2 different numeric types:

1. numeric(18,2)
2. numeric(18,5)

The first number is Precision and determines the number of digits in the value. The second is Scale and determines the number digits to the right of the decimal place.

So our values become something like the following:

1,234,567,890,123,456.78
1,234,567,890,123.45678

Because those values can become large and using decimals can be 20x more slow then doubles or floats we have to be careful to limit the input and processing on the machine.

In a future release I will have to update the interest rates to have more scale.

Currently, rounding is 5 decimal places which means rounding can result in:

• $100,000,000 x 0.000009 = $900, $900 / 12 = $75

This means someone lost $75 a month. We want to aim for less than $0.01 a month.

• $100,000,000 x 0.0000000009 = $0.09, $0.09 / 12 = $0.0075

Now someone is only losing $0.09 a year. 18 places means the interest rate can be 1,234,567.0000000009%. We could cap the interest rates at 1000% and feel comfortable that anything over that is for theoretical purposes and outside the scope of this project.

Show - Expand

There are several "Show" and "Expand" bit/boolean values in the table. These deal with preserving GUI state for different Mortgages and will be covered in a future post.

Null or Not Null

The only columns that are not allowed to be null are the Key, Name and the values necessary to do a basic Amortization.

• Key
• Name
• Principal
• Interest Rate
• Term

For everything else we would prefer not to enforce updating information if we don't have to.

In the next post we will be exploring more of the xmal side of our Navigation and Home Page.


Download the Code Here