I have a database that contains a table of deposits (security deposits, pet deposits, etc) and in certain cases these deposits need to be reduced, i.e. someone purchases supplemental insurance. Should I have a table called say, alter_deposits, that will contain the conditions for reduction as well as the amount or is this something that belongs in the application code as business logic?
Thanks in advance!
What you need to determine is the likelihood that these conditions might change. Putting them in the database and wrapping a UI around them would make them easier to change, especially if there is some admin-type user who is not the maintaining programmer.
To me the rule of thumb to this kind of question is whether you would like to change it in the runtime without taking down the website.
If you would like your business rules to be updated without scheduling a downtime then you probably want to put it in the database, otherwise put it in code where you will have to schedule a down time and notify your users before updating the website code.
In cases like this I like to try and generalize the rules into as basic of a rule as possible. So you might have a flat reduction rule which takes x% off. Then you might have a rule which is based on conditions, so you could say take x% off when balance is greater then y.
After you have your rules generalized, I would implement the rules in business logic in their generic form, and in your database you might store the types of rules currently active, and their inputs.
And you can do this without scheduling downtime, dropping in a new rule, should be as simple as updating the DB, and if there is a new rule type you could just deploy the new piece of code. Of course this depends on your environment, I know in .net this should be preety straight forward.
I worked on a really fun rule based processing system, where we essentially built a binary tree of And & Or operands whose Operators would return true or false, if the tree returned true we did the action. This was all serialized to Xml, which let the storage mechanism be agnostic to the types of rules and their data requirements.
Edit
A rules based system can be very powerful. I've used them to help locate widgets spread out in thousands of different inventories and rank the best place to buy widgets based on any number of criteria. I've seen them used to do conquesting on parts, for example in the Automotive sector, the OEM's like to compete on AM parts but only in certain cases.
You might have define a superset of rules, and as the item passes down the chain, you might find other rules. A rule can consist of an Expression and an Action. The expression can be expressed as a Tree of sub-conditions which evaluate to true or false, when its true your action runs.
If your using .net you could actually build a fairly dynamic system using the Expression Tree's and dynamically creating lamba's which can represent the operands, and actions.
There are prebuilt rules engines which can help jumpstart things but I never found one cheap enough or basic enough for my needs.
An extension of this concept in my mind is workflow programming. So when a deposit comes in let's say if after your rules run the deposit results in a > 25%. And in this case you require an employee to approve this case. A workflow engine can encapsulate this type logic and express it in a more fluid manner then traditional coding. But with all things there are lots of downsides. Everytime I've started down the path of using a workflow engine, it ends up getting gutted in the end for a variety of reasons, but usually because our workflows are too simple to justify the cost of learning the engine.
You should consider this as a "Transaction" transactions are powerful in SQL (and only properly just implemented in MySQL recently), Even though you may be incrementing or decrementing the Transaction method provides you the ability to return the units of currency to the entities before the failure came.
Regardless I would manage all of the business logic in your Model (assuming you follow an MVC convention). You shouldn't have columns in your database that serve only to administrate other columns, unless it's a foreign key. Consider a transaction (I believe a transaction is a resource) table, with transaction_type and value.