I am an enterprise computing newbie. Don't know the right forum on the Net, so am taking the liberty to post this query of mine here... on the necessarily correct assumption that a lot of smart and experienced developers:
a) will be hanging in here,
and
b) will be able to take a moment to share their opinion.
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.
1. Yes, you can. However, there will be tradeoffs. One important piece of sharing business logic involves sharing instances of running code, not just the logic itself. It's tough to implement a singleton in a set of stored procs. I'd recommend that you leave stored procs for the kinds of logic that you might code into a web service: transactional concerns that have a complicated set of steps.
2. Yes, that's a popular approach, and for good reason: the objects you create against the persistence store are sharable at the instance level.
3. It will scale less-well than an architecture with a solid middle tier, but it's impossible to put a user-count on it without knowing much much more. If you pick a commercial RDBS like Oracle or Sybase (and many people don't like to do that), you will find that there are a lot of expensive but effective scaling mechanisms to handle this. It's usually cheaper to invest in a decent middle tier, a caching server, and scale that way.
4. That's not necessarily true. Portability is nice, but there are many other reasons to pull the business logic out of the database. For example, it's hard (but not impossible) to implement an event-driven system where transactions will fire off other business events downstream when you're using a stored procedure set as your logic layer. You can do it, but you end up with triggers or batch jobs that reduce the real-time processing capabilities of the whole architecture.
5. P/L SQL is pretty powerful, but I still have my logic in objects rather than in Oracle. I can introduce caching and other sscaling strategies easier this way.
Though I use and like Oracle, I'd steer you away from it unless you have a really good reason to buy it. It takes a lot more care-and-feeding than MySQL, the licensing model becomes oppressive when you start to need to cluster the DB, and experts in the area are expensive.
Hibernate is a good framework. Why are you resisting the use of it? If you want to go heavy on the stored procedures, check out the iBatis framework. It's really good at ORM and stored procs.