Designing Database Centric Applications

I get the impression that most of the people on these forums are non-DB people. What is your opinion of making database centric applications when building larger custom systems? This is basically what I do for a living. I find developers know very little of how databases work and often do not want to use any of the features(such as stored procedures).

Most do not even seem to understand the value of normalization. What is your opinion?

It seems a large number of people in the Java Community(accept Rob Johnson) want database independence. I sent an email to Dave Thomas and he said stored procedures are 'evil'. I believe the creator of Ruby on Rails says the same thing.

I believe these forums have alot of .Net programmers? What do you think?

Ryan
Saturday, September 09, 2006

Deleting …Approving …

I love SPs. UDFs, too. Views, triggers, etc., bring 'em on.

I think many programmers avoid database-specific features for a number of reasons (several were listed in a post on this board with "bias" in the subject):

* Database code is often non-portable between platforms* Database code requires work to integrate into versioning systems* Learning Enterprise RDBMS is often outside the reach of the average programmer* Data normalization is a lost art

I can't even remember the last time I saw a sample database in Fourth Normal Form.

Rob Johnson(creator of spring) wrote a very good J2EE book and he said about this

" Database code requires work to integrate into versioning systems"

what database people say about this... you are paying an incredible amount of money for your database product. What are the odds that you will ever have to port it? If they are low(and for custom software they almost always are) then you are wasting money by not using the features.

Ryan
Saturday, September 09, 2006

Deleting …Approving …

@Ryan

I think you grabbed the wrong quote... I've gotten around the versioning problems by generating a db creation script and storing it in the source control with verything else.

There has only been one place that I've ever worked which was only supporting one database. Everywhere else and every project I've worked on has had to support multiple database engines.

I don't mind sprocs if they are doing things that the database is good at. Selecting, slicing and dicing tables to find exactly what I want? Sure, that's fantastic.

Sprocs that do things like create HTML, convert dates to various string formats, or send emails, on the other hand, I'd rather not have to deal with.

I would much rather have data-centric stuff in the database. But there's a lot of programming problems that SQL just sucks at, and I'd rather have the database experts do what they're good at, manipulate data, rather than trying to calculate eigenvectors in SQL.

Plus, I've found that the number of good OO programmers is higher than the number of good SQL folks. If all your logic is inside sprocs, I'm limiting who can work on the project.

Chris Tavares
Sunday, September 10, 2006

Deleting …Approving …

Chris: I frequently get the response 'let the database do what its good at'. To me that is a cliched response. How do you know what its good at? People tend to recite the same things. I specialize in Oracle. I think most people on here refer to sql server when they talk about databases. My understanding is the SQL Server is very robust as well.

HTML-- Oracle provides a very easy to use pl/sql web toolkit. I have used it and jsps. Its easier to code with the web toolkit. Time to develop is a little higher. Though not that much. I am a bit ambivalent on html in the database. It doesn't really matter.

Email: Oracle provices a simple API for doing this. It takes less code than doing it in an application. Emailing is easy enough where I am a bit ambivalent. It just depends on your application.

Date to String: This boggles my mind. Is this hard to do in SQL Server? It's absolutely simple to do in java. You have a little less code too since you don't have to instantiate an object. I don't understand this.

There are alot of bad DB people out there. Generally you need 20 phone screens to get one good one(and not great one). However, if you have good ones you should use them. The time to develop is much lower when you code in the database because you are using a 4 GL vs. a 3 GL (most of the time) and this requires less code.

Many of the developers on here work on shops where you have to support multiple database products. In those cases, they have to go outside the database. However, the really custom projects do not. In these cases your costs savings and your maintenance costs can be much lower with code in the database.

BTW, I read the bias post. I have seen posts like this put on the Java forums and they just freak out when you contradict the slogan of 'database independence' and give you a bunch of BS responses. The crowd on here seems to be more thoughtful.

One other argument I get is 'developers are not good at databases'. Get a book. It's easier to learn about databases than it is to learn how write recursive algorithms. That is not an excuse.

Ryan
Sunday, September 10, 2006

Deleting …Approving …

Ryan,

<snip>"Get a book. It's easier to learn about databases than it is to learn how write recursive algorithms. That is not an excuse."

If your really want to piss off the people whose opinion you are soliciting, continue this. You asked - you got answers. The correct approach for you, then, is to thank people for showing you their reasoning, not slam them for not being Oracle-centric.

When I read your OP, I thought you were looking for answers. Now it sounds like you're pushing an agenda.

The poster you responded to above had some good, valid points. Databases aren't always the answer. Many times they are. But not always. You're going to get a lot of answers here, and you need to think about them before trying to shoot them down.

