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.

/SD

Probably not. Stored Procedures are not meant for display,
presentation, stat work, etc. Pick the right tool for each job. Your
application would have to be damn dumb for that to work.

But you would be surprised how much of the simpler business rules can
be put into CHECK() constraints, CREATE ASSERTION(), etc.

This lets anyone who can get to the DB screw up everything. Overhead
is also a problem.

RDBMS is just for data storage and data integrity. It is not a
magical all-purpose tool.

What is the database? DB2 will scale easier than SQL Server. But you
want Teradata or SAND or WX2 for Petabytes.

Not true.

Actually, I use stat packages a lot ...analytics and that kind of
thing.

Yep! Ain't science grand?

Skip Oracle; MySQL can sit on a lot of different storage engines.
Look at DB2.

That is all I have, but I am a very narrow SQL guy and stopped doing
apps decades ago.

Probably not. Stored Procedures are not meant for display,
presentation, stat work, etc. Pick the right tool for each job. Your
application would have to be damn dumb for that to work.

But you would be surprised how much of the simpler business rules can
be put into CHECK() constraints, CREATE ASSERTION(), etc.

This lets anyone who can get to the DB screw up everything. Overhead
is also a problem.

RDBMS is just for data storage and data integrity. It is not a
magical all-purpose tool.

What is the database? DB2 will scale easier than SQL Server. But you
want Teradata or SAND or WX2 for Petabytes.

Not true.

Actually, I use stat packages a lot ...analytics and that kind of
thing.

Yep! Ain't science grand?

Skip Oracle; MySQL can sit on a lot of different storage engines.
Look at DB2.

That is all I have, but I am a very narrow SQL guy and stopped doing
apps decades ago.

Probably not. Stored Procedures are not meant for display,
presentation, stat work, etc. Pick the right tool for each job. Your
application would have to be damn dumb for that to work.

But you would be surprised how much of the simpler business rules can
be put into CHECK() constraints, CREATE ASSERTION(), etc.

This lets anyone who can get to the DB screw up everything. Overhead
is also a problem.

RDBMS is just for data storage and data integrity. It is not a
magical all-purpose tool.

What is the database? DB2 will scale easier than SQL Server. But you
want Teradata or SAND or WX2 for Petabytes.

Not true.

Actually, I use stat packages a lot ...analytics and that kind of
thing.

Yep! Ain't science grand?

Skip Oracle; MySQL can sit on a lot of different storage engines.
Look at DB2.

That is all I have, but I am a very narrow SQL guy and stopped doing
apps decades ago.

On 4 Aug., 18:46, SomeDeveloper <somedevelo... (AT) gmail (DOT) com> wrote:
You should really think about the RDBMS possibilities and offered
features. Don't know much about MySQL for example, but as SPs were
added there just in most recent version (probably 2 versions) I doubt
their offered functionality is big. On the other hand of course the
question is what are your requirements. If offered functionality meet
your requirements then this is perfect solution.

Yeahh that's the dreadful approach to DBMSes as bit bucket. This to my
mind is completely and absolutely dangerous approach and leads us to
70ties or probably 60ties when everything was stored in flat
(formatted) text files. No need for DBMSes then. Just flat files would
suffice. Why you'd spend DBMS licence cost using it just like bit
waste?
A couple of links here:
Do you have data waste or data base? at http://www.gplivna.eu/papers/data_wa..._data_base.htm
http://asktom.oracle.com/pls/asktom/...:6692296628899

Definitely not true.

approach #2 won't necessary ask for biz logic must be independent of
the RDBMS vendor. All big RDBMSES have invested hundreds of man years
to develop their functionality. If you choose to use only the common
features you'll get a very small subset of them. So effieciently
throwing away most of your money you paid for licences. Do you buy a
flat and then don't use kitchen just because it is not the same as
your friend have? You adapt to your kitchen and use it. And when you
go to your friend you use his/her kitchen just like he/she wants and
just like ktchen environment allows.

Be cautious of that. Data resides in database and if you try to do
everything out of it you'll need to invent many already invented
things, you'll need to send much data from client to db, it all husrts
performance and scalability.

I'm personally using Oracle, but unlike the previous poster I won't
say everybody should use it just because I use it. For a large banking
app as you said before I doubt I'd choose MySQL, but it doesn't mean
it cannot be done.
I have written a short paper How to choose database at
http://www.gplivna.eu/papers/choose_database.htm with some criteria to
think about. Remember that you have to choose it, you and your
colleagues will work with it, and this is important step, not to be
later much frustrated. Don't choose it blindly because someone likes
anyone. Choose it based on YOUR requirements, skills and available
skilled human resources.

Cannot see any reason why not use the power of RDBMS, especially if
you paid much money for its licenece and are not blinded by RDBMS
indepnedent app.

We have craeted succesfully apps on Oracle both putting almost all
business logic in Stored procs and php as presentation environment as
well as Oracle as mostly relational data store and .NET for business
logic. Both variants work and definitely are doable.

Gints Plivna
http://www.gplivna.eu

