The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

application server really needed?

a project for up to 4 million (prospective) users
data packages that are send and recieved range from 100k to 250 mb
system must be highly secure (patient data)

the ideal design of a university professor follows - can one agree?

direct connection from the client via ODBC to a DBMS system, like e.g. Oracle
client access via PL-SQL-statements only - the Client defines all the SQL statements.
the complete business logic is completed as PL-SQL-statements in the client
Oracle facilitates all aspects of the connection - the threading (LOGGING, monitoring, user administration,en/decryption, …).

can and should such a design be implemented?
What are the problems?
henry koch Send private email
Monday, September 10, 2007
That could be fine if the end-users are aware of what they are getting into.  If they are high-end IT departments of competent institutions that have experience integrating their systems with external databases, it could be entirely appropriate and a web app would be overly complicating the product.  If, however, you deliver this service and they are not prepared to be connecting with their own ODBC clients and writing PL/SQL queries to access the system, they will be supremely disappointed.

You need to look at the whole picture.  What kind of customers do you expect?
John Cromartie Send private email
Monday, September 10, 2007
Here are some questions I'd be asking about:
1. How do I upgrade the clients? If all the business logic is there, I need to make sure that I can quickly update the client.
2. 250MB transfers. How do I handle failures - restart or negotiated continue? Is the 250 Mb stored as a BLOB or reference to a file. If file, how will the file space be managed?
3. (really 1a) How do I handle feature updates in the inline protocol? For example, I want to update the client to include a new data point. How will I handle clients that are sending with the new data point and those without? i.e. How is the propagational wavefront of a new client handled?
4. When ODBC is specified, I am assuming that this is *encrypted* ODBC, true?
5. Man in the middle protection. How does the client learn where to 'phone home' to? Is that protected against cache poisoning?
6. (A big point) Will your clients be willing to open an outbound ODBC port on their firewalls? Personally, I have encountered great reluctance on the part of corporations to open ports for applications. Will you have to negotiate/navigate some proxy scheme?

Personal view:
I am not a big fan of distributing the business logic to the clients. Sure you offload the compute demand on your server, but I find the headaches of managing the update issue far outweigh the headaches of properly sizing your server farm. Also, from a purely selfish perspective, I like to the keep the IP crown jewels close to home.

That being said, there is nothing wrong with the proposed solution that I can see. There are certainly a lot of examples of client/server applications written this way and they work just fine.
Just A Guy Send private email
Monday, September 10, 2007
the customers are md's, hopitals and patients.
the odbc traffic would be encrypted.
all the logic for the database is supposed the stored procedures.

i myself think that this is really not so smart for all the above reasons. the patient data should be kept as inaccessible as possible.
henry koch Send private email
Monday, September 10, 2007
How many concurrent clients and how many open connections does that add up to? An app server or TPM could manage far fewer connections in a pool, which may make a huge difference to the database.

More important than that (to me) I have a general philosophy of not trusting my clients. They may cause accidental or malicious problems in my database. It's worrying to give remote machines the ability to run arbitrary SQL.

I've been doing browser based apps in recent years. At first I hated to give up the power and rich UI of a fat client, but I sure don't miss distributing and configuring and compatibility testing client code one bit!
Stan James Send private email
Monday, September 10, 2007
==> can and should such a design be implemented?


Not only no, but H3LL NO!

==> direct connection from the client via ODBC to a DBMS system, like e.g. Oracle

4 Million users. Directly connected. Hope you've got a huge infrastructure budget.

==> client access via PL-SQL-statements only - the Client defines all the SQL statements.

Very, very bad idea. You wan't a Client to kick off a few large table scans, using non-indexed fields, doing any old thing they want? Bringing the server to it's knees with an unintentional Denial Of Service attack and knocking out the remaining 3,999,999 users because the server's busy handling one F*cked up query?

==> the complete business logic is completed as PL-SQL-statements in the client