Karl Perry
Sunday, September 10, 2006

Deleting …Approving …

It depends on what you mean by "database centric applications". I have been designing and building CRUD applications for several decades and I firmly believe that a good database design is the heart of any system, which requires the ability to create properly normalised data structures. Get those wrong and no amount of application code will get you out of the sh*t.

I know how databases work, but I only use those features which I want to use. Views are OK, but I avoid stored procedures and triggers like the plague.

>>> Chris: I frequently get the response 'let the database do what its good at'. To me that is a cliched response. How do you know what its good at? <<<<

If you're a developer with a CS degree, you know. The DB is good at working with DATA. Relational databases that are in mainstream use today are based on relational algebra and when implemented well, do the work of slicing/dicing/aggregating/filtering DATA very well. The relational algebra has no notion of general computing constructs like loops/data conversion etc.

Of course, over the years, to differentiate themselvs, many RDBMS vendors started adding extensions to the SQL language. So today we are able to do calculations, type conversions, loops - almost anything a "host" language like C++, Java or Ruby are capable of.

But it doesn't mean that we SHOULD use these capabilities. The problem is the old saying that goes something like this: if all you've got is a big hammer, suddenly the whole world starts looking like a nail. In other words, before you know it, most of your business logic ends up in the database, BECAUSE IT'S EASY, which pretty much guarantees a disaster when you switch RDBMS, which you inevitably will. But even if you don't, "mainstream" languages are simply better at non-SQL related tasks and there are a lot more developers to choose from to do that.

I have seen this anomaly (business logic ending up in the database) happen more than once. I have seen the problems it brings when porting the code to another RDBMS. I have also seen the projects where the DB tasks are done in SQL while everything else is done in the "host" language and these projects ended up much better.

On the other hand, I have seen the reverse too - instead of handling a filter in the SQL, the dev would just do blind "SELECT * FROM SomeTable" and then manually do the equivalent of "... WHERE SomeColumn = SomeValue" in the host language. Not only is this slow, it splits the operation into two "worlds" and makes debugging or performance optimization hard, if not impossible.

So there's a reason why you get the cliché response - there's truth in that response.

There are a lot of issues that you bring up. In my experience, you are basically correct that most programmers are essentially non-DB people. I still see programmers iterating through client-side cursors when handling tasks that a single select or update would take care of. I had an experienced developer ask me a few months ago, "why do we have so many tables? Why don't we just put everything in a single table with lots of fields like 'field1', 'field2', 'field3', etc?" Yes, that was said with a straight face.

Here's the bottom line - the most database experience programmers get in college is in a single, 3 hour, database systems course. And sometimes that is more management focused than programming focused. Or it's taught by someone who thinks relational algrebra is more important than actual SQL. Or nobody covers performance issues. You just hope the DBMS is fast enough for what you ask it to do, becuase nobody actually learns how to tune the things (either the SQL or indexes) in college.

Well, relational algrebra is more important than SQL if you are going to go on to design database management systems. But 80% of programmers end up programming business apps and almost 100% of business apps involve a database. So is it any surprise that 3 hours of database coursework as traditionally taught is insufficient?

So a lot of anti-DB feelings (or DB-indifference) is a lack of experience. As you correctly point out, a lot of it stems from a desire for independence. Many developers initial forays into stored procedures are a painful experience - not so much becuase stored procedures are hard, but do to other issues - lack of portability, concurrency issues leading to sporadic deadlocks, etc. After a couple painful experiences, it's understandable that they would shun DBs as anything other than a persistence mechanism.

Personally, I think more work needs to be done on two fronts. First, agile development needs to continue to tackle the problems of database schema migrations. There is now significant literature on this, from Scott Ambler and others. Additionally, Ruby on Rails embraces this need from the start. Second, we need to better clarify where the best practice dividing line between DB and non-DB code really is. DBA's want all the code in stored procedures and non-DBA's want no code in the DBMS. Clearly, on some projects, there is a more appropriate mix. But there aren't a lot of developers skilled at bridging that gap.

College leve DB classes are horrible. They teach anything the least bit useful. Relational Algebra is completely useless in the real world.

The good thing about databases is that if you have a CS degree and you have some experience, learning more about a database is alot easier than your CS classes. Any good programmer can do it. You can download all the oracle software and documentation for free and learn it? otn.oracle.com I would assume the same is true for SQL Server and DB2.

First off, I work in a bit of a different world than most of you. I work on very large projects often with 100+ developers. These types of projects are never going to be ported to a new database. The licensing fee for Oracle alone makes you not want to pay for another DB license.

