Good question when it comes to decide which part of the program to be executed where. I had a similar question when I was coding an application using Classic ASP and MS SQL Server.
Before I answer your query, I would like to note that a web application has minimum three tiers:
- the presentation tier, e.g., the client's web browser
- an application tier - where your PHP or JSP or ASP servers run
- and the database tier- where the data resides.
It is common sense that whatever data validation that is possible to be carried out at the presentation tier, we put it there. E.g., checking if a certain form field is empty.
It is interesting when the application has to be developed at the other two tiers - the application and database tiers. Many bad programmers code in such a way that there are many round trips between the web server and the DB server for very little things. We should consider coding stored procedures in the database at these times.
About the points you have mentioned, I would like to answer:
-
Requests speed.
The requests made to your web server are processed faster as there are less round-trips between the web server and the database server. The difference is particularly notable when your application grows very complex and data grows too large, in terms of multiple hundred TBs. The processing load is transferred from your web server to your DB server, hence your web server is more available for your client requests.
-
Security aspects. (program and database).
The security offered to your program is now at two levels - at the web server level and database level. You can put restrictions on the communications between these two to make sure your program doesn't collapse and data is intact.
-
Scalability.
The web server code and database server code is now at two different tiers. Both the tiers can scale independently as per requirements. Read here for exactly what is meant by scalability.
-
Profitability.
I don't know how to answer this, but a thought just came across my mind: If you're paying a database server and letting it sit idle while the web server is overloaded, what's the point? If I'm paying for a database server, I'd make full use of it by giving it some of the processing load that my web server has.
-
When it is convenient to use and when not.
It is most convenient to use when there are too many round-trips between the web and database server. I might be mentioning this again and again but this issue turns out to be a big headache.
When it is not convenient? Well, in simple cases where a single SQL statement does everything necessary.
-
Others good and bad points.
- You must be sure about the communication between these two tiers - what is passed and what is received. Keep everything well documented as any mistake would hamper both the tiers.
