The idea of putting any logic in the database never did make much sense to me for these exact reasons. The beauty of software is it is soft (i.e. easily changed) and us hackers have learned how to manage change with tools and whatnot. Databases are robust and solid because they have to be. A lot of the time they're the app's foundation. You go chipping away at the foundation and you end up with a trailer home.
I spend 100% of my time writing "database apps." I'm not saying I'm right, but I use the database to store data, and my app to maintain consistency and provide logic. You can't see the future, so why pretend? I mean, who would have thought a single person can be considered either male, female, or both depending on the context and date... That happened to one of my apps... No one would have had the forethought to handle that case during the initial design.
> The idea of putting any logic in the database never did make much sense to me for these exact reasons.
I'm a big proponent of enforcing business rules right into the data model. Constraints such as not null, reference constraints, or even conditional unique constraints (yes, there's such a thing!) should be enabled wherever possible.
Just remember, your app will not be the final app that makes use of the data. Your app comes and goes, along with many other apps written by many others. But the data lives "forever."
In my opinion the notion of implementing business logic outside of the database and acknowledging the longevity of the data are not necessarily in conflict. You need to have a well defined model and you can choose whether the database is a full implementation of that model or just a representation of the state of that model.
I don't find databases well suited for expressing complex behavior - programming languages are far better at that.
Business rules applied to the data are good (constraints, etc.) because they force the developer to fix the old data when they introduce a new rule. Doing data migration from old systems is quite painful when required fields aren't there. It's hard work to introduce a foreign key constraint when there are a million rows that have to be updated...
However, much of what people call business rules are much more complex logic than that. I like the store things like the state transition mappings in the database, but ultimately the application is the more flexible place to execute the state transitions.
> Just remember, your app will not be the final app that makes use of the data. Your app comes and goes, along with many other apps written by many others. But the data lives "forever."
Here here - that quote is so close to what Tom Kyte always says I reckon you are either Tom himself or an Oracle DB programmer :-)
> I spend 100% of my time writing "database apps." I'm not saying I'm right, but I use the database to store data, and my app to maintain consistency and provide logic. You can't see the future, so why pretend?
The weird thing about that is in some organizations (like my own), databases are considered easier to change than the software. Hence the push by management to move the logic out of the view or controller layers and into the "model" (i.e. use of stored procedures, constants fetched from the DB instead of in config files or headers, etc.) Where I work, they only pay one person to do database work, but three to write code. Yet the DB guy is always crushed by work, since he gets change/reporting requests all the time.
That breaks as soon as you realise apps:databases isn't 1:1 but n:1 and you can't scale without having core logic implemented in one place and one place only.
The solution to this is a multi-tier architecture. Only one application that implements the domain model talks to the DB and all other apps talk to that application. This doesn't give you the raw performance of a model living purely in the DB but may be a viable design decision. You trade flexibility for performance.
depending on the size of your model, right? If your dataset is huge, your domain model app can shard the data and provide a nice entry point to tune performance without touching any of the n apps who are dependent on the data. I'm not sure i've ever seen a database that lets you plug in memcached to speed up certain queries.
The model may still become a bottleneck if objects are tightly coupled and you can't scale out the model in a horizontal way. The low level nature and conceptual simplicity of the relational model allows database vendors to pull some pretty amazing tricks with scalability and clustering.
People aren't building big database centric applications because they don't know any better (so I would hope). It all depends on the context. It shouldn't be the default because it can be inflexible.
Umm, yeah, then you buy a third-party application that talks to your database just fine but doesn't know a thing about your "one application". Oh noes!
Is it realistic to assume that a 3rd party application that can be customized to work with my database model can't be customized to work with a model exposed in some other way (web service, J2E, .Net or whatever)?
No, it isn't. Database interfaces are very simple, text and table based, have been used for many years, are well tested, work reasonably well and fast, there are tools, bindings for every language in existence, and thousands of trained programmers. To change them for web services (I don't know how .NET or J2EE are equivalent) is not realistic at all.
I spend 100% of my time writing "database apps." I'm not saying I'm right, but I use the database to store data, and my app to maintain consistency and provide logic. You can't see the future, so why pretend? I mean, who would have thought a single person can be considered either male, female, or both depending on the context and date... That happened to one of my apps... No one would have had the forethought to handle that case during the initial design.