Sometimes it makes sense to make a common library shared between apps that interacts with your database and sometimes it makes sense to put that code itself in the database (as a view or stored proc). The latter has the advantage of working in multiple languages, direct DB access (eg. your fav DB client), and external systems that also read from your database (eg. a reporting service that connects to your DB). A shared library would be useless in the second and third situation and you'd end up duplicating logic.
So when the time comes to change the business logic you have stored in the database, do you have to ensure that every possible client that may use that business logic is updated at the same time? You could version your business logic in the database, but then it seems like you've implemented a version controlled business logic library inside the database.
Incidentally, every time I see people attempting to put business logic in the database it's usually manually updated views or stored procedures. It reminds me of the days of yore of people shelling into production to edit php files. Other than Rails migrations, South, etc, are there usable tools out there for sanely writing software that runs inside the database?
> So when the time comes to change the business logic you have stored in the database, do you have to ensure that every possible client that may use that business logic is updated at the same time?
No. If you change any of the interfaces (e.g., the structure or semantics of a view) in a non-backward-compatible manner, rather than merely changing the implementation, you have to assure that the consumers of the specific affected interfaces are updated. But if you've built a DB structure that isolates applications well (each application uses its own set of views, which reference the views implementing shared logic, which reference the base tables) most changes to shared business logic should be completely transparent to most applications, in the normal case not impacting even the application-specific views, but even when they do only requiring changes to the app-specific view definitions that don't impact the actual application.
> So when the time comes to change the business logic you have stored in the database, do you have to ensure that every possible client that may use that business logic is updated at the same time? You could version your business logic in the database, but then it seems like you've implemented a version controlled business logic library inside the database.
I don't see this as an issue at all. Consider the DB like any other software component and treat its data model as its API. Adding columns to existing structures or new stored procs should not effect any existing clients. Anyone who intends to use the new fields would explicitly use them.
Modifying and existing structure is a no-no (removing fields or dropping an existing view or proc "breaks" the DB module). Changing internals is fine though. If I change how a formula is calculated in a view or function but the API is stable then there should be no issue for existing clients. Sure you must test things in more places but that has nothing to do with the code being centralized. That's just because you have more code! The alternative would be independently building and testing multiple implementations of the same logic and deploying them simultaneously.
> Other than Rails migrations, South, etc, are there usable tools out there for sanely writing software that runs inside the database?
I've never considered this a major problem. If you design software starting with the data model then you rarely have to change it. Sure it does happen but no where need as often as the rest of the app. For basic changes Rails migrations, Hiberate scheme updates, etc are fine. For anything more major doing it manually isn't that much of a pain as you don't do it very often and when you do, it can usually be done in advance of your deployment (per my previous paragraph about non breaking DB changes).
If you're doing destructive changes to your data model regularly then you really need to stop and re think what the heck your building!