On 4 Aug., 18:46, SomeDeveloper <somedevelo... (AT) gmail (DOT) com> wrote:
You should really think about the RDBMS possibilities and offered
features. Don't know much about MySQL for example, but as SPs were
added there just in most recent version (probably 2 versions) I doubt
their offered functionality is big. On the other hand of course the
question is what are your requirements. If offered functionality meet
your requirements then this is perfect solution.

Yeahh that's the dreadful approach to DBMSes as bit bucket. This to my
mind is completely and absolutely dangerous approach and leads us to
70ties or probably 60ties when everything was stored in flat
(formatted) text files. No need for DBMSes then. Just flat files would
suffice. Why you'd spend DBMS licence cost using it just like bit
waste?
A couple of links here:
Do you have data waste or data base? at http://www.gplivna.eu/papers/data_wa..._data_base.htm
http://asktom.oracle.com/pls/asktom/...:6692296628899

Definitely not true.

approach #2 won't necessary ask for biz logic must be independent of
the RDBMS vendor. All big RDBMSES have invested hundreds of man years
to develop their functionality. If you choose to use only the common
features you'll get a very small subset of them. So effieciently
throwing away most of your money you paid for licences. Do you buy a
flat and then don't use kitchen just because it is not the same as
your friend have? You adapt to your kitchen and use it. And when you
go to your friend you use his/her kitchen just like he/she wants and
just like ktchen environment allows.

Be cautious of that. Data resides in database and if you try to do
everything out of it you'll need to invent many already invented
things, you'll need to send much data from client to db, it all husrts
performance and scalability.

I'm personally using Oracle, but unlike the previous poster I won't
say everybody should use it just because I use it. For a large banking
app as you said before I doubt I'd choose MySQL, but it doesn't mean
it cannot be done.
I have written a short paper How to choose database at
http://www.gplivna.eu/papers/choose_database.htm with some criteria to
think about. Remember that you have to choose it, you and your
colleagues will work with it, and this is important step, not to be
later much frustrated. Don't choose it blindly because someone likes
anyone. Choose it based on YOUR requirements, skills and available
skilled human resources.

Cannot see any reason why not use the power of RDBMS, especially if
you paid much money for its licenece and are not blinded by RDBMS
indepnedent app.

We have craeted succesfully apps on Oracle both putting almost all
business logic in Stored procs and php as presentation environment as
well as Oracle as mostly relational data store and .NET for business
logic. Both variants work and definitely are doable.

Gints Plivna
http://www.gplivna.eu

On 4 Aug., 18:46, SomeDeveloper <somedevelo... (AT) gmail (DOT) com> wrote:
You should really think about the RDBMS possibilities and offered
features. Don't know much about MySQL for example, but as SPs were
added there just in most recent version (probably 2 versions) I doubt
their offered functionality is big. On the other hand of course the
question is what are your requirements. If offered functionality meet
your requirements then this is perfect solution.

Yeahh that's the dreadful approach to DBMSes as bit bucket. This to my
mind is completely and absolutely dangerous approach and leads us to
70ties or probably 60ties when everything was stored in flat
(formatted) text files. No need for DBMSes then. Just flat files would
suffice. Why you'd spend DBMS licence cost using it just like bit
waste?
A couple of links here:
Do you have data waste or data base? at http://www.gplivna.eu/papers/data_wa..._data_base.htm
http://asktom.oracle.com/pls/asktom/...:6692296628899

Definitely not true.

approach #2 won't necessary ask for biz logic must be independent of
the RDBMS vendor. All big RDBMSES have invested hundreds of man years
to develop their functionality. If you choose to use only the common
features you'll get a very small subset of them. So effieciently
throwing away most of your money you paid for licences. Do you buy a
flat and then don't use kitchen just because it is not the same as
your friend have? You adapt to your kitchen and use it. And when you
go to your friend you use his/her kitchen just like he/she wants and
just like ktchen environment allows.

Be cautious of that. Data resides in database and if you try to do
everything out of it you'll need to invent many already invented
things, you'll need to send much data from client to db, it all husrts
performance and scalability.

I'm personally using Oracle, but unlike the previous poster I won't
say everybody should use it just because I use it. For a large banking
app as you said before I doubt I'd choose MySQL, but it doesn't mean
it cannot be done.
I have written a short paper How to choose database at
http://www.gplivna.eu/papers/choose_database.htm with some criteria to
think about. Remember that you have to choose it, you and your
colleagues will work with it, and this is important step, not to be
later much frustrated. Don't choose it blindly because someone likes
anyone. Choose it based on YOUR requirements, skills and available
skilled human resources.

Cannot see any reason why not use the power of RDBMS, especially if
you paid much money for its licenece and are not blinded by RDBMS
indepnedent app.

We have craeted succesfully apps on Oracle both putting almost all
business logic in Stored procs and php as presentation environment as
well as Oracle as mostly relational data store and .NET for business
logic. Both variants work and definitely are doable.

Gints Plivna
http://www.gplivna.eu