Hi,
I am working on a project with a friend of mine and we are having some trouble coming up with a nice database design. The goal is creating a site which lets customers compare borrowing rates from different mortgage companies. We have a reasonable design for just about every `easy' part, but are having trouble coming up with a smart way to handle `additional conditions'. Lenders have some conditions that make lending more expensive depending upon several factors. For example, if the mortgage amount is more than 70%, the interest rate will be .25% higher. These factors vary between banks and also over time, i.e. banks change them every quarter or so.
I guess the easiest solution to this problem would be to put these checks into the application logic, however I think that hardcoded values are hard to maintain, therefore would prefer some form of DB-based solution. My best attempt at this problem would be a table that parametizes conditions.
Let's say that we can abstract conditions into this form:
<Conditional Value> <Logical Test> <Reference Value>
if true
return <Modification Value> <Modifier>
else
return <Modification Value>
I was thinking to create a table that the following six colums in addition to an artificial key and a foreign key to the lender:
[*] <Conditional Value>
[*] <Logical Test>
[*] <Reference Value>
[*] <Modification Value>
[*] <Modifier>
Then I would create a stored procedure that selects all conditions from a particular lender and executes them one after another in the pattern shown above.
While I am sure that I could put this together and get it to work, I am not particularly happy with it, since it appears somewhat hacked together to me. What do you think?