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.

When is this particular bias of mine "wrong"?

I've been thinking that, if I have my druthers when writing sw to be used by humans, it would be preferable to implement the business logic (except for the UI, obviously) completely in the database itself, using PL/SQL, T-SQL, whatever.

Since no answer is right for every situation, when would this methodology be "wrong"?
Steve Hirsch Send private email
Thursday, August 31, 2006
 
 
I don't think this is a "wrong" per se, just a design trade off.

Alright, so you've implemented it actually in the database.  So it should be:
*  fast,
*  relatively testable,
*  UI agnostic, and
*  potentially scalable.

Of course, being that close to the database, some problems come in if you have to:
*  support another database engine,
*  another version of your engine changes syntax/implementation,
*  allow users to add additional logic, or
*  support multiple versions simultaneous.
KC Send private email
Thursday, August 31, 2006
 
 
Steve, I don't understand the implicit rule: It is better for humans to use software with business logic in the database, than to use software with business logic outside the database.  What would make that rule true?  In other words, when is that particular bias of yours "right"?"
Flow
Thursday, August 31, 2006
 
 
It would be 'wrong' in a real-time system, where you either didn't have the space for a database, or didn't have time for the 'rule lookups'.
AllanL5
Thursday, August 31, 2006
 
 
It certainly is a design trade off...I like it because a) everything's in one place, easier to manage and b) you don't have to do any mapping from, say, Java objects to relational tables. Relational db's such as Oracle, SQL, et. al. have lots of built in infrastructure for things like row contentions, indexes, triggers, materialized views, as well as, well, SQL. To rebuild the SELECT statement functionality in the middle tier or on the client is alot of duplicated work.

On my latest project, I did everything within the Oracle DB itself, the scheduling, etc.; I implemented the change-data-capture logic using materialized views with fast refresh, didn't do any coding beyond specifying the queries.
Steve Hirsch Send private email
Thursday, August 31, 2006
 
 
IMO, it would be wrong to do it a certain way when the reason for doing it that way was not completely dependent on the best solution for the problem at hand.
D in PHX Send private email
Thursday, August 31, 2006
 
 
What do you have in mind when you say "written for humans"?  E.g. what about image manipulation algorithms?
Scot Doyle
Thursday, August 31, 2006
 
 
Here are some scenarios where implementing business logic in the database would be "worse" than implementing it in code...

1) The application needs to be able to work offline. If all or most of the business logic is in the database and the database connection is down, the application is useless.

2) You have databases in many locations. We sell Point of Sale software and it may be running in as many as 1000 stores across the chain. We don't even use stored procedures or triggers due to the difficulting in keeping 1000 database instances synchronized with the latest software updates. It is much easier to do it all in code and use something like xcopy deployment. The rules change dramatically when you move from a centralized corporate database model to separate databases at client sites.

3) You need to be able to support more than one database vendor.

4) You do a lot of customization of the business rules for clients. It is easier to manage custom functionality using common OO constructs in software than through the database.
Turtle Rustler
Thursday, August 31, 2006
 
 
My preference is totally the opposite. I avoid stored procedures and triggers like the plague and keep everything in one place - the application. To me the database is not a rules engine, it is the place where I store and retrieve data.
Tony Marston Send private email
Friday, September 01, 2006
 
 
I don't like this approach for two reasons:

a) Stored procedures are generally an extremely limited language - it's much easier to implement business logic in something higher level like Java.

b) I can't easily test the business logic as a separate component, it's tied to the database.

c) Almost all the projects I work on need to be portable across different databases - this means doing as little in SQL as possible.
anonymous Send private email
Friday, September 01, 2006
 
 
For values of 2 = 3. I added another reason halfway through.
anonymous Send private email
Friday, September 01, 2006
 
 
I would say when used by humans.

How do you handle very rich user interfaces which are highly interactive?  In our CRUD applications, the UI somehow needs to know the business rules.  It can't just pop up dialog after dialog with not-quite-right messages from the database.  There has to be a conduit from the database to the user for the business rules.

So ultimately, your database code will become extremely large, since it actually knows how to pump the UI properly.  But in the database, you typically do not have adequate tools to manage that complexity in the long term as the system evolves.  Source control becomes an issue, modularization becomes an issue, OO is non-existent.