On smaller projects when there is a real chance you have to switch databases, then yes avoid stored procedures. However, this is over done. Projects that use SQL Server, Oracle, or DB2 rarely port to a new DBMS. I am sure there are some exceptions.

I agree with the splitting of code issue leading to some code in the application and some in the database. This can be a nightmare. I have seen it happen on large projects. (multi hundred million dollar projects). This reduced productivity and wasted money.

You really do need more planning if you are going to use the database features. You need to architect for it, so you don't end up with a hodge podge of code all over the place.

The bottom line is that I can almost universally develop an application faster, that is easier to maintain by extensively using the database than virtually any developer can using an application language. I am not claiming to be a 'rock star'. 4 GL languages do not require framework code. I can do it with less code and in a way that is easier to read and maintain.

Ryan
Sunday, September 10, 2006

Deleting …Approving …

Check my previous post in this forum, if you're interested.

Steve Hirsch
Sunday, September 10, 2006

Deleting …Approving …

The database is the lowest common denominator for many applications, hence some people like the idea to build the applications starting from it. On the other hand, you generally can't pre-configure or configure a lot of programs directly in the database, not to mention that development and debugging take a hit if you use a lot of the possible programming features of the database. Alas, you even make it a little obfuscated in the end, which will cause maintenance headaches unless some genious maintainer takes over where the genious original developers left it.

You can try to beat some development approaches with your database centered one, but the grandiose of everything has its price. You tradeoff flexibility, reuse, inexpensiveness, and other things because of it.

Yeah, I'm not talking about lowest common denominator when I say that your approach can be beaten.

One warning is that many folks, whether DB-folks or not, worry too much about the persistence and create the programs around it, as if the persistence weren't only one of the basic features of the program. One of the most important in the long term, but many projects fail with or without a DB-centered approach, thus rendering any long term goals moot.

But I like your style. It's reductionism. It's management of expectations. Feature creep passes far away from your goals. Nonetheless, your style is time and money consuming, to say the least.

Sigrist
Sunday, September 10, 2006

Deleting …Approving …

Using a layered architecture is essential to building maintainable, flexible, well-performing applications in a complex domain. For simple apps, any approach will work but for the tough enterprise problems, you have to use each layer to do what it does best. Don't do record filtering in ASP script, don't do HTML rendering in SQL. On the complex enterprise apps I build, SPs are a must, proper DB normalization (and selective denormalization for performance) is a must, but I wouldn't call this db-centric.

I think I'll write an article about this some day...

my 3c

Mike S
Sunday, September 10, 2006

Deleting …Approving …

Your database is well designed when you can use it to teach new-hires how your company operates.

A properly designed database is a high-level reflection of how a business operates. Since it's all visual, I can immediatly grasp something that would take weeks of code reading. Screw the code man, if I wanna figure out how your company does inventory all you gotta do is draw the database tables on your whiteboard and I'm golden.

Whether it's drawn or described in sentences or both, whether it's stored in a database instance or an XML file or a flat file or whatever, the data model is the heart of the app. Everything radiates outward from there.

Steve Hirsch
Sunday, September 10, 2006

Deleting …Approving …

+1 to Steve Hirsch

A properly organised data model is absolutely essential, whereas the location of certain business logic - in the database or in the application - is a matter of choice.

We're too busy to learn proprietary extensions to SQL for every type of database that we maintain. (For the record, I personally work with Ingres, Oracle, MySQL and SQL Server in this job, and I have peers here who deal with even more obscure systems.) We'd like to take advantage of the databases' special features, but we have job postings more than a year old for experienced, non-centric DBAs and we have yet to find a single candidate we liked enough to interview.

In contrast, it is a lot easier to find, "test", hire and train programmers who at least know how to connect to a database, submit a SQL query and then work on the record set at the application level. In business terms, that's a LOT of saved money and productivity.

TheDavid
Tuesday, September 12, 2006

Deleting …Approving …

what is a non-centric DBA? You mean a DBA who does all the databases?

DBA work by its very nature is specialized. I know people who work on 2 of the following: Sybase, Oracle, DB2, SQL Server.

Any more than that and they won't be good enough with one to work on a large system which is where the money is.

You don't get paid being a jack of all trades DBAs. High end specialzied DBAs can make $100/hour.

Ryan
Wednesday, September 13, 2006

Deleting …Approving …

>> You don't get paid being a jack of all trades DBAs. High end specialzied DBAs can make $100/hour. <<

Or much, much more if they're really good. We were paying an Oracle kung fu master $225/hour at one company, because he really was the cat's pajamas.