I agree completely, the only thing I would add is some validation logic in the top layer. Getting the client to do some of the work (where possible) on web pages relieves the server of some of the work. e.g. check passwords match on a user forms simple credit card validation (mod 10 and expiry dates) etc... Although this logic should be in the second layer too, at least the server wont have to post as many error pages. -----Original Message----- From: thelist-admin at lists.evolt.org [mailto:thelist-admin at lists.evolt.org]On Behalf Of Simon Lee Sent: 17 October 2002 14:54 To: thelist at lists.evolt.org Subject: RE: [thelist] Separation of app & db (was: Beginner asp question) > I've been mulling over just this point lately and your tip is > really provoking some churning of the brain muscles. I am at > heart a very procedural programmer. However I lean more and > more on using the database to order my results for me. In the > last week I've been working on a query to assemble some > statistics from a grat many results in a booking application. > I won't go ito details, but I've solved the problem in two > parts using views in MS SQL2K. The first view links together > various values in relational tables and the second collects > total values of sales from the first view for each type of > item available. > > What I've been pondering over is that I have now rewritten > this in 3 different ways. If I were using procedural code > (vbScript in ASP pages) I would have a gut feeling for which > method is most effective. However in the database there is so > much that is going on "behind the scenes" that I find it very > difficult to gain a feeling for which solution is best. I can > time queries, but beyond that I always find I have a sneaking > feeling that I could have done it better. > > What this leads me to is whether it is always better to use > the database, or whether the advantage of clearly-read > procedural code is worth a performance hit (within certain bounds). > > Is this heresy, or do others find a similar problem? > > > .steve > Hi All, This could be a *real* can of worms we've opened here! ;) I can see where Rudy's coming from, but I've personally found that using the database to get back *exactly* what you want is flawed....both from a performance and scalability point of view. Here's a real problem that I came across not so long ago: A client had a web-based application that ran across their intranet. It used a SQL Server backend, and relied on a custom-written COM object to build the web-pages that were served by IIS. A number of views, and functions were used to bring back data, but there were certain circumstances where the database struggled -- take a view that is supposed to return back the details of a project -- the sponsor, project manager, description etc. Each row of data returned dessribed a project. Now the client says that they would like to see the list of people working on that project as well as the other information, and here's where the problem arises. For each project you can have many people working on it, and so we have a one-to-many relationship. A left join in this situation is no use as for each project / row returned, you would end up with as many rows as you had people working on a project. So, SQL Server now had to find a list of the people working on each project and then concatenate them all in a string so that this list of people could be viewed along with the other project information -- this was achieved by running a function from the select statement. Now this ran like an absolute dog, for the simple reason that databases are meant for storing and returning information, *NOT* for concatenating strings together. If this additional field showing who was working on the project was removed, then the query / application ran like a dream. You may be thinking that our database design was flawed, or that we didn't have the proper indexes setup, but as we had the skills of a SQL Server database guru working on the project, this wasn't the case. Unfortunately for this client, their decision to put all the application *AND* display logic together into this COM object meant that nothing could really be done without a complete re-write of the application. This example has only served to reinforce my opinion that a database abstraction layer is an important *thin* layer to implement on any major project -- this layer would have handled the above example in the language of the application, whatever that may be -- PHP, ASP, CF, etc -- and returned the correct dataset to the application layer. Not only is performance much better, but your database is now independent of the application -- scalability is now greatly improved as you can have the application and database on different servers, or cluster of servers, *AND* if you have to, you can migrate to another database easily enough by just changing the database abstraction layer. There is a slight performance penalty perhaps with this additional layer, but it keeps code out of the database, which I think is important for maintainability and readability -- I'm not a big fan of stored procedures as you can imagine! ;) Here's how I structure my web applications currently: ~ Display Logic ~ Application Logic ~ Database Abstraction layer ~ Database So, that's my tuppence worth.....I'd be interested to hear what all you gurus out there think of this, and how you organise your web apps! sl -- For unsubscribe and other options, including the Tip Harvester and archive of thelist go to: http://lists.evolt.org Workers of the Web, evolt !