If you were talking about an OO database, the question would probably not arise.  The objects hold their rules, and they live in the database.  The problem is that in the traditional RDBMS which we all use, the object really doesn't live on both sides.
Cade Roux Send private email
Friday, September 01, 2006
 
 
Great responses!

"What do you have in mind when you say 'written for humans'?  E.g. what about image manipulation algorithms?"

I was thinking of board level sw when I wrote that.

"1) The application needs to be able to work offline. If all or most of the business logic is in the database and the database connection is down, the application is useless."

Agree 100% here.

"2) You have databases in many locations. We sell Point of Sale software and it may be running in as many as 1000 stores across the chain. We don't even use stored procedures or triggers due to the difficulting in keeping 1000 database instances synchronized with the latest software updates. It is much easier to do it all in code and use something like xcopy deployment. The rules change dramatically when you move from a centralized corporate database model to separate databases at client sites."

Agree, but oy, what a scenario!

"3) You need to be able to support more than one database vendor."

Agree 100% here, but try to avoid this like the dickens.

"4) You do a lot of customization of the business rules for clients. It is easier to manage custom functionality using common OO constructs in software than through the database."

This I disagree with...Oracle has a very nice OO model built-in to the db, inheritance and all.

"My preference is totally the opposite. I avoid stored procedures and triggers like the plague and keep everything in one place - the application. To me the database is not a rules engine, it is the place where I store and retrieve data."

Why?

"a) Stored procedures are generally an extremely limited language - it's much easier to implement business logic in something higher level like Java."

That's not true for Oracle. PL/SQL is very sophisticated (it's really Ada Version 10.7).

"b) I can't easily test the business logic as a separate component, it's tied to the database."

Personally, I don't like to do that. Of course, YMMV.

"How do you handle very rich user interfaces which are highly interactive? In our CRUD applications, the UI somehow needs to know the business rules. It can't just pop up dialog after dialog with not-quite-right messages from the database. There has to be a conduit from the database to the user for the business rules."

That's a good point...I don't work very much with rich UI's.

"So ultimately, your database code will become extremely large, since it actually knows how to pump the UI properly. But in the database, you typically do not have adequate tools to manage that complexity in the long term as the system evolves. Source control becomes an issue, modularization becomes an issue, OO is non-existent."

Like I said above, PL/SQL is OO now (whether anybody uses it is another question). I handle source control outside the database, modularization isn't an issue.

"If you were talking about an OO database, the question would probably not arise. The objects hold their rules, and they live in the database. The problem is that in the traditional RDBMS which we all use, the object really doesn't live on both sides."

If you'd want it, Oracle is an OO database (actually O-R, but good enough). Don't know if SQL is yet.
Steve Hirsch Send private email
Friday, September 01, 2006
 
 
Me: "My preference is totally the opposite. I avoid stored procedures and triggers like the plague and keep everything in one place - the application. To me the database is not a rules engine, it is the place where I store and retrieve data."

Steve Hirsch: Why?

Because I can. I don't *have* to execute business rules in the database, so I don't. Besides, SQL is not a programming language, it is a data manipulation language, so I can do a better job in my application code than I can with SQL in stored procedures or triggers.

Also - debugging is a pain if the processing flow jumps between application code, stored procedures and triggers.

And another thing - updating my application is a lot easier if all I have to deliver is updated program files. Updates to stored procedures and triggers is a right PITA.

To cut a long story short - I can do everything I want in application code, so that's where I do it. I cannot do everything I want with SQL, so I don't bother.
Tony Marston Send private email
Friday, September 01, 2006
 
 
We treat each layer as an interface contract.  The interface contract to the database is defined with SPs.  I'm not sure what you mean about Oracle being OO.  Are those objects portable?  Instead of a rowset, do you get a collection of objects and you can execute methods against them?  If the client has to do some of the same operations, won't the code be duplicated and require further maintenance.

For instance, can you write the following code once and it can execute either in the database or at the client?

foreach (ChildObject co in BusinessObject.ChildCollection) {
  co.ChildMethod() ;
}

This is potentially possible using .NET and CLR in the SQL Server, but we don't.

Instead, the business objects pump the SPs - keeping the application layers distinct.  Ideally, yes, I would love to have an OO database infrastructure, but right now all the frameworks are too much black box and you don't get enough control to get the performance where it needs to be.

Right now, we know that for the majority of use, everything is going to use the business object model, which may itself be shared amoungst several different applications.
Cade Roux Send private email
Friday, September 01, 2006
 
 
Turning the database into an application server is a tried and somewaht untrue architecture. It works fine to a point, then hits scale problems. You then want to scale up your hardware which you find is very expensive and then you are screwed.

The problem is that a database is good at data, not logic and threads and parallelism and memory managment and modularization and all those other things that make a good application.

Once you hit your limit with all your code in the database you are just dead. You are totally at the database vendor's mercy and that's not a fun place to be as there is nothing they can do to help you.
son of parnas
Friday, September 01, 2006
 
 
SQL itself is surely not a programming language, but PL/SQL definitely is. Don't know if you're an Oracle guy Tony, just in case you're not.

>Are those objects portable?

Yup. They have something called a data cartridge, which is expressly for exporting objects (they call them types, same difference).

>Instead of a rowset, do you get a collection of objects and you can execute methods against them?

Yup

>If the client has to do some of the same operations, won't the code be duplicated and require further maintenance.

Sure, but there's no reason to do that.

>For instance, can you write the following code once and it can execute either in the database or at the client?
>
>foreach (ChildObject co in BusinessObject.ChildCollection) {
>  co.ChildMethod() ;
>}

Yup. Don't know about the client, but certainly in the database.

>Instead, the business objects pump the SPs - keeping the application layers distinct.

It's nice in theory, but I just wonder if it's extra work.

>Ideally, yes, I would love to have an OO database infrastructure, but right now all the frameworks are too much black box and you don't get enough control to get the performance where it needs to be.

If you run Oracle, then it is there for you. Don't know about other RDB's tho', and if you have to support different db's, then it isn't much good.

>Right now, we know that for the majority of use, everything is going to use the business object model, which may itself be shared amoungst several different applications.

My point is that if you could (and it's certainly not always feasible, as we have seen), it's potentially a lot easier to maintain, a lot less plumbing required and a lot more infrastructure available.

I haven't had many apps where scaling was an issue. I could see a case where your maxing out your db just pumping data in and out. I just haven't come across that in my journeys.
Steve Hirsch Send private email
Friday, September 01, 2006
 
 
I worked at a company where the CTO was a former Sybase consultant.  Virtually all of the business was in the database.  This worked fairly well, except the database started becoming a bottle neck (ie performance, and the DBA was overwhelmed because EVERY change involved the database).

In my case, I was responsible for a lot of data processing, and could scale up the service by just adding another processing box or two.  I implemented some business rules myself (the CTO had left by that point) so my system was very scalable.  However, every once in a while my processing needed some business logic from the database.  My processing boxes began stressing the database to the point that the entire service started slowing down.  We started off-loading logic back out to processing boxes and used the database for what it was good at: storing and retrieving data.

The system was thereafter able to handle much larger loads.
Doug Send private email
Saturday, September 02, 2006
 
 
Can you post an example of what code is in the UI, then?

I'm trying to understand how you drive everything from the database.  Are the objects used by the database the same as those used by the client application?

If you have a grid of objects on a form and the row needs to validate itself before/as changes are commited to the database, does the object serialize itself back to the database, execute part of the method on the database side and then serialize back to the application and display its appropriate warnings/errors?

In our case, using CSLA, that's kind of what happens, except the database doesn't really deal with objects and the data portal is not typically on the db server itself (and can actually be coallesced to avoid the serialization if object transport around the network is not necessary).  In our case, the objects are the same objects (.NET object model) after transport around the network.  Conceivably these could be in SQL Server, too, but we're not using the CLR - it requires more than xcopy deployment.
Cade Roux Send private email
Saturday, September 02, 2006
 
 
>Can you post an example of what code is in the UI, then?

It's been a while, I've mostly been doing ETL apps, but when I did, the code in the UI was almost entirely just simple CRUD.

>I'm trying to understand how you drive everything from the database.  Are the objects used by the database the same as those used by the client application?

Yes, that's key. There should be no "mapping", the OO model on the client and the server should be identical. The classes and their attributes should be the same. I could see different sets of methods on the client and server, tho'.

>If you have a grid of objects on a form and the row needs to validate itself before/as changes are committed to the database, does the object serialize itself back to the database, execute part of the method on the database side and then serialize back to the application and display its appropriate warnings/errors?

Yup.

>In our case, using CSLA, that's kind of what happens, except the database doesn't really deal with objects and the data portal is not typically on the db server itself (and can actually be coallesced to avoid the serialization if object transport around the network is not necessary).  In our case, the objects are the same objects (.NET object model) after transport around the network.  Conceivably these could be in SQL Server, too, but we're not using the CLR - it requires more than xcopy deployment.

I live in the Oracle world, can't comment on that.
Steve Hirsch Send private email
Sunday, September 03, 2006
 
 
So what do you write your client in, and how do you deploy the code for the classes to the clients - is there some kind of framework which brings the right code to the client for the class?
Cade Roux Send private email
Sunday, September 03, 2006
 
 
No, they are separate pieces of code. I guess my point was to keep the client as simple as possible.
Steve Hirsch Send private email
Sunday, September 03, 2006
 
 
I'm confused, I thought the client would be able to pump the same object model - that it was transparent so that if you made a change to the server-side objects to add new functionality, the client would simply be able to pump the same object methods.  The client would need to be changed to take advantage of the new methods, but they would already be in the client-side interface once the changes were made in the server-side.
Cade Roux Send private email
Tuesday, September 05, 2006
 
 
No Cade, I don't think I was clear enough. AFAIK there's no automatic synchronization of the client and server software, except for maybe PowerBuilder-like stuff where you store your code on the database.

What it does let you do is structure your code in similar ways, I guess, but you would still have to have 2 sets of code. My point was to strip the client (thin or fat, whatever) of as much as possible, because I find it easier to maintain that way.
Steve Hirsch Send private email
Tuesday, September 05, 2006
 
 
Steve,

I understand where you're coming from, but (in most cases) there needs to be a balance.

First, it is all well and good that you understand and can navigate the code, but no SQL implementation I've seen has truly organized and manageable code structures.  It's 99% procedural with a pretty dress that makes it look OO (a la VBScript).  Very few (if any) outside people would be able to pick up your code if you were hit by the proverbial bus.

Second, it's hard to compare the rich debugging tools provided by most development platforms to anything provided in SQL.  Sure, they'll optimize your queries, but logic stepping and watching locals, etc., is a bear if at all possible.

Third, and this is where I agree with you, there are a lot of times where SQL can implement a bit of logic /n/ times faster than the development platform.  I'm an MSSQL guy, but I know Oracle provides many of the "jeez that's fast" features like views and UDFs that would take several round-trips from a business logic layer to implement.  BUT, these should be migrated to the database ONLY when clearly beneficial, and not just because it's easier.

I've had a handful of apps that dealt with millions of rows in ginormous databases where business layer trips would've crippled the network.

So, don't feel too badly about your position, but ease off the extreme.  Put another way, there's a time and place for fourth normal form, but it's certainly not the best choice for *every* time or place.

-Matt
Matt Lavallee Send private email
Wednesday, September 06, 2006
 
 
"BUT, these should be migrated to the database ONLY when clearly beneficial, and not just because it's easier."

Absolutely. I started this thread to find some parameters, some rules of thumb, as to when this would be "good" and "bad".

1) When performance is an issue, for sure. That's a constant.

2) If you have to support multiple databases.

3) If your database's programming language add-on isn't very sophisticated.
Steve Hirsch Send private email
Wednesday, September 06, 2006
 
 
I don't think this would work on most of our applications.  The front end is so sophisticated and interactive, I don't see how putting more code in the database will do anything to reduce code in the interface.  Yes, we put as much as we can in the database, where it is appropriate.

I do code a lot of things straight in the database, but these tend to be system-level things and processes - any time a user is involved with anything seriously interactive, I don't think this would result in a very good user experience.
Cade Roux Send private email
Wednesday, September 06, 2006
 
 
Cade, Steve's initial point was about business logic, not UI elements.

I have created plenty of complex user-defined functions that couldn't have been reasonably executed client/server without countless adverse effects.

This gets even more true the further into normalizing the database you get.

-Matt
Matt Lavallee Send private email
Wednesday, September 06, 2006
 
 
The other aspect to consider is the case when you want different views of your data for different applications.  In monolithic data view applications, one database, one application can make some good sense.  In larger organisations where you have multiple uses of that data, storing all your business logic in the db becomes a significant development bottleneck with significant risks.  Do you want to support all possible developers using your db?

Good design keeps the storage of data seperate from the manipulation of that data seperate from the display of that data.  Why?  Storage and retrieval is one specific set of problems.  Manipulation and interpretation is another.  Pretty display and dynamic viewing is still another. 

Stored Procedures are really good when you have a specific type of calculated value (like an age which is retrieved from the database by taking the difference between two dates) but not so much when you need to structure that data in dynamic ways (like taking a signal trace and running filtering algorithms on that).

Note, these are not just performance differences, but fundamental linguistic and architecture issues.  Let the most appropriate place handle the most appropriate tasks.
James Birchall Send private email
Thursday, September 07, 2006
 
 
"The other aspect to consider is the case when you want different views of your data for different applications."

That's very easy to do inside the database, using a view. I think it would much easier than coding it yourself.

"In monolithic data view applications, one database, one application can make some good sense. In larger organisations where you have multiple uses of that data, storing all your business logic in the db becomes a significant development bottleneck with significant risks."

Why? Don't see how that is any different.

"Do you want to support all possible developers using your db?"

Aren't you anyways?

"Good design keeps the storage of data separate from the manipulation of that data separate from the display of that data. Why? Storage and retrieval is one specific set of problems. Manipulation and interpretation is another.  Pretty display and dynamic viewing is still another."

I try not to use the words "good" and "bad", because they are so context-sensitive.

"Stored Procedures are really good when you have a specific type of calculated value (like an age which is retrieved from the database by taking the difference between two dates) but not so much when you need to structure that data in dynamic ways (like taking a signal trace and running filtering algorithms on that)."

I've done that kind of thing all the time, using views and stored functions within the database. By using the SELECT functionality, you get so much prewritten for you, like joins, groupings, sorting, etc.

"Note, these are not just performance differences, but fundamental linguistic and architecture issues. Let the most appropriate place handle the most appropriate tasks."

On that, I agree.
Steve Hirsch Send private email
Thursday, September 07, 2006
 
 
Hey Steve,

I'm not saying that you *can't* do this stuff in the db, just that it's unwise IMHO.  What we're talking about is replacing the use of compiled business code with compiled stored procedures, right?

"That's very easy to do inside the database, using a view. I think it would much easier than coding it yourself."

Really?  In my experience it is not precisely because the semantics of PL/SQL and T-SQL do not lend themselves to classic business level stuff as well as Java/.NET.  Just semantic design. 

<<In monolithic data view applications, one database, one application can make some good sense. In larger organisations where you have multiple uses of that data, storing all your business logic in the db becomes a significant development bottleneck with significant risks.>>

"Why? Don't see how that is any different."

How do you do things like pull data from other stores/files/URI's?  If all your data resides in one DB, then you can get away with it.  But how do you make queries across a network and integrate those results into a stored procedure?  Or for files on the server?

On the development management side, who manages the stored procedures?  Does each Developer get a local copy of the DB?  If not, how do you handle concurrent development?  What tools are there that support debugging into stored procedures?  What about localisation?  How easy/hard is it to find programmers that can do this stuff?  By doing things this way, are you choosing a route that will make it very difficult to staff up if you need to?  When you're dealing with one application, these questions are relatively trivial and can be mitigated relatively easily.  But when you have manage a whole host of applications, it becomes unnecessarily time consuming with some pretty substantial risks. 

<<Stored Procedures are really good when you have a specific type of calculated value (like an age which is retrieved from the database by taking the difference between two dates) but not so much when you need to structure that data in dynamic ways (like taking a signal trace and running filtering algorithms on that).>>

"I've done that kind of thing all the time, using views and stored functions within the database. By using the SELECT functionality, you get so much prewritten for you, like joins, groupings, sorting, etc."

Really?  Can you elaborate on this?  Generally, in my experience, long running calculations are performed poorly by the database engine while intelligent retrievals are done excellently.  Signal analysis stuff generally takes a while.  When you tie up the DB CPU calculating a lot of stuff, you lose a lot of concurrent user activity while you wait for the DB to finish the calculation. 

Now, a fair argument is around how much of a business layer one needs and depending upon that determination, where you put it becomes a useful question.  In some cases, there are good reasons to put the entire business layer into the DB.  For some other problems, that just doesn't make sense.

My point is that the database just isn't designed to do this stuff.  Sure, for standard form entry, straight to the DB is fine.  Web app stuff, yeah, go for it.  Even putting a shwack of the business logic in there makes some sense.  But when you get to serious applications that need a real business layer, I think that putting this sort of stuff into the DB just makes it fall down.  And as a "biased approach" it seems weak to me.

- James
James Birchall Send private email
Thursday, September 07, 2006
 
 
Reasonable people disagreeing, sounds like good clean fun for the whole family to me (:=). What do you think we do in Yeshiva all day? (:=)

"I'm not saying that you *can't* do this stuff in the db, just that it's unwise IMHO.  What we're talking about is replacing the use of compiled business code with compiled stored procedures, right?"

No, not really. The stored procedures are themselves compiled business code.

"Really?  In my experience it is not precisely because the semantics of PL/SQL and T-SQL do not lend themselves to classic business level stuff as well as Java/.NET.  Just semantic design."

I can't speak for TSQL, but I've never lacked for infrastructure using PL/SQL. OO, Inheritance, overloading, etc. It's really just Ada after all.

"How do you do things like pull data from other stores/files/URI's?"

You can do that with PL/SQL, actually.

"If all your data resides in one DB, then you can get away with it.  But how do you make queries across a network and integrate those results into a stored procedure? Or for files on the server?"

Do it all the time. DB links.

"On the development management side, who manages the stored procedures?"

"Does each Developer get a local copy of the DB?"

Yup. I've got two instances on my home PC as we speak.

"If not, how do you handle concurrent development? What tools are there that support debugging into stored procedures?"

Check out TOAD.

"What about localisation?"

I think it'd be easier, you could store stuff in DB tables easily.

"How easy/hard is it to find programmers that can do this stuff?"

Real easy. They're cheaper than Java and .NET programmers (unfortunately (:=)).

"By doing things this way, are you choosing a route that will make it very difficult to staff up if you need to?"

I honestly don't think so.

"Really? Can you elaborate on this? Generally, in my experience, long running calculations are performed poorly by the database engine while intelligent retrievals are done excellently. Signal analysis stuff generally takes a while. When you tie up the DB CPU calculating a lot of stuff, you lose a lot of concurrent user activity while you wait for the DB to finish the calculation."

You're right, if you have heavy duty number crunching to do, then the DB is not the right place to do it. For sure.

"Now, a fair argument is around how much of a business layer one needs and depending upon that determination, where you put it becomes a useful question. In some cases, there are good reasons to put the entire business layer into the DB. For some other problems, that just doesn't make sense."

Agreed. My point in putting this here is to come up with some rules of thumb.
Steve Hirsch Send private email
Thursday, September 07, 2006
 
 
I understand the UI-counter point raised, but you show me an app with all the business logic in the database, and I'll show you a bad UI.

A fundamental problem in quality software where people feel they are empowered when using the software, and does not make them feel stupid is that the separation of data, business logic and presentation often tends to result in very poor UIs as the separation of layers tends to eliminate all the good conduits of feedback to the user in the right places to make the app feel usable.

Getting all these things bound together in a good way is a core problem in the design of quality software.  I think it's even a bigger issue than "impedance mismatch" which we already have common techniques with a variety of ORM approaches.

Properly getting the conduits in place between layers in the architecture is a massive challenge - and it can be done in a variety of ways, but I have not found any great solutions, although we've used many techniques.  By necessity all involve some type of duplication, even if it is at the framework level.  I was wondering if the OP had some tips on saving UI work, but I don't think so.  If there is new business logic added into the database, there is no framework he specified which allows this to be transported to the UI for interpretation.

I have done a variety of architectures.  I've put big business logic into the DB, but getting that information to the UI has never been easy to standardize, resulting in tons of command-pattern type classes.  Lately we've been putting the logic into business objects (although often the SPs underneath have a great deal of the code) and the application only calls the business object model.  I've done two different architectures this way - one lighter version which did not transport the objects and one which did (based on CSLA).  They both result in a very clean domain-specific business object model which is highly maintainable and scalable.  The libraries of business objects are highly re-usable and able to be evolved.

I love putting stuff straight in the database as SQL procs, but it tends to be too much work for interactive applications, since whatever constructs you build in the database don't just transport to your client side in a transparent fashion, so you are constantly re-inventing the wheel.  However, I think CLR in SQL is a possible way there, but it's not well tested - and it is probably not as scalable, and because of the GAC, is not xcopy-deployable.  Another interesting thing for the future is LINQ, but may have performance issues.
Cade Roux Send private email
Thursday, September 07, 2006
 
 
You know, if you could do old fashioned client server, I'd say PowerBuilder would be the way to go for your needs, since the UI code is stored in the database as well.

But Cade, I think you're right, if you really need a kick-butt UI, then you are going to have to move a chunk of logic to the front end.

I tend to do mostly reporting based, server to server apps, the UI isn't really much of a concern.
Steve Hirsch Send private email
Friday, September 08, 2006
 
 
In particular, I was thinking of the last app I wrote. The entire app, scheduling, over the network communications, was completely done in PL/SQL on the database. No shell scripting whatsoever.

The beauty of this approach was that the client was considering migrating the instance from Unix to NT. Nothing in the system would have had to have changed based on the way it was written.
Steve Hirsch Send private email
Friday, September 08, 2006
 
 
Steve,

Ok, now you've got me intrigued...

If PL/SQL has evolved to the point where it's essentially as capable as Java or .NET then the semantic argument is kinda moot.  If you can homogenise the DB requirements (so you don't need to run on MySQL, SQL, ORACLE and DB2) then there might be a good case for keeping everything in the DB.

As far as I can tell on the DBLink issue, I think that only works with other Databases (and they have to implement the database connection protocol), doesn't it?  Does it work for Web Services and other networking servers (torrents, ftp servers, email, etc...)?  I don't think "send an email" is supported in PL/SQL, but I'm not sure.  Can you do that?

The only other argument I have is the round-trip argument, where UI code frequently calls low-latency Business Code but Business Code only infrequently calls high-latency DB code.  But this is more an optimisation point rather then a fundamental design point.

I guess, as was mentioned earlier, the only reasons to keep stuff out of the DB are:

1)  The DB Environment can not be controlled by the development team.

2)  The application has significant performance requirements.

3)  Your local market does not have a lot of available DB programmers.

4)  Your business layer has significant proprietary networking.

James.
James Birchall Send private email
Friday, September 08, 2006
 
 
"Ok, now you've got me intrigued..."

We aim to please. (:=)

"If PL/SQL has evolved to the point where it's essentially as capable as Java or .NET then the semantic argument is kinda moot."

I am continually amazed at the capabilities of the packages that are available. PL/SQL server pages, scheduling software, so on and so on. Check out  technet.oracle.com  look for the Packages and Types reference book. Talk about your ecosystem!

"If you can homogenise the DB requirements (so you don't need to run on MySQL, SQL, ORACLE and DB2) then there might be a good case for keeping everything in the DB."

Definitely. That's a without which nothing. I happen to have worked a lot in pharma, where Oracle is standard.

"As far as I can tell on the DBLink issue, I think that only works with other Databases (and they have to implement the database connection protocol), doesn't it?"

Last I knew, yes. Who knows what they've added since I last checked!

"Does it work for Web Services and other networking servers (torrents, ftp servers, email, etc...)?"

I'd have to check, I'd guess so.

"I don't think 'send an email' is supported in PL/SQL, but I'm not sure.  Can you do that?"

Yup.

"The only other argument I have is the round-trip argument, where UI code frequently calls low-latency Business Code but Business Code only infrequently calls high-latency DB code.  But this is more an optimisation point rather then a fundamental design point."

I can see that. Life is full of tradeoffs.

"1)  The DB Environment can not be controlled by the development team."

This drives me out of my mind. You definitely need a sandbox where you can potchker. Major pet peeve
Steve Hirsch Send private email
Friday, September 08, 2006
 
 
<<1)  The DB Environment can not be controlled by the development team.>>

"This drives me out of my mind. You definitely need a sandbox where you can potchker. Major pet peeve"

It's pretty common in web application development.  Your software has to be able to run on every OS, web server, app server and db on the planet.  Makes the "Java/SQL-92/No stored procs" dev choice a no brainer.

Personally, I prefer the web services model for solving client-server type designs.  A lot more flexible and easy to find developers that can do it without any training.  I like to keep the DB well designed, but with few triggers and procedures (mostly for pseudo-multi dimensional rollups).

Another thing a collegue here mentioned, was that DB servers are generally locked down like Ft. Knox.  That hampers a lot of what you can do on them.  Often, you can't even see the DB box from the outside world (and they can't see out) so that takes away a lot of your options. 

I guess it really depends (d'uh) on the particulars of the problem.  "If all you have is a hammer, every problem looks like a nail"  There's something to be said for staying within the common architectural patterns (N-tier architecture with a standard DB role) even if it is sub-optimal from an individual standpoint.

James.
James Birchall Send private email
Friday, September 08, 2006
 
 
I definitely have to jump in for Steve, here.

I develop web applications, some of which are large-scale (3 or 4 big database servers), and there are most certainly occassions where business-logic-in-the-DB is the *only* appropriate route.

Just as an example:
A widely deployed (120 office) corporate invoicing application (which has a local db) feeds data upstream to high-level "collector" databases.  It also pulls certain configuration data downstream, as well as summary data churned out by the servers at corporate (millions upon millions of rows).

Those big databases generate views out of those millions and millions of rows in realtime, performing aging calculations against prospective proposals based on existing jobs (% likely, % on-time, % to collect, and so on, based on jobs of similar scope, size, region, client, etc.).

700 project managers can access that data via the web at any time, from anywhere, through an extranet.  Clients can also review their invoice data, etc., for over 50,000 projects per year.

The logical argument of NOT using any database functions would also preclude COUNT(), SUM() or any of the grouping functions provided.

The capabilities afforded by triggers, views, and column-based functions simply cannot be replicated at the same performance level with ANY application that lives outside of the database.  That's the reason those features exist and why people spend hundreds of thousands of dollars to build giant datastores.

-Matt
Matt Lavallee Send private email
Friday, September 08, 2006
 
 
Just to point out something we DID do completely in our SQL Server:

email

Our applications which generate emails eventually call one of a number of SPs which create the email in the database.  The management of the queue is done completely in the database.  The beauty of this is really that you can tie email into a real database transaction without any other transaction manager needs.  Emails have a variety of columns you can query on, you can schedule emails to be transmitted at a later time, you can re-queue emails, you can display old emailed reports without having to open the old emails in a client or mailbox.

Obviously the progenitor of this system was the crappy SQLMail in SQL Server versions prior to 2005 (with a MAPI profile required, all your emails come from one user! - who exactly can use this kind of system?!).  We currently use SQLAnswersMail for the SMTP portion of the process, but everything else is managed with T-SQL, jobs, DTS, etc.
Cade Roux Send private email
Friday, September 08, 2006
 
 
A great big Mazel Tov to all the contributors to this thread. Intelligently discussing differing points of view, with respect to all participants, on the internet. What a concept!

Anyways, another time the bias would point to storing business rules on the database is where you really need to optimize for referential integrity (like clinical trial apps). The built-in infrastructure of triggers, foreign keys, etc. saves a lot of code from having to be developed.
Steve Hirsch Send private email
Sunday, September 10, 2006
 
 
@ Steve: For pure entertainment value post this on a java forum somewhere. The responses from the people here are alot more thoughtful than you will get there.
Ryan
Monday, September 11, 2006
 
 
Thanks, but I've done the whole sticking needles in my eyes thing, I think I'll pass.
Steve Hirsch Send private email
Monday, September 11, 2006
 
 
hahahahahahahaha
Matt Lavallee Send private email
Monday, September 11, 2006
 
 

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

Other recent topics Other recent topics
 
Powered by FogBugz