Ask HN: Can I use the RDBMS as an application server?
1. Assuming portability of SQL and Stored Procedures is the least of my concerns (because, let's say, I want to -- by design -- support a single RDBMS vendor only), can I, in such a case, use an RDBMS (such as MySql) as the application server, with all the biz logic coded in the SPs?
2. The other popular approach these days apparently is (or, at least has been so... for, well, the last several years): 2.1 to use an RDBMS as a mere persistence mechanism (ignoring completely the 'R' in the term 'RDBMS'), 2.2 do a Relational-to-Object mapping (with a tool like Hibernate), 2.3 code all your business logic against these 'objects' (reside inside an App Server like JBoss)!
3. If I follow approach #1, how will my application scale as the number of users / load increase? Via clustering of the RDBMS server? Even with clustering, will approach #1 turn my application into a 2-tier client-server architecture which apparently got rejected/superseded by N-tier architecture by mid-90s itself? I've heard that client-server application architectures don't scale beyond a 100 concurrent users or so (not sure if this is true)! Assume that this is in context of a distributed, banking application... let's say for Citibank, and that they have hired (an enterprise dud such as) me to be their chief software architect :-) and that they have the time, money, and the resources for a full redesign/rewrite from scratch!
4. Based on what I know so far, I think approach #2 will be beneficial, if and only if either of these two hold: 4.1 the biz logic must be independent of the RDBMS vendor; and 4.2 the biz logic requires sophisticated algorithms for which SQL (even with SPs) is not the best language... thus forcing you to import the data from the RDBMS into a more convenient, 'object' environment of a general purpose, Turing-complete language such as Java/C++.
5. If let's say, 4.2 does not apply to my case, can point 4.1 be addressed via some source to source translation tools, that 5.1 are already out there, or 5.2 will be there in near future, or 5.3 can be hand-coded, inhouse without necessarily an advanced, guru-like expertise in compilers?
Basically, I'm finding myself overwhelmed with the tools, technologies, frameworks that are springing up by gazillions ... to the point where it's becoming humanly impossible for me to evaluate them, with every single one of their pros and cons fully understood.
Based on what I know and understand so far, this is what I probably would do: a) Use MySql because its FOSS; (or, use Oracle... I hear it's very powerful, rich in features). b) Make full use of -- nay, exploit -- the power of SPs, the relational paradigm, the 'R' in the RDBMS. c) For any sophisticated algorithms for which SQL/SP appears less than ideal... use JDBC (with parametrized queries) to convert R to O, and then exploit the power of OO/Java. d) Use JSP/Servlets/Tomcat for presentation/front end and for the sophisticated-logic coding of the preceding item (c). e) Use clustering at the dbms server, load balancing at Tomcat/Apache level.
Can't think of anything better at this point. So I humbly ask for your kind advice.