Why are database features being ignored, and instead reinvented in the middle tier?

I think the biggest reason that overshadows all the rest is that relational database systems become dramatically more important when multiple applications are sharing the same data. Codd's famous paper is titled "A Relational Model of Data for Large ***Shared*** Data Banks" (emphasis mine).

People have a tendency to think that the application they are writing now will always be controlled by their team; and that it will always satisfy all of the needs of people interested in the data generated by the application. If a new need arises, that would be satisfied by adding a new feature to an existing application, not creating a new application.

But in many cases (not all, of course; every situation is different), that development model doesn't work very well in the long term. As the data generated by the application accumulates and becomes more important to the business, different people will have interesting ideas about how to use the data. When that happens, if you don't have a relational database management system, you are in for a big challenge.

Scalability. The more work you give to the database server, the greater a bottleneck it becomes. It's more scalable to have a whole farm of load-balanced application servers processing the data, and just use the database as a persistence store.

"Why are database features being ignored".

Because lots of so-called developers are completely ignorant about data management, and what's worse, they're completely ignorant of their ignorance too. "Unskilled and unaware of it", for whom this rings a bell.

To advance on what [Christian][1] said, about scalability.

Simply, RDBMs are being used more as pure data stores, while the logic has been migrating in to the Application Servers. The extra tier of the AS give developers more flexibility than using the RDBMS as an Application Server.

Before, in the classic days of Fat Apps and Client Server, the DB and Application Server were basically the same thing. You had application logic either embedded in your fat client code, or you pushed it back in to the RDBMS. But back then, the primary form of communication was SQL directly to the database.

Nowadays, other application protocols are more common (CORBA, DCOM, Remote EJB, and more and more common today XML/JSON/HTTP-RPC style protocols over HTTP). Most databases don't respond to those protocols directly, so an Application layer is interjected to intercept those calls, and that layer calls the database.

But as we've learned we now get a lot more flexibility putting logic in to this layer. Wider choice of tools, more flexibility over caching, or failover, or, even database technology (RDMBS, OODBMS, Document stores like CouchDB). That "new", 3rd tier, despite the added complexity, adds more flexibility and power than the complexity it introduces.

When your app tier is a very thin veneer on top of Stored Procedures, it's valid to question why it's even there at all.

Leveraging the database and all of its features is a valid application strategy, even today. SQL Server, Oracle, etc, are terribly powerful pieces of software.

Even then, though, the third tier is enormously helpful in adding flexibility to a modern system.


[1]:

[To see links please register here]

I think the reason is a combination of vendor lock-in and lack of knowledge on the part of most RDBM users. SQL is a programming language, and it's much harder to master both the language you're calling SQL from and SQL than to master one or the other, especially since SQL is a particularly unique language.

The solution, I think, is to abstract your database functionality into a utility class, and give ownership of the class to a few users who know what they are doing with SQL. This minimizes the risk of vendor lockin (if you switch vendors, the only thing that gets rewritten is the class). This also gives the developers who aren't experts in SQL an abstracted-out interface so they don't have to deal with the database directly.

MySQL.

When web applications exploded in the late 1990s and early 2000s, MySQL was at version 3.3 or 4.0 and didn't support anything above simple `SELECT`s. It was, however, free, and installed with most Linux distributions. As a result, a generation of programmers didn't learn about databases and don't know how to use them.

Even now that MySQL is at 5.1 and supports most of the features of a commercial system, the same cruddy old blogs and articles are used as templates when a new LAMP project is kicked off, and MySQL is deployed with MyISAM tables and 3.3-era functionality.

If your software runs on your client's hardware then any change to the database (new Stored Procedures, updated views, etc) will require DB administrator rights. This is nearly always a problem for clients. Involving the DB group complicates any updates you need to do. There are a lot of great reasons presented here, but this is the only one I need to avoid putting code in the database like the plague.

I have been in too many situations where corporate politics ("we arent allowed access to the SQL Server so lets install a lesser powerful DBMS like Access to process millions of rows and join that with millions of rows in another table, and lets automate that import..") or even the technical politics that can happen ("I know Access can handle that amount of data and even if it doesnt we can split the MDB into several MDBs and reference them.....")

UGH. Corporate politics and technical politics or even ignorance have prevented me from using many features.

Another example - I see no reason to not use stored procedures in a 100% Microsoft shop where SQL Server is **the** DBMS of choice. But, because the IT guy who was eventually going to own the solution was "light" on SPs, I had to resort to other measures. I mean, there is a perfect example of why some that "feature" went ignored by them at their shop.

I know another shop that still uses DOS Foxpro 2 because their sole IT guy wrote the existing system that way and that is how all new stuff will be developed. Why? Cant we move along with the times? Many of the marketing people over there have several DOS prompts open at once, with Foxpro "jobs" running in them to produce the most ugly reports I've ever seen. But it works - I will give them that. It works -- they have 12 million rows in their main table and 50+ other tables that they 'join' with that main table (not all 50 at once obviously) but man... its well past 1991! They dont even want to discuss one item from that bullet list you provided in your question.

Stuff like this is why I guess.

One concern I have seen with leveraging increased database functionality is scaling. It seems to be a much more difficult proposition to scale your database load vs web/application server load.

Your options are limited, scaling-up with bigger faster hardware (with sometimes much higher licensing costs), or complicated, scaling-out with read-only copies, etc.

If there are performance issues, I want them to be at the web server application level. At least then one of my options is to add another web server and distribute load.

I am not arguing against database level code to minimize the amount of network traffic (records) sent between the web server and database server. I am arguing against other features, eg. extensive business logic processing at the database level.

A few posts note that it is cheaper to scale at the application layer than at the db layer.

Another consideration is composite applications that access multiple data stores. Much easier to write and maintain platform agnostic query language in the app tier than separate platform specific queries in the the db layer.