how much work should we do in the database?

Question

how much work should we do in the database? Ok I'm really confused as to exactly how much "work" should be done IN the database, and how much work had to be done instead at the application level?

I mean I'm not talking about obvious stuff like we should convert strings into SHA2 hashes at the application level instead of the database level..

But rather stuff that are more blur, including, but not limited to "should we retrieve the data for 4 column and do a uppercase/concatenation at the application level, or should we do those stuff at the database level and send the calculated result to the application level?

And if you could list any more other examples it would be great.

Answer 1

It really depends on what you need.
I like to do my business logic in the database, other people are religously against that.

You can use triggers and stored procedures/functions in SQL.

Links for MySQL:
http://dev.mysql.com/doc/refman/5.5/en/triggers.html
http://www.mysqltutorial.org/introduction-to-sql-stored-procedures.aspx
http://dev.mysql.com/doc/refman/5.5/en/stored-routines.html

My reasons for doing business logic in triggers and stored proces

Note that I'm not talking about bending the database structure towards the business logic, I'm talking about putting the business logic in triggers and stored procedures.

1. It centralizes your logic, the database is a central place, everything has to go through it. If you have multiple insert/update/delete points in your app (or you have multiple apps) you'll need to do the checks multiple times, if you do it in the database you only have to do the checks in one place.
2. It simplifies the application e.g., you can just add a member, the database will figure out if the member is already known and take the appopriate action.
3. It hides the internals of your database from the application, if you do all your logic in the application you will need intricate knowledge of your database in the application. If you use database code (triggers/procs) to hide that, you don't need to know every database detail in your app.
4. It makes it easier to restucture your database If you have the logic in your database, you can just change a tablelayout, replace the old table with a blackhole table, put a trigger on that and let the trigger do the updates to the new table, your app does not even need to know the database has changed, this allows legacy apps to keep working unchanged, whilst new apps can use the improved database layout.
5. Some things are easier in SQL
6. Some things work faster in SQL
7. I don't like to use (lots of and/or complicated) SQL code in my application, I like to put SQL code in a stored procedure/function and try to only put simple queries in my application code, that way I can just write code that explains what I mean in my application and let the database layer do the heavy lifting.

Some people disagree strongly with this, but this approach works well for me and has simplified debugging and maintenance of my applications a lot.