So the Client can blast any remnants of data consistency? They'll be free to write whatever SQL they want, even if it's in violation of basic business logic/rules that are needed by the database?


I definitely would not take this approach. I'd think it doomed to failure.
Monday, September 10, 2007
This has to be a troll.  This approach is too loaded with problems to be taken seriously.
Monday, September 10, 2007
==This has to be a troll.

No - I don't think so. I think it's an honest question from someone who's being mislead by  the typically "real-world-cluelessness" of your average University Professor. I'd just make sure I steer clear of any of this particular Prof's classes in the future unless/until you smack him, hard, upside the head with a giant clue-by-four.
Monday, September 10, 2007
Just when you think that there will be somebody to install and configure 4.000.000 Oracle clients.
Monday, September 10, 2007
i am no troll (however i am not a very tall person).
this is a serious question. this guy from the university of bonn put forward this design, even without knowing the real world problems. i am looking for arguments, actually against this, but also i thought there might be something i don't know or unterstand - and maybe that is so.
professors (consutants) don't err - or?
Henry Koch
Monday, September 10, 2007
Whether the 4 million are concurrent or only logging in once a month to check something, would make a big difference.
Greg Send private email
Monday, September 10, 2007
Business logic in DB is fine, but the real worry is clients connecting directly to DB. Also ODBC is a real old technology and not efficient.

You need application servers for load balancing and frankly better security. Client machines having network connectivity to DB will definitely fail a security audit. I hope the business logic for the critical DB is through stored procs which will be vetted by a competent DBA team.
Monday, September 10, 2007
we got 3.5 million users on a site(about 100,000/day) on a site with a cheap web server. We had alot of servers. There was no need for an expensive application server. This was very complex business logic.

we had alot of pl/sql, but we had a lot of java code using spring.
Monday, September 10, 2007
While it is difficult to comment on this without knowing the detailed requirements...

However, this sounds absolutely barking mad!

Four million thick client apps with embedded business logic and a direct connection to a database server? Securing this could be pretty difficult (i.e. a nightmare).
Arethuza Send private email
Tuesday, September 11, 2007
1992 just called and they want their application design pattern back.

Seriously, these are incredibly naive questions from someone who has never given any deep thought to the implications of 4 million thick clients connecting to a system.  What a horrible idea...
xampl Send private email
Tuesday, September 11, 2007
Replace ODBC with Web Services, sprinkle on a little Web 2.0 terminology, and "bingo!" you've got a real winner.

The biggest issue as others have pointed out is giving remote clients direct access to the database. It will be slow, insecure, and painful at best. If nothing else you should consider something like SQL Server over HTTP. But I still wouldn't recommend it unless the clients are completely under your control at all times and you spend a LOT of time on a solid authentication/authorization scheme.

You should also remember that users do NOT have to use your client application to access the database. They are free to use/write their own applications that use ODBC to access your database directly. If your client can do it, so can theirs. Many developers forget this and assume that some code in the client which locks down access is all that is needed. Nothing could be further from the truth.
dood mcdoogle
Tuesday, September 11, 2007
It's interesting that some people read it as 4 million concurrent connections and others as a population of 4 million potential connections. If it truly is concurrent connections, I hope you have some heavy duty HVAC to handle the load ;-)
Just A Guy Send private email
Tuesday, September 11, 2007

Similiar to what dood said.

Make it a webservice, expose an API webservice that will allow them access to the DB.  Then you can change the DB all you need to and the API remains the same.  You can either provide a thin client (web), think client (uses the web service API) or publish the API and let them write their own.

This will work best since now you have a standard published shema api that all your clients can use.  If its a pay model you can vary how they will be billed, $x for the thin client $XX for the think client and $ not at much if they write their own.
Thursday, September 20, 2007
oh and security is taken care of in the API so you never have to worry about direct writes by the client to the DB
Thursday, September 20, 2007

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz