design strategies, database views and stored procedures

[paul wheaton]

Years ago folks sitting down to design an app would design the database first and then design the program that would use that data.
Now, we design the program first and when we have determined the application's needs for object persistance, we design the database tables.
For the last ten years, when I have worked with a database and left to my own devices, I tend to keep all of the business logic out of the databse. I use the database for just storage of my data. Sometimes there is an existing database full of stored procedures and views and I get around okay.
Once in a while I talk to a DBA that insists that I'm wrong and that most of the logic for an application should be done within the database. Well, I'm no DBA, and there is a great deal offered in the database world that I have not taken the time to learn. So I could be wrong. But my gut tells me that if I knew both sides really well, a small amount of business logic (maybe 5% to 10%) in the database could have a worthwhile optimizing effect at a small cost of violating our ideas involving "n-tiered design".
What do you guys think? Plain indexed tables only? Some views? Lots of views? A few stored procedures? Lots of stored procedures?

[Thomas Paul]

The problem with business rules in a database is that there is no easy way to increase the number of servers to improve performance if your business rules are in the database. Let's take a simple case where we have one complex rule that chews up CPU. If this is in a database, we would have to upgrade the database server and perhaps pull other apps off that server to improve performance. The time and effort to do this could be painful. If the rule is outside of the database, I could add extra servers just for that rule. All I need is some simple algorithm to distribute the workload across multiple servers which should take about 3 minutes to code.
This ignores the other issue which is what happens if we change database brands. Suddenly all those highly efficient stored procedures need to be rewritten.

[Jason Menard]

I would think that somewhere there is a balance. Where I work, they are all about the stored procedure, mainly for performance purposes. For one thing, our database server is by far the most powerful server we have. The general philosophy we subscribe to is that the database is used to return the data to the application (duh!), regardless of the view of that data. In other words, the view of the data is manipulated on the database side, not in the application.
While Thomas' concerns are very valid, we aren't in danger of switching brands, and if this were to happen, we would have many other problems to contend with as well (non-Java apps), so it might depend on your personal situation. If performance is king, it's hard to beat the stored procedure.

[paul wheaton]

How portable are views?

[Michael Matola]

Originally posted by Paul Wheaton:
How portable are views?

I would (naively?) guess as portable as the SQL used to create them. In other words, if you use all sorts of vendor-specifcy SQL extensions in the queries backing your views, you'll have portability issues. Stick to more "standard" SQL and you may be better off.

[Tina Coleman]

Another reason I've seen proffered for business rules in the database is for the cases when databases are shared across applications. Or might be in the future. Then, regardless of what applications folks write against the data, everyone's playing with the same set of rules (if your business rules are encapsulated in stored procedures, triggers, and the